DotNetBob on SQL

Assuming you don’t have already have a fancy tool like Quest Software’s Performance Analyzer, or you aren’t using the Performance Dashboard offered by Microsoft, one way to quickly determine if you are CPU bound and what is actually utilizing your CPU utilization is to use a couple queries. I borrowed some information from the Microsoft document called Troubleshooting Performance Problems in SQL Server 2005 below.

First, CPU bottlenecks can occur from a variety of sources, including:

• Nonoptimal query plan
• Poor SQL Server configuration
• Insufficient hardware resources
• Nonoptimal schema design

 

The best approach to determine if your SQL Server is CPU bound (meaning the CPU has reached 100% utilization) is to utilize System Monitor in Windows and review the counter called Processor: % Processor Time. Values greater than 80% indicate your system has a CPU bottleneck. In addition, you can also use sys.dm_os_schedulers to find out how many runnable tasks exist. Values other than zero indicate that tasks are waiting to run, and high values are an indication that the CPU is bottlenecking your performance. This query will identify the number of runnable tasks:

SELECT

    scheduler_id,

    current_tasks_count,

    runnable_tasks_count

FROM

    sys.dm_os_schedulers

WHERE scheduler_id < 255

Additionally, the following query shows which batches or procedures are consuming the most CPU and will also include the actual SQL statement. The query aggregates by a specific plan handle. If the plan handle contains more than one SQL statement you must drill into each statement to determine where the greatest CPU contribution comes from.

SELECT TOP 50

   SUM(qs.total_worker_time) as total_cpu_time,�
   (SELECT [TEXT] FROM sys.dm_exec_sql_text(plan_handle)),
   SUM(qs.execution_count) as total_execution_count,
   COUNT(*) as number_of_statements,�
   qs.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) DESC

Additional consideration must be given to excessive query recompilation, innefficient query plans, intra-query parralelism and cursor usage when considering CPU bottlenecks. Please refer to Microsoft’s article titled Troubleshooting Performance Problems in SQL Server 2005 for more detailed information.

Leave a Reply

You must be logged in to post a comment.