SQL Server – Performance Top Queries by Total CPU Time

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

 

 

 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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Performance Top Queries by Total CPU Time


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer