Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How to find CPU usage for SQL server 2005 Server

share|improve this question

migrated from Aug 19 '09 at 6:47

This question came from our site for professional and enthusiast programmers.

From MSDN Article on Troubleshooting Performance Problems in SQL Server 2005

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,  
    sys.dm_exec_query_stats qs 
group by qs.plan_handle 
order by sum(qs.total_worker_time) desc
share|improve this answer
How do I get the plan_handle i.e. the query for that handle ? – Anonymous Aug 18 '09 at 22:38

A few more:

  • WMI
  • Peformance Counters
  • SNMP
share|improve this answer

A few ways come to mind

  • TaskManager
  • Process Explorer
  • Get-Process sqlservr |select CPU
  • WMI
  • Performance Counters via PerfMon
share|improve this answer
Task Manager ..good ..thaks Scott – Anonymous Aug 18 '09 at 22:34
Performance counters ? where are they located ? – Anonymous Aug 18 '09 at 22:40
Start->Run.. Type: Perfmon – Nick Kavadias Aug 19 '09 at 6:59

Using sys.dm_exec_query_stats this you can find cpu usages by different SQL Server objects. Below is a script:

    DB_NAME(st.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS SchemaName
    ,cp.objtype AS ObjectType
    ,OBJECT_NAME(st.objectid,dbid) AS Objects
    ,MAX(cp.usecounts)AS Total_Execution_count
    ,SUM(qs.total_worker_time) AS Total_CPU_Time
    ,SUM(qs.total_worker_time) / (max(cp.usecounts) * 1.0) AS Avg_CPU_Time 
FROM sys.dm_exec_cached_plans cp 
INNER JOIN sys.dm_exec_query_stats qs 
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
GROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),cp.objtype,OBJECT_NAME(objectid,st.dbid) 
ORDER BY sum(qs.total_worker_time) desc

In this result of the script, you can find type of objects and different calculation for CPU time.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.