Wednesday, August 21, 2013

Processor time in perfmon for SQL Server diagnostics

It's along time since I used Perfmon for SQL Server problems so I looked up the basic starting counters to use.

Processor = % Processor Time 

This can spike and probably should not be above 70%. This may be system wide problems and not SQL Server. If high it needs investigating because CPUS should not, are not generally the bottleneck in modern systems.

Processor - % Privileged Time

The amount of time the processor on Kernel system processing. Is there a problem with the IO OS system?


Process (sqlservr) - % Processor Time 

See how these relate to the previous Processor times. Is it SQL Server using the CPU?

Process (sqlservr) - % Privileged Time


System - Processor Queue Length

This value should be zero. It counts the number of threads waiting for CPU

Memory - Available Mbytes

> 300Mbytes is ok.
> 500Mbytes is better.

If memory is disappearing then it may be that you have not set a maximum for SQL Server. SQL Server will grab it all if it can.

What SQL Server Perfmon Counters to look at

There are a ton of Perfmon counters to look at when investigating SQL Server problems. This pdf is a great resource for starting out.

http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

There is a cool spreadsheet too

http://blogs.msdn.com/b/jimmymay/archive/2008/10/15/perfmon-objects-counters-thresholds-utilities-for-sql-server.aspx