DMV Queries - Get Top N CPU Queries on Server

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown/ChillyDBA
Date:        1 Dec 2010/26 Jun2012

Description

This function returns a results set containing a list of the top N (supplied as a parameter) worst CPU consuming query plans on the server.
The stats are valid since the last recompile of the plan.
 
Both the full test of the query batch and the text of the specific SQL statement are included in the results, along with DB and Object information where the statement is not an ad-hoc or prepared SQL statement.
 
NOTE:  CPU time is measured in microseconds, and has no correlation with the perfmon CPU figure, which is a percentage.
 

Code

Function:

DROP FUNCTION dbo.udf_GetTopNQueries_CPU
Go

CREATE FUNCTION dbo.udf_GetTopNQueries_CPU (@n INT)
RETURNS @TopNQueries_CPU TABLE
  
(
      
PlanHandle              VARBINARY(64),
      
TotalCPUTime            INT,
      
ExecutionCount          INT,
      
DatabaseName            SYSNAME NULL,
      
ObjectName              SYSNAME NULL,
      
ObjectID                INT NULL,
      
Number                  INT NULL,
      
Encrypted               INT NULL,
      
ParentQuery             NVARCHAR(MAX) NULL,
      
IndividualQuery         NVARCHAR(MAX) NULL
   )
AS
/***************************************************************
Purpose:   To retrieve the top N greatest query plan consumers of CPU
           on the SERVER (since the last recompile of the plan)
          
           The following DMVs are used:
               sys.dm_exec_query_stats - to actually identify the CPU levels
               sys.dm_exec_sql_text - to retrieve the original parent query plus
                                      the specific statement that the stat applies to      
          
          
          NOTE:  The DatabaseName and Object details are included in the results
                   but are not really useful when the query was ad-hoc or a prepared statement
          
Author:        Unknown/ChillyDBA
History:   1 Dec 2010 - Initial Issue  
           26 Jun 2012 - ChillyDBA - Converted to a table valued function
            with optional number or results (@N) parameter

****************************************************************/

BEGIN

   INSERT
@TopNQueries_CPU
      
(
          
PlanHandle,
          
TotalCPUTime,
          
ExecutionCount,
          
DatabaseName,
          
ObjectName,
          
ObjectID,
          
Number,
          
Encrypted,
          
ParentQuery,
          
IndividualQuery
      
)
  
SELECT  
      
top_n.plan_handle                               AS PlanHandle,  
      
top_n.total_worker_time                         AS TotalCPUTime,
      
top_n.execution_count                           AS ExecutionCount,
      
ISNULL(DB_NAME(q.dbid), NULL)                    AS DatabaseName,
      
CAST(OBJECT_NAME(q.objectid) AS VARCHAR(20))  AS ObjectName,
      
q.objectid                                      AS ObjectID,
      
q.number                                        AS Number,  --for a numbered stored procedure
      
q.encrypted                                     AS Encrypted,
      
CONVERT(NVARCHAR(MAX), q.text)                   AS ParentQuery,
        
      
SUBSTRING (q.text,(top_n.statement_start_offset/2) + 1,(
           (  
              
CASE
                  
WHEN top_n.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), q.text)) * 2
                  
ELSE top_n.statement_end_offset
              
END - top_n.statement_start_offset)/2
          
) + 1
      
)                                               AS IndividualQuery
  
FROM  
      
(
          
SELECT TOP (@n)
              
qs.plan_handle,  
              
qs.sql_handle,  
              
qs.execution_count,
              
qs.total_worker_time,
              
qs.statement_end_offset,
              
qs.statement_start_offset
          
FROM sys.dm_exec_query_stats qs
          
ORDER BY qs.total_worker_time DESC
      
) AS top_n
  
CROSS APPLY sys.dm_exec_sql_text(top_n.sql_handle) AS q
  
ORDER BY top_n.total_worker_time DESC
  
   RETURN
  
END

 
 

Test Code:

USE master
GO

SELECT * FROM dbo.udf_GetTopNQueries_CPU (15)
SELECT * FROM dbo.udf_GetTopNQueries_CPU (20)


ċ
udf_GetTopNQueries_CPU.sql
(3k)
Andy Hughes,
Jun 26, 2012, 12:14 PM
Comments