09 November,2012 by Jack Vamvas
SQL Server Standard Reports offer some great out-of-the-box reports. When experiencing sudden SQL Server Performance Issues , one of the areas to check is Top Queries by Total CPU Time.
Once you’ve identified the queries with high CPU usage
1) Check if Is Auto Create and Update Statistics is Enabled. If Auto Create statistics is disabled , this may indicate out of date statistics. If Disabled, than proceed to Update Statistics – use sp_updatestats to update all statistics on the database. Run the query and check if any improvement.
2) If the query hasn’t improved analyse in SSMS for Hash Joins, Sorts, Filters. If any of these exists , progress with Query Analysis. Run the queries in SSMS can match the query with suitable indexes. The Execution Plan displays those details
select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no , (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1 , creation_time , last_execution_time , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [logicalWrites] , execution_count , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO] , case when sql_handle IS NULL then '' else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) ) end as query_text , db_name(st.dbid) as database_name , st.objectid as object_id from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st where total_worker_time > 0 order by total_worker_time desc