Detect CPU Bottleneck in SQL Server

By : Kasim Wirama, MCDBA

When you have your database server experiencing a problem, there are many possibilities here, such as CPU, memory, hard disk or database configuration itself. There should be a systematic way to trouble shooting performance problem in SQL Server. This article, I would like to point out how to detect bottleneck in CPU with SQL Server 2005.

The straight forward way to detect CPU problem is to look at performance counter, with object : Processor, and counter name : %Processor Time. If it shows high percentage value, let’s say 80% or over during 15 to 20 minutes, you definitely have CPU bottleneck. Anyway, you need to establish your baseline for CPU threshold above. Another counter name that is useful is System:Processor Queue Length. This counter gives information how long a queue for each processor. If you see 2 or more value for most of the time, your processors are under pressure. When your server box has some applications running besides SQL Server, probably one of the application takes up significant CPU resource. To prove your suspicious thought, get information from Process:%Processor Time counter.

If you have your CPU bottleneck caused by SQL Server, you need to find out how many processes that are running, runnable, and suspended. An amount of runnable processes indicate that the CPU is busy serving other request, and an amount of suspended processes indicate that there is blocking issue. Here is the query to get the information.

SELECT COUNT(*) , t2.scheduler_id
From sys.dm_os_workers as t2, sys.dm_os_schedulers as t2
Where t1.state = ‘runnable/running/suspended’ and t1.scheduler_address = t2.scheduler_address and t2.scheduler_id < 255
Group by t2.scheduler_id

In general, there are 2 things that causes CPU bottleneck, they are :

1. Inefficient query plan.

If you want to associate the query with CPU bottleneck, you query it from DMV sys.dm_exec_query_stats and extract query text from sys.dm_exec_sql_text with parameter sql_handle. You sort the result based on most expensive average CPU cost that consists of division between total_worker_time and execution_count

2. Excessive compilation and recompilation.

If SQL Server needs some time to compile/recompile the query, it shows that your execution plan is not reusable. If your query is very complex, try to rewrite/adding some index that will make the compilation time run faster.

These are 3 performance counter relating to excessive compilation/recompilation issue :