Get Top N Cache Write 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: ChillyDBA
Date: 27 Jun 2012
Description
This function returns a results set containing a list of the top N (supplied as a parameter) worst logical cache disk read 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.
Code
Function:
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
Test Code:
SELECT * FROM dbo.udf_GetTopNQueries_LogicalWrites(15)
SELECT * FROM dbo.udf_GetTopNQueries_LogicalWrites(20)