SQL Reference

By Frederic JOFES and Co.

Étiquette : Performance counters

performance counter thresholds

 Objet  Compteur Valeur 
 Memory  Available MB  Moyenne > à 10 Mb
 Pages/sec  Moyenne < à 50
 Pages faults/sec
 Pages input/sec
 Pages output/sec
 SQLServer: Buffer Manager  Buffer cache hit ratio  Moyenne >=  à 90%
 Free pages  Minimum > à 640
 SQServer : Memory Manager  Memory Grants Pending  Moyenne = à 0
 Target Server Memory (KB)  Doit être proche de la mémoire physique allouée à SQL Server.

If this counter is still growing the server has not yet reached its steady-state, and it is still trying to populate the cache and get pages loaded into memory.  Performance will likely be somewhat slower during this time since more disk I/O is required at this stage.  This behavior is normal.  Eventually Total Server Memory should approximate Target Server Memory.

 Total Server Memory (KB)  Doit être proche de la mémoire physique du serveur
 PhysicalDisk  %DiskTime  Moyenne < à 5%
 Current Disk Queue Length  Moyenne < à 2 par disque
 Avg. Disk Queue Lenght  Moyenne < à 2 par disque
 Disk Transfers/sec  Valeur maximale < à 100
 DiskBytes/sec  Valeur maximale < à 10 MB/sec
 Processor  % Processor Time  Moyenne < à 90%
 % Privileged Time  Moyenne < à 10%
 System Processor Queue Length  Moyenne < à 2
 Context Switches/sec  Moyenne < à 1000 par processeur
 Network Interface (Network card)  Bytes Total/sec  Moyenne < à 50% de la capacité NIC
 Network Segment  % Net Utilization  Moyenne < à 80% de la bande passante
 SQLServer : Access Methods  FreeSpace Scans/sec
 Full Scans/sec 1 full scan pour 1000 index searches
 SQLServer : Latches  Total Latch wait Time (ms)  (Total Latch Wait Time) / (Latch Waits/Sec) < 10
 SQLServer : Locks (_Total)  Lock TimeOuts/sec
 Lock Wait Time (ms)  

Note: For “Lock Wait Time” it is recommended to look beyond the Avg value.  Look for any peaks that are close (or exceeds) to a wait of 60 sec.   Though this counter counts how many total milliseconds SQL Server is  waiting on locks during the last second, but the counter actually records  at the end of locking event.  So most probably the peaks represent one huge locking event.  If those events exceeds more than 60seconds then they may have extended blocking and could be an issue. In such cases, thoroughly analyze the blocking script output. Some applications are written for timing out after 60 seconds and that’s not acceptable response for those applications.

 Number of Deadlocks/sec  < 1
 SQLServer : SQL Statistics  Batch Requests/sec  < 20 Page Splits/sec pour 100 Batch Requests/Sec

Over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon

From a network bottleneck approach, a typical 100Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this busy, you may need to have two or more network cards, or go to a 1Gbs network card.

 SQL Re-compilations/sec  < 10% of the number of SQL Compilations/sec
 SQLServer : General Statistics  User Connections  Note: It is recommended to review this counter along with “Batch Requests/Sec”.   A surge in “user connections” may result in a surge of “Batch Requests/Sec”.  So if there is a disparity (one going up and the other staying flat or going down), then that may be a cause for concern. With a blocking problem, for example, you might see user connections, lock waits and lock wait time all increase while batch requests/sec decreases.
 Objet  Compteur Valeur 
 SQLServer:Access Methods  Forwarded Records/sec  < 10 per 100 Batch Requests/Sec
 
 Index Searches/sec  1 full scan pour 1000 index searches
 
 Page Splits/sec  < 20 par 100 Batch Requests/Sec
 SQL Server:Buffer Manager  Free list stalls/sec  < 2
 
 Lazy Writes/Sec  < 20
 
 Page Life Expectancy  > 300
   Page lookups/sec  (Page lookups/sec) / (Batch Requests/sec) < 100
 Page reads/sec  < 90
 Page writes/sec  < 90
 SQLServer:General Statistics  Logins/sec  < 2
 Logouts /sec  < 2
 SQL Server:Latches  Latch Waits/sec  (Total Latch Wait Time) / (Latch Waits/Sec) < 10
 SQL Server:Locks  Lock Waits/sec  0
 SQLServer:SQL Statistics  SQL Complilations /sec  < 10% of the number of Batch Requests/Sec

Performance counters with type

-- Donne la liste des compteurs avec leur type
select object_name, counter_name, instance_name, cntr_value, CASE
WHEN cntr_type = 1073939712 THEN 'LARGE_RAW_BASE'
WHEN cntr_type = 537003264 THEN 'LARGE_RAW_FRACTION'
WHEN cntr_type = 1073874176 THEN 'AVERAGE_BULK'
WHEN cntr_type = 272696576 THEN 'COUNTER_BULK_COUNT'
WHEN cntr_type = 65792 THEN 'COUNTER_LARGE_RAWCOUNT'
END cntr_type from sys.dm_os_performance_counters

Fièrement propulsé par Fred & Co.