Many times you may experience regular slowdowns on MS SQL database. There could be many reasons for CPU bottleneck on MS SQL Server but one of the main reasons for CPU bottlenecks is insufficient hardware resources. However, CPU utilization can usually be reduced by configuration changes and query tuning so you may like to avoid investing your money in buying faster, or more processors.

In this tip, I will provide you some ideas on how to identify CPU related bottlenecks using AppManager.

Performance Monitor

Start using the NT KS NT_CPULoaded to get the counter for the Processor:% Processor Time: if it regularly exceeds 80% of the processor time per CPU then most probably you're facing a CPU-related bottleneck. While this counter helps you to identify the CPU bottleneck on MS SQL server some of the CPU intensive operations are compilation and recompilation. You can monitor them using the SQL Statistics object counters using AppManager NT general KS "General_Counter". These counters are suggested by Microsoft and at a minimum, you should monitor the number of batches received. If the ratio of SQL Recompilations/sec to Batch Requests/sec is high then it potentially indicates a problem.

Setup and monitor these counters:

SQL Server: SQL Statistics: SQL Compilations/sec
SQL Server: SQL Statistics: SQL Recompilations/sec
SQL Server: SQL Statistics: Batch Requests/sec

Another counter to detect CPU related problems is the SQL Server: Cursor Manager By Type - Cursor Requests/Sec counter which shows you the cursors used on your server. If you can see hundreds of cursor requests per second then it is most probably because of poor cursor usage and small fetch sizes.

Intraquery parallelism can also be detected by examining the SQL Statistics: Batch Requests/sec counter. The less number of batches processed per second during high CPU utilization periods, the more likely the batches are running with parallel plans.

Dynamic Management Views

There are some useful Dynamic Management Views (DMVs) to check CPU bottlenecks. The sys.dm_exec_query_stats DMV shows you the currently cached batches or procedures which are using the CPU. The following query can be executed using "RunSQL" KS to check the CPU consumption per plan_handle.

select plan_handle,
sum(total_worker_time) as total_worker_time,
sum(execution_count) as total_execution_count,
count(*) as number_of_statements
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc

The plan_generation_num column shows how many times the query has been recompiled.

The SQL Server optimizer tries to choose an execution plan for the query that provides the fastest response time but this does not always mean minimal CPU utilization. Inefficient query plans that cause increased CPU consumption can also be detected using the sys.dm_exec_query_stats.

If you would like to have an overview of how much time is spent by SQL Server with optimization then check sys.dm_exec_query_optimizer_info. The elapsed time and final cost counters are particularly useful.

Using these small tips, you can save the spending on the hardware. Enjoy!