List of Threshold Value for SQL
Server Performance Counters
By : Kasim Wirama, MCDBA, MCITP
Monitoring database performance is
proactive task that a DBA should do. Possible bottleneck for SQL Server is memory,
processor, I/O subsystem, tempdb database, and locking. For SQL Server
specifics, below list is a minimum set of counters that should be used for
monitoring SQL Server performance daily. I would give preferred value but your
environment might require the value stick with the preferred one because each database
environment is unique. For your initial baseline, it could your reference.
1.
1. SQL Server:Access Methods with counter :
Forwarded Records/sec
Preferred value : less than 10 of 100 Batch
Requests/Sec
2.
2. SQL Server:Access Methods with counter : Full
Scans/sec
Preferred value : (Index
searches/sec)/(Full scans/sec) should be greater than 1000
3. 3. SQL Server:Access Methods with counter : Index
Searches/sec
Preferred value : same with point 2
4.
4. SQL Server:Access Methods with counter : Page
Splits/sec
Preferred value : less than 20 of 100 Batch
Requests/sec
5.
5. SQL Server:Buffer Manager with counter : Buffer
Cache Hit Ratio
Preferred value : greater than 90%
6.
6. SQL Server:Buffer Manager with counter : Free List Stalls/sec
Preferred value : less than 2
7.
7. SQL Server:Buffer Manager with counter : Free
Pages
Preferred value : greater than 640
8. 8. SQL Server: Buffer Manager with counter : Lazy
Writes/Sec
Preferred value : less than 20
9. 9. SQL Server: Buffer Manager with counter : Page
Life Expectancy
Preferred value : greater than 600 seconds.
10.
10. SQL Server: Buffer Manager with counter : Page
Lookup/sec
Preferred value : (Page Lookup/sec)/(Batch
Requests/sec) should less than 100
11.
11. SQL Server: Buffer Manager with counter : Page
Reads/sec
Preferred value : less than 90
12. 12. SQL Server: Buffer Manager with counter : Page
Writes/sec
Preferred value : same with point 11
13.
13. SQL Server: General Statistics with counter : Logins/sec
Preferred value : less than 2
14.
14. SQL Server: General Statistics with counter : Logouts/sec
Preferred value : less than 2
15.
15. SQL Server: Latches with counter : Latch
Waits/sec
Preferred value : (Total Latch Wait
Time)/(Latch Waits/Sec) less than 10
16.
16. SQL Server: Latches with counter : Total Latch
Wait Time (ms)
Preferred value : same with point 15
17.
17. SQL Server: Locks with counter : Lock Wait
Time(ms)
Preferred value : should not exceeds 60
seconds.
18.
18. SQL Server: Locks with counter : Lock Waits/sec
Preferred value : should be zero
19.
19. SQL Server: Locks with counter : Number of
Deadlocks/sec
Preferred value : less than 1
20.
20. SQL Server: SQL Statistics with counter : SQL
Compilations/sec
Preferred value : less than 10% of number
of Batch Requests/sec
21.
21. SQL Server: SQL Statistics with counter : SQL
Re-Compilations/sec
Preferred value : less than 10% of number of
SQL Compilations/sec
Generally 15 seconds sample rate is sufficient and it is recommended
to save it into CSV for later analysis.