Home > SQL Server > Monitoring SQL Server Performance

Monitoring SQL Server Performance

Here are the perfmon counters that you can use to monitor SQL Server Performance

Purpose is taken from technet: http://technet.microsoft.com/en-us/library/ms189628%28v=sql.105%29.aspx, and http://msdn.microsoft.com/en-us/library/ms804035.aspx

Perfmon Purpose Recommended Values
Process : % Processor Time : sqlservr.exe Monitors the CPU utilization of SQL Server Different people have different values, typically anything below 50% is good
SQL Server : SQL Statistics : SQL Compilation/sec Monitors the number of SQL Compilations per second <2/sec is good
2-20/sec: not so good
20-100: bad
>100: please look into it
SQL Server : SQL Statistics : SQL Re-Compilation/sec Monitors the number of SQL re-compilations per second Same as SQL Compilation
SQL Server : Buffer Manager : Buffer Cache Hit Ratio

Percentage of pages found in the buffer cache without having to read from disk.

anything < 99.5%, please look into it
SQL Server : Buffer Manager : Checkpoint Pages/sec

Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

It varies from system to system, you might want to find your own base line by monitoring this value
SQL Server : Buffer Manager : Page life expectancy Number of seconds a page will stay in the buffer pool without references. Preferably at least 300
SQL Server : Buffer Manager : Lazy writes/sec Number of buffers written per second by the buffer manager’s lazy writer. As close to 0 as possible
Physical Disk : % Disk Time Reports the percentage of time that the selected disk drive is busy servicing read or write requests. 0.2 to 0.3 is fast
0.3 to 0.4 is normal
> 0.4 is not good
Physical Disk : % Idle Time Reports the percentage of time that the disk system was not processing requests and no work was queued < 99.5 is not good
Physical Disk : Avg Disk Read Queue Length Shows the average number of read requests that were queued for the selected disk during the sample interval. <2 is recommended
Physical Disk : Avg Disk Sec/Read Shows the average time, in seconds, of a read of data from the disk. 0.2 to 0.3 is fast
0.3 to 0.4 is normal
> 0.4 is not good
Physical Disk : Avg Disk Sec/Write Shows the average time, in seconds, of a write of data to the disk. 0.2 to 0.3 is fast
0.3 to 0.4 is normal
> 0.4 is not good
Physical Disk : Avg Disk Write Queue Length Shows the average number of write requests that were queued for the selected disk during the sample interval. <2 is recommended
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: