RSS

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,
    s.text
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
,total_worker_time
,total_elapsed_time
,last_elapsed_time
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
s.text
,db_name(s.dbid) as db_name
,qs.total_worker_time
,qs.total_elapsed_time
,qs.plan_generation_num
,qs.last_worker_time
,qs.execution_count
,qs.creation_time
,qs.total_physical_reads
,qs.last_elapsed_time,max_elapsed_time
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

Advertisements
 
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s

 
%d bloggers like this: