Get Top queries by CPU Time

This script will help you to identify the TOP X heaviest statements executed on the SQL Server since the last time the service was started. The result set returns CPU Times, Logical reads and writes, as well as I/O stats.


It is very useful when you need to start to troubleshoot a performance issue.

USE master

GO

IF (object_id( 'tempdb..#TMP_T1' ) IS NOT NULL) DROP TABLE #TMP_T1 ;

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 DBName

,       st.objectid

,             cast(NULL as varchar(255)) as ObjectName

INTO #TMP_T1

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

GO

DECLARE curObj CURSOR

       FOR Select Distinct DBName, objectid from #TMP_T1

Declare @Cmd varchar(max)

Declare @DBName sysname

Declare @OBJID int

OPEN curObj

WHILE (1=1)

BEGIN

       FETCH NEXT FROM curObj INTO  @DBName, @OBJID

       IF (@@fetch_status <> 0)

              break

       Set @Cmd = 'Update T set ObjectName = O.name from #TMP_T1 T inner join '+@DBName + '.dbo.sysobjects O (nolock) on O.ID = T.objectid where t.objectid = '+cast(@OBJID as varchar(15))

       --print @Cmd

       exec ( @Cmd )

END

CLOSE curObj

DEALLOCATE curObj

go

select * from #TMP_T1

GO