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 grants pending should be 0
- Avg Latch Wait Time(ms) should be less than 10
- 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.
- Context switches/sec should be less than 20,000 (if greater, you might need to turn on lightweight pooling)