Header

Monday, May 19, 2008

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.