Performance tuning counters for SQL Server

As an instructor I always tell my students to make a performance baseline on their SQL Server so that they have something to compare against, when (not if) the users complain about performance. But my students and customers often ask – which counters should I monitor then?

Well here is, to my experience, a good list to start with. Arguably you could narrow or widen the list – but for a start this list has in my experience proven to be sufficient. 

 

Performance object	Counter	Warning	Alert	Notes
Memory	Pages/sec	100	200	I know that many sites say 20 – but that was from the NT4 days with 128 MB RAM
Memory	Available MBytes	200	100
SQL Server: Buffer Manager	Buffer Cache Hit Ratio	90	85
SQL Server: Buffer Manager	Page Life Expectancy	350	300
These should be per physical disk
Physical Disk	Disk Reads/sec	No levels – just monitor these
Physical Disk	Disk Writes/sec	No levels – just monitor these
Physical Disk	% Disk time	No levels – just monitor these
Physical Disk	% Disk time	No levels – just monitor these
Physical Disk	Avg. Disk Queue Length	1	2
Processor	% Processor Time	70	80
System	Processor Queue Length
Network Interface	Bytes Received/sec	No levels – just monitor these
Network Interface	Bytes Sent/sec	No levels – just monitor these
Network Interface	Output Queue Length	No levels – just monitor these
SQL Server: General	User Connections	No levels – just monitor these
SQL Server: Memory Manager	Connection Memory (KB)	No levels – just monitor these
SQL Server: Memory Manager	Total Server Memory (KB)	No levels – just monitor these

 

You don't have to monitor the counters all the time to create a good baseline, the warning and alert levels are included for those of you who want to implement the counters in your normal performance monitoring system like MOM.