Important Perfmon Counters %Processor and Disk monitoring counters

Monitoring  SQL Server Performance is one of important task for a DBA using Performance Microsoft Management Console (MMC) snap-in. 

Performance monitoring can by done by many ways including monitoring SQL Performance Counters below which are for %Processor utilization and Disk Queue Monitoring. Perfmon analysis is important step in troubleshooting issues when a application goes slow.

Performance Monitor in SQL Server 2012 , 2014, 2016 etc are also very capable

 Important Perfmon Counters %Processor and Disk monitoring counters

S.NOPerformance Counter NameComments
9Processor – %Processor TimeThe percentage of time the processor spent executing a non-idle thread. This value is subtracted from the time the processor was idle 100 percent. This is an indicator to the overall CPU utilization in the system.
9Processor – %Interrupt TimeThe percentage of time the processor spent servicing hardware interrupts. This can be any activity on the server machine. This needs to be 0 while our testing process.
9Processor – Processor Queue LengthThis counter indicates the number of threads that are waiting in the processor queue. It can be also interpreted as the number of threads that are waiting to be run by the processor. If this value is greater than the number of processor then we have aCPU bottleneck in the system.
9Processor – Context Switches/secThis is an interesting counter. A typical context switch occurs when the OS or the application is forced to change the executing thread on one processor to another thread executed on another processor. This value has to be as small as possible. Context switches are not avoidable in multi-processor machines. Hence any value below 10000 is fine.
9PhysicalDisk – %Disk Read TimeTime spent by the disk for read operation. We can have disk by disk analysis also to narrow down any read IO bottlenecks.
9PhysicalDisk – %Disk Write TimeTime spent by the disk for write operation. We can have disk by disk analysis also to narrow down any write IO bottlenecks.
9PhysicalDisk – Avg. Disk Queue LengthAverage number of requests that waited for the read and write requests in the particular disk. A high value suggests we have a IO bottleneck.
9Memory – Page Faults/secTotal number of faulted pages handled by the processor per second. This value needs to as small as possible.
9Memory – Pages/secThe number of pages written to disk or from disk to resolve page faults. This would be the sum of page reads/sec and page writes/sec counter.

Leave a Reply

Your email address will not be published. Required fields are marked *