How to Identify CPU Bottlenecks in SQL Server Using Performance Counters

There are instance when you are facing CPU bottlenecks on your SQL Server Instance. In this article we will take look at How to Identify CPU Bottlenecks in SQL Server Using Performance Counters.The most common reason for a CPU bottleneck will be insufficient hardware. However, as a database administrator one can make certain configuration changes and tune the SQL Server queries to improve the performance. To begin with Performance Monitor is a great tool which can be used to quickly identify CPU bottlenecks on your SQL Server Instance. Monitoring the below counters can help you in Performance Tuning of SQL Server.

Most important CPU counters to be looked at are: –

Processor: % Processor Time

This counter gives you information with respect to how much time the processor has actually spent working on productive threads and how often it was busy serving such requests. If you find the value of this counter hovering above 80% per CPU then this gives a clear indication that the server is clearly facing CPU bottlenecks.

System: Processor Queue Length

This counter gives you information with respect to the number of threads in the processor queue. This counter basically displays the ready threads; it doesn’t count the threads which are running. Even if you have multiple processors on the system this counter will display a cumulative value. Hence if you have 5 processors on a system and the value for the system turns out to be 60 then it mean on an average the Processor Queue Length for a single processor is 12. Ideally the average processor queue length should be less than 10.

System: % Total Processor Time

This counter gives you information with respect to the total performance of the entire system i.e. it is an average of all the processors together on the system.

SQL Server: SQL Statistics: Batch Requests/sec

This counter gives you information with respect to the number for TSQL command batches received per second.

SQL Server: SQL Statistics: SQL Compilations/sec

This counter gives you information with respect to number of SQL Compilations happening per second.

SQL Server: SQL Statistics: SQL Re-Compilations/sec

This counter gives you information with respect to number of TSQL statement recompiles per second. This counter has the count of number of times statement recompile is triggered by the database engine. This counter value should be very low. Ideally SQL Statistics: SQL Re-Compilations/sec value should not be more than one percent of SQL Statistics: Batch Requests/sec.

For example if your system has 1000 SQL Statistics: Batch Requests/sec then the value of SQL Statistics: SQL Re-Compilations/sec shouldn’t exceed 10 (> 1%) after the SQL Server is stable and its up and running for more than 24 hours.

SQL Server: Cursor Manager By Type: Cursor Requests/Sec

This counter gives you information with respect to number of SQL cursor requests received by server. If you end up seeing 100 of cursor requests per sec then it’s a worrying factor. This means that the code needs to be rewritten to avoid as much cursor usage as possible to improve the overall performance.

Reference: SQL Server, SQL Statistics Objects