Monday, December 19, 2011

SQL Server 2008 - CPU Usage

Below, a query due to verify the CPU Usage of Sql Server.
Finally, check also this link http://support.microsoft.com/kb/961811 related the orphaned session IDs.

SELECT TOP 500 (a.total_worker_time/a.execution_count) as [Avg_CPU_Time],
Convert(Varchar,Last_Execution_Time) as 'Last_execution_Time',
Total_Physical_Reads,
SUBSTRING(b.text,a.statement_start_offset/2,
            (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
            else
            a.statement_end_offset end - a.statement_start_offset)/2) as Query_Text,
dbname=Upper(db_name(b.dbid)),
b.objectid as 'Object_ID'
FROM sys.dm_exec_query_stats a
cross apply
sys.dm_exec_sql_text(a.sql_handle) as b
ORDER BY
  [Avg_CPU_Time] DESC

Denis

No comments: