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
|