These are the counters recommended when looking at SQL Server Performance. Counters should be sampled every 15 seconds and all instances included not just totals.
Memory – Available Mbytes (pref 512MB – 1GB)
Paging File – % Usage (pref <1)
SqlServer: Buffer Manager
Page Life expectancy – (pref >300, possible fix indexes)
SQLServer: Memory Manger
Total Server Memory
Target Server Memory – ( if reducing OS asking for memory )
Memory Grants Pending – ( should be zero, add memory )
Physical Disk
Avg. Disk sec/Read ( 100ms slow)
Avg. Disk sec/Write
Processor – % Processor Time
System – Processor Queue Length ( <0)
SQLServer: Batch Statistics
Compilations/sec
Recompliations/sec
SQLServer:General Statistics
User Connections ( Trends )
SQLServer: Access Methods ( Schema Design )
Page Splits/sec
Page Alloacted/sec
Range Scans/sec
Disk Reads/sec ( Trend )
Disk Writes/sec ( Trend )
These where taken from Brent Ozar’s presentation.
