Tuesday, December 27, 2011

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) /) )
        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

3 comments:

  1. Is there a way to get which stored proc contained the SQL that is returned in this?

    ReplyDelete
    Replies
    1. You can get it through the plan_handle joined to sys.dm_exec_query_plan, which contains the object_id (stored proc or UDF) associated with the plan.

      From there, you can query sys.objects

      Delete
  2. what is it when the DBName, Objectid, and ObjectName are all NULL?

    ReplyDelete