Check SQL Server CPU status

24 Nov

/* Monitor the SQL Server schedulers using the sys.dm_os_schedulers view to see if the number of runnable tasks is typically nonzero. A nonzero value indicates that tasks have to wait for their time slice to run; high values for this counter are a symptom of a CPU bottleneck. You can use the following query to list all the schedulers and look at the number of runnable tasks.

select scheduler_id, current_tasks_count,runnable_tasks_count
from sys.dm_os_schedulers
where scheduler_id < 255

The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU. The query aggregates the CPU consumed by all statements with the same plan__handle (meaning that they are part of the same batch or procedure).

If a given plan_handle has more than one statement, you may have to drill in further to find the specific query that is the largest contributor to the overall CPU usage.

select top 50
    sum(qs.total_worker_time) as total_cpu_time,
    sum(qs.execution_count) as total_execution_count,
    count(*) as  number_of_statements,
from  sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) s
group by s.text
order by sum(qs.total_worker_time) desc

When you use the sys.dm_exec_query_optimizer_info DMV, you can get a good idea of the time SQL Server spends optimizing.
If you take two snapshots of this DMV, you can get a good feel for the time that is spent optimizing in the given time period.

Viewing the total number of optimizations, How many optimizations are performed?


SELECT occurrence AS Optimizations
FROM sys.dm_exec_query_optimizer_info
WHERE counter = ‘optimizations’;


Average elapsed time per optimization, What is the average elapsed time per optimization?


SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info
WHERE counter = ‘elapsed time’;


 Fraction of optimizations that involve subqueries, What fraction of optimized queries contained a subquery?


SELECT (SELECT CAST(occurrence AS float)
                             FROM sys.dm_exec_query_optimizer_info
                             WHERE counter = ‘contains subquery’)
       (SELECT CAST(occurrence AS float)
FROM sys.dm_exec_query_optimizer_info
 WHERE counter = ‘optimizations’) AS ContainsSubqueryFraction;


The following sample query gives you the top 50 stored procedures that have been recompiled.


select sql_text.text
, last_execution_time
, plan_generation_num
, execution_count
from  sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where  plan_generation_num >1 –and sql_text.text not like ‘%SQL diagnostic%’
order by plan_generation_num desc
–order by last_execution_time desc


the query determines which query is using the most cumulative CPU


select top 50
,db_name(s.dbid) as db_name
from  sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as s
order by qs.total_worker_time desc

Leave a comment

Posted by on November 24, 2011 in CPU


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: