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)