How to determine the CPU bottleneck

Detect CPU bottleneck in SQL serverby: Kasim wirama, mcdba

 

When you have your database server experiencing a problem, there are available possibilities here, such as CPU, memory, hard disk or database configuration itself. there shoshould be a systematic way to trouble shooting performance problem in SQL Server. this article, I wowould 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 values 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 susponded 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/susponded '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. 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

 

1. 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:

1. A. SQL server: SQL statistics: Batch requests/sec
2. B. SQL server: SQL statistics: SQL compilations/sec
3. C. SQL server: SQL statistics: SQL recompilations/sec

 

With wealth information from DMV and Performance Monitor, you have a useful tool for troubleshooting CPU bottleneck right away.