Get Top N Cache Write Queries on Server

DROP FUNCTION dbo.udf_GetTopNQueries_LogicalWrites

Go

CREATE FUNCTION dbo.udf_GetTopNQueries_LogicalWrites(@n INT)

RETURNS @TopNQueries_LogicalWrites TABLE

(

PlanHandle VARBINARY(64),

TotalLogicalWrites 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

logical writes on the SERVER (since the last recompile of the plan)

The following DMVs are used:

sys.dm_exec_query_stats - to actually identify the disk usage 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: ChillyDBA

History: 27 Jun 2012 - Initial Issue

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

BEGIN

INSERT @TopNQueries_LogicalWrites

(

PlanHandle,

TotalLogicalWrites,

ExecutionCount,

DatabaseName,

ObjectName,

ObjectID,

Number,

Encrypted,

ParentQuery,

IndividualQuery

)

SELECT

top_n.plan_handle AS PlanHandle,

top_n.total_logical_writes AS TotalLogicalWrites,

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_logical_writes,

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_logical_writes DESC

RETURN

END

SELECT * FROM dbo.udf_GetTopNQueries_LogicalWrites(15)

SELECT * FROM dbo.udf_GetTopNQueries_LogicalWrites(20)