How to find CPU usage for SQL server 2005 Server
5 Answers
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,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
-
How do I get the plan_handle i.e. the query for that handle ?– AnonymousAug 18, 2009 at 22:38
Use performance counters http://tipsstation.com/article/SQL-Server-Performance-Counters.aspx
A few ways come to mind
- TaskManager
- Process Explorer
Get-Process sqlservr |select CPU
- WMI
- Performance Counters via PerfMon
-
Task Manager ..good ..thaks Scott– AnonymousAug 18, 2009 at 22:34
-
Performance counters ? where are they located ?– AnonymousAug 18, 2009 at 22:40
-
Using sys.dm_exec_query_stats this you can find cpu usages by different SQL Server objects. Below is a script:
SELECT
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
WHERE DB_NAME(st.dbid) IS NOT NULL
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.