|
DROP FUNCTION dbo.udf_GetTopNQueries_Duration
Go
CREATE FUNCTION dbo.udf_GetTopNQueries_Duration (@n INT)
RETURNS @TopNQueries_Duration TABLE
(
PlanHandle VARBINARY(64),
TotalDuration 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 long running queries
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_Duration
(
PlanHandle,
TotalDuration,
ExecutionCount,
DatabaseName,
ObjectName,
ObjectID,
Number,
Encrypted,
ParentQuery,
IndividualQuery
)
SELECT
top_n.plan_handle AS PlanHandle,
top_n.total_elapsed_time AS TotalDuration,
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_elapsed_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_elapsed_time DESC
RETURN
END
|
