If not otherwise specified, these numbers are from a Microsoft webcast.

SQL Server: Buffer Manager

• Page life expectancy should be > 300
• If Page lookup/sec / Batch Requests / sec is > 100, then you're doing too much I/O.
• Lazy writes/sec should be 0. > 20 is bad, indicates memory pressure.
• Free pages should be >= 640.

Memory manager

• Memory grants pending should be 0

Latches

• Avg Latch Wait Time(ms) should be less than 10
  

Locks

• Total wait time (ms) > 60,000 ms is bad.

SQL Server Statistics

• Compilations/sec should be less than 20% of batch requests.
  
• Recompiles should be less than 10% of batch requests.

Threads

• Context switches/sec should be less than 20,000 (if greater, you might need to turn on lightweight pooling)

Labels: SQL Server, SQL Server - Diagnostics

This entry was posted on Tuesday, January 22, 2008 at 1:22 PM.