Useful Performance Monitor Counters for SQL Server DBAs

Performance Monitor counters are playing major role while we are diagnosing the SQL Server Instance or System Performance factors. These counters are very helpful to analyze the issue as well as to monitor the SQL Server Instance and OS activities.

There are many numbers of counters available to perform the task. In which, I have categorized important counters which will be helpful for SQL DBAs to monitor and analyze the performance factors.

We can also use the SQL Profiler to identify the performance factors.

I have mentioned in the format of << [Performance Object Name]   ::    [Counter Name] >>

1.   SQLServer:General Statistics    ::    Processes Blocked counter

ð  It is used to identify the number of blocked processes.

2.   SQLServer:Wait Statistics    ::    Lock Waits counter

ð  It is used to find the count & duration of waiting that is occurring.

Performance Counters to check System/Instance/Application processes Availability

3.   System     ::     System Up Time

ð  It is used to find that How many seconds it’s been since our server is last rebooted.

4.   Process(Instance)  ::    Elasped Time

ð  It tells us how long particular process is running on our system.

Performance Counters to check the busyness of the system

5.   Processor(_Total)   ::   %Processor time

ð  It tells us total utilization of processors by running all processors (Average processor utilization of your machine)

6.   Process(instance)   ::    %Processor time

ð  It is used to check the individual process level

7.   System    ::    Processor Queue Length

ð  It tells us how many threads are waiting for the execution.

Performance Counters to check the Hardware functionality

8.   System    ::    Context Switches/sec

ð  It measures how frequently the processor has to switch from user mode to Kernel model to handle request from thread running in user mode.

ð  This counter value will be high, if heavier workload in the system

Performance Counters to check the RAM Availability

9.   Memory    ::    Pages/Sec

ð  It indicates the number of paging operations to disk in the mentioned interval.

10. Memory   ::    Available bytes

ð  If it is greater 10% of available RAM capacity, then no need to worry about RAM.

ð  If it is less than 2%, then needs to consider improving the RAM capacity.

11. Process(Instance)    ::    Working Set

ð  It is used to identify which process is consuming more amount of RAM.

12. Memory        ::      Transition faults/Sec

ð  It indicates how often trimmed page on the standby list are re-referenced. If this counter slowly starts to rise over time then it could also indicating you're reaching a point where you no longer have enough RAM for your server to function well.