bschelst/ February 11, 2019/ SQL-Server/ 0 comments

The t-sql code below will give you the top 10 most CPU consuming SQL statements in your database, sorted by CPU time.

SELECT TOP 10 
        qs.total_worker_time/qs.execution_count as [Avg CPU Time], 
        SUBSTRING(qt.text,qs.statement_start_offset/2,  
            (case when qs.statement_end_offset = -1  
            then len(convert(nvarchar(max), qt.text)) * 2  
            else qs.statement_end_offset end -qs.statement_start_offset)/2)  
        as query_text, 
        qt.dbid, dbname=db_name(qt.dbid), 
        qt.objectid  
FROM sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt 
ORDER BY  
        [Avg CPU Time] DESC
Share this Post

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
*
*

four × three =