Performance Monitor (PerfMon) is a tool that comes built-in with Windows and allows you to look into the performance of your system and the applications that are running on it. It gives you a way to view data points that are associated with these applications and link them with the effect they have on your system.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform monitoring related queries.
In this context, we shall look into how to set up the performance counter and the different Perfmon counters for CPU usage.
More information about performance counter (PerfMon) ?
Perfmon Counters for CPU Usage include Processor: % Processor Time, % User Time, % Privilege Time, % Interrupt Time, and so on.
Performance Monitor (PerfMon) is a tool that comes built-in with Windows. It mainly allows looking into the performance of the system and the applications that are running on it.
How to set up a performance counter for CPU Usage ?
You can follow the steps below to set up the performance counter.
1. First, we to the Control Panel and click on Administrative Tools.
2. Next, we click on the Performance option and right-click on Counter Logs.
3. After that, we select the New Log Settings and enter a name for the log and click OK.
4. Now, we click on Add Objects and select what type of performance object we want.
5. Then we click Add and Close.
6. Finally, we change the Log File type to "Text File (Tab Delimited)" and click on Apply and OK.
Perfmon Counters for CPU Usage
We first look at CPU usage in Windows Task Manager. If the CPU utilization is continuously going beyond 85% then it means the system is facing a CPU bottleneck. Here is the list of performance counters that we look at to identify SQL Server CPU bottlenecks.
1. Processor: % Processor Time
This counter displays the percentage of time the CPU spends executing a thread that is not idle. The value of this counter must be less than 50%. But if tt frequently reaching beyond 85% then it means the system is facing a CPU bottleneck.
2. Processor: % User Time
This counter shows the percentage of time a process spends executing user processes like SQL Server.
If this counter is high then it means CPU pressure is because of SQL Server or because of user applications. So we retrieve CPU intensive queries to get the culprit transactions that are causing high CPU pressure. Also, there is no idle value for this counter.
3. Processor: % Privilege Time
This counter shows the percentage of time the processor spends on the execution of Microsoft Windows kernel commands, such as processing of SQL Server I/O requests. Also, it captures the information about kernel mode and indicates the poor performance of disk subsystems.
So we monitor DISK IO related performance counters to identify DISK IO bottleneck. Also, if possible, we install a faster or more efficient disk subsystem.
4. Processor: % Interrupt Time
This counter shows the time a processor spends receiving and servicing hardware interruptions during specific sample intervals. Also, it indicates a possible hardware issue if the value is greater than 15 percent.
5. System: Processor Queue Length
This counter gives information about the number of threads waiting for processor time. If the value of this counter is greater than zero then it means the system is facing CPU bottleneck.
We can further check Compilations/sec and Re-Compilations/sec. A high number of compilations and recompilation usually indicates poor reuse of the query plans. We can fix this by optimizing the queries and stored procedures.
6. System: Calls/sec
This counter shows the number of software calls to the operating system service routines per second.
Similarly, another CPU perform counter Processor: Interrupts/Sec shows the number of hardware calls to the processor. We can use both perfmon counters to determine the source of the performance issues. The acceptable values for Calls/sec are under 20,000. Higher values indicate excessive OS activity caused by software applications.
7. Context Switches/sec
Context Switches occur when kernel switches processor from one thread to another. Its value must be less than 2,000 per processor.
But if the values of Context Switches/sec and Processor: % Processor Time on the system is constantly high then it is a clear sign of processor bottlenecks. The permanent solution to reduce the CPU bottleneck is to change the processor to a more powerful one or add an addition to existing ones.