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

6 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
  3. I have found that this site is very informative, interesting and very well written. keep up the nice high quality writing Consultor Qlik Ecuador

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. CopyCat is a great Figma Plugin Development tool to quickly get started with development. It's simple to use and install, and it comes with a wide range of features that make developing for Figma much easier.

    ReplyDelete