SQL Server – Performance Top Queries by Total CPU Time

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

 Read More

SQL Server – How to troubleshoot a slow running query

SQL Server – Find high impact queries with sys.dm_exec_query_stats

CPU Count - SQL Server DBA

SQL Server – Processor priority to Background services