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.

No comments:

Post a Comment