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,5	2	This is x times the number of spindles in the raid set. E.g. Raid 1 with 2 disk = 4
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.