Procedure statistics over period of time

Standard

procedureRecently I enjoyed a PASS session with Paul Randal where I saw a nice script which took two snapshots of the wait statistics and calculated the delta of the two. If you’re interested you can find the blog post here.

Based on that idea I wanted a script that did the same thing but than for stored procedures.

The script will take a snapshot of the sys.dm_exec_procedure_stats, wait for a set amount of time and take another snapshot. Based on the values of these two snapshots the script will calculate several values.

/*============================================================================
  File:     ProcedureStatsPeriod.sql
 
  Summary:  Take snapshots of the procedure stats
 
  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Sander Stad, SQLStad.nl
 
  (c) 2015, SQLStad.nl. All rights reserved.
 
  For more scripts and sample code, check out http://www.SQLStad.nl
 
  You may alter this code for your own *non-commercial* purposes (e.g. in a
  for-sale commercial tool). Use in your own environment is encouraged.
  You may republish altered code as long as you include this copyright and
  give due credit, but you must obtain prior permission before blogging
  this code.
 
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/


-- Drop the temporary tables in case they exist
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##ProcStats1')
    DROP TABLE [##ProcStats1];
 
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##ProcStats2')
    DROP TABLE [##ProcStats2];
GO

SELECT
	d.object_id
	, d.database_id
	, DB_NAME(d.database_id) AS [database_name]
	, OBJECT_NAME(object_id, database_id) [procedure_name]
	, d.cached_time
	, d.last_execution_time
	, d.total_elapsed_time
	, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time]
	, d.last_elapsed_time
	, d.total_worker_time
	, d.last_worker_time
	, d.total_worker_time/d.execution_count AS [avg_worker_time]
	, d.execution_count
INTO
	##ProcStats1
FROM 
	sys.dm_exec_procedure_stats AS d
GO

-- Lets wait
WAITFOR DELAY '00:05:00'
GO

-- Collect the data for the second snapshot
SELECT
	d.object_id
	, d.database_id
	, DB_NAME(d.database_id) AS [database_name]
	, OBJECT_NAME(object_id, database_id) [procedure_name]
	, d.cached_time
	, d.last_execution_time
	, d.total_elapsed_time
	, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time]
	, d.last_elapsed_time
	, d.last_worker_time
	, d.total_worker_time
	, d.total_worker_time/d.execution_count AS [avg_worker_time]
	, d.execution_count
INTO
	##ProcStats2
FROM 
	sys.dm_exec_procedure_stats AS d
GO


WITH [DiffProcStats] AS
(
	SELECT
	-- Proc stats that weren't in the first snapshot
		[ps2].[object_id]
		,[ps2].[database_name]
		,[ps2].[procedure_name]
		,[ps2].[cached_time]
		,[ps2].[last_execution_time]
		,[ps2].[total_elapsed_time]
		,[ps2].[last_elapsed_time]
		, [ps2].[total_worker_time]
		,[ps2].[last_worker_time]
		,[ps2].[execution_count] 
    FROM [##ProcStats2] AS [ps2]
    LEFT OUTER JOIN [##ProcStats1] AS [ps1]
		ON [ps2].[object_id] = [ps1].[object_id]
		AND [ps2].[database_id] = [ps1].[database_id]
		AND [ps2].[procedure_name] = [ps1].[procedure_name]
    WHERE [ps1].[database_name] IS NULL
    --AND [ps2].[total_elapsed_time] > 0
UNION
SELECT
-- Diff of elapsed time in both snapshots
        [ps2].[object_id]
		, [ps2].[database_name]
		, [ps2].[procedure_name]
		, [ps2].[cached_time]
		, CAST([ps2].[last_execution_time] AS DECIMAL (16, 4)) AS [last_execution_time]
		, CAST([ps2].[total_elapsed_time] - [ps1].[total_elapsed_time] AS DECIMAL (16, 4)) AS [elapsed_time_ms]
		, CAST([ps2].[last_elapsed_time] - [ps1].[last_elapsed_time] AS DECIMAL (16, 4)) AS [last_elapsed_time_ms]
		, CAST([ps2].[total_worker_time] - [ps1].[total_worker_time] AS DECIMAL (16, 4)) AS [total_worker_time_ms]
		, CAST([ps2].[last_worker_time] - [ps1].[last_worker_time] AS DECIMAL (16, 4)) AS [last_worker_time_ms]
		, CAST([ps2].[execution_count]  - [ps1].[execution_count] AS DECIMAL (16, 4)) AS [execution_count]
    FROM 
		[##ProcStats2] AS [ps2]
    LEFT OUTER JOIN [##ProcStats1] AS [ps1]
        ON [ps2].[object_id] = [ps1].[object_id]
	WHERE 
		[ps1].[database_name] IS NOT NULL
)
,[ProcStats] AS (
	SELECT
		[object_id]
		, [database_name]
		, [procedure_name] AS [procedure_name]
        , MAX([cached_time]) AS [cached_time]
		, MAX([last_execution_time]) AS [last_execution_time]
		, [total_elapsed_time] AS [total_elapsed_time]
		, [last_elapsed_time] AS [elapsed_time]
		, [last_worker_time] AS [last_worker_time]
		, [total_worker_time] AS [total_worker_time]
		, [execution_count] AS [execution_count]	
		, 100.0 * [total_elapsed_time] / SUM ([total_elapsed_time]) OVER() AS [percentage]
		, ROW_NUMBER() OVER(ORDER BY [object_id] DESC) AS [rownum]
    FROM 
		[DiffProcStats]
	WHERE
		database_name IS NOT NULL
	GROUP BY
		[object_id]
		, [database_name]
		, [procedure_name]
		, [total_elapsed_time]
		, [last_elapsed_time]
		, [last_worker_time]
		, [total_worker_time]
		, [execution_count]
)


SELECT
	[p1].[object_id]
    , [p1].[database_name] 
	, [p1].[procedure_name]
	, [p1].[cached_time]
	, [p1].[last_execution_time]
	, [p1].[total_elapsed_time] /1000 AS [total_elapsed_time_s]
	, [p1].[elapsed_time] /1000 AS [last_elapsed_time_s]
	, [p1].[last_worker_time] /1000 AS [last_worker_time_s]
	, [p1].[total_worker_time] /1000 AS [total_worker_time_s]
	, [p1].[percentage] AS [percentage]
	, [p1].[execution_count]
	, CAST([p1].[elapsed_time] / [p1].[execution_count] / 1000 AS DECIMAL (16, 4)) AS [avg_elapsed_time_s]
	, CAST([p1].[total_worker_time]/ [p1].[execution_count] /1000 AS DECIMAL (16, 4)) AS [avg_worker_time_s]
FROM [ProcStats] AS [p1]
INNER JOIN [ProcStats] AS [p2]
    ON [p2].[RowNum] <= [p1].[RowNum]
WHERE
	[p1].execution_count > 0
GROUP BY 
	[p1].[object_id]
	, [p1].[database_name] 
	, [p1].[procedure_name]
	, [p1].[cached_time]
	, [p1].[last_execution_time]
	, [p1].[total_elapsed_time]
	, [p1].[elapsed_time]
	, [p1].[last_worker_time]
	, [p1].[total_worker_time]
	, [p1].[execution_count]
	, [p1].[percentage]
HAVING 
	SUM ([p2].[Percentage]) - [p1].[Percentage] < 95 -- percentage threshold
ORDER BY
	[percentage] DESC;
GO

-- Cleanup
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##ProcStats1')
    DROP TABLE [##ProcStats1];
 
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##ProcStats2')
    DROP TABLE [##ProcStats2];

Thank again Paul for the idea.

 

 

Leave a Reply