Top Perfmon Counters to Identify SQL Server CPU Bottleneck
We should always monitor system resource utilization of our database servers and make sure that these utilizations should not exceed the benchmark ranges of our environment. Today, I will explain about monitoring top perfmon counters for CPU usage. If your database server is continuously showing high CPU usage that means you need to look in to top perfmon counters to identify SQL Server CPU bottleneck. You can monitor these performance counter values to detect the CPU pressure.
I have already explained top performance counters to detect Memory and Disk IO bottlenecks in SQL Server in my last articles. Have a look at them (attached below) to get Memory and Disk IO related performance counters and their ideal values for healthy system.
- Top 10 Performance Counters to Identify SQL Server Memory Pressure
- Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
Generally, CPU bottleneck arises because of either your system has not enough CPUs to process current workload or your transactions or applications are poorly designed that causes the high CPU. To deal with SQL Server CPU pressure, we need to either find the high CPU intensive queries and optimize them or increase the total CPU counts of the database server to handle current workload. There can be multiple reasons for high CPU usage like high recompilations of SQL Queries, OS issues, Query design issues: Sorting Joins, Poor Indexes, Condition Clauses etc. Here, I am going to explain about top perfmon counters to identify SQL Server CPU bottleneck.
Perfmon Counters for CPU Usage
Here, I am going to describe about perfmon counters for CPU usage in SQL Server. Ideally, first we should start with looking at CPU usage in Windows Task Manager. If the CPU utilization is continuously going beyond 85%, it means your system is facing CPU bottleneck. Find the list of performance counters to identify SQL Server CPU bottleneck.
Processor: % Processor Time
One of the very useful CPU perfmon counter for SQL Server is Processor: % Processor Time. This counter displays the percentage of time CPU spends executing a thread that is not idle. If your system is frequently reaching beyond 85% then it means your system is facing CPU bottleneck. The acceptable value for this counter should be less than 50%.
Processor: % User Time
Windows operating system has two modes of processing for any process. One is Kernel mode another one is user mode. This counter shows the percentage of time a process spends executing user processes like SQL Server. If this counter is high it means CPU pressure is because of SQL Server or because of user applications. You should focus to retrieve CPU intensive queries to get the culprit transactions that are causing high CPU pressure. There is no idle value for this counter. You should benchmark each system based on your workload.
Processor: % Privilege Time
This counter shows the percentage of time the processor spends on execution of Microsoft Windows kernel commands, such as processing of SQL Server I/O requests. This counter captures the information about kernel mode and indicates towards poor performance of disk subsystems. Monitor DISK IO related performance counters to identify DISK IO bottleneck and if possible, consider installing a faster or more efficient disk subsystem.
Processor: % Interrupt Time
This counter Processor: % Interrupt Time tells about the time a processor spends receiving and servicing hardware interruptions during specific sample intervals. This counter indicates a possible hardware issue if the value is greater than 15 percent.
System: Processor Queue Length
This is an important perfmon counter to detect CPU bottleneck. This counter gives information about the number of threads waiting for processor time. If value of this counter is greater than zero then this is a sign of CPU bottleneck. We can further check Compilations/sec and Re-Compilations/sec. A high number of compilations and recompilations usually indicates poor reuse of the query plans. This can be fixed by optimizing your queries and stored procedures.
Any system would have either software calls or application related calls or hardware related calls. This counter System: Calls/sec metric 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. Both perfmon counters can be used to determine the source of the performance issues. The acceptable values for System: Calls/sec are under 20,000. Higher values might indicate excessive operating system activity caused by software applications.
Context Switches occurs when kernel switches processor from one thread to another. The acceptable Context Switches/sec value should be less than 2,000 per processor. If you are observing constant high values of Context Switches/sec and Processor: % Processor Time on your system then this would be a clear sign of processor bottlenecks. Permanent solution to reduce CPU bottleneck is to change the processor to a more powerful one, or add an additional to existing ones.
You might have seen high Context Switches/sec values for systems that are running with multiple SQL Server instances or a system that is hosted other applications along with SQL Server.
- Disk and Performance Impact of running Online Index Rebuild Operation
- Understanding Database Shrink Operation and Why should you Avoid it?
- Fix Error 701: There is Insufficient System Memory to Run this Query
- How to Improve Bulk Data Load Performance?
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018