Hi,
Following on from my script that looks at poorly performing queries, here’s one that helps to find procedures that may be performing poorly. It makes use of sys.dm_exec_procedure_stats and produces a result set that shows the following:
- [Object]
- [SQLText]
- [QueryPlan]
- [execution_count]
- [Avg. CPU Time(s)]
- [Avg. Physical Reads]
- [Avg. Logical Reads]
- [Avg. Logical Writes]
- [Avg. Elapsed Time(s)]
- [Total CPU Time(s)]
- [Last CPU Time(s)]
- [Min CPU Time(s)]
- [Max CPU Time(s)]
- [Total Physical Reads]
- [Last Physical Reads]
- [Min Physical Reads]
- [Max Physical Reads]
- [Total Logical Reads]
- [Last Logical Reads]
- [Min Logical Reads]
- [Max Logical Reads],
- [Total Logical Writes]
- [Last Logical Writes]
- [Min Logical Writes]
- [Max Logical Writes]
- [Total Elapsed Time(s)]
- [Last Elapsed Time(s)]
- [Min Elapsed Time(s)]
- [Max Elapsed Time(s)]
- cached_time
- last_execution_time
I hope you find it useful.
SET NOCOUNT ON;
/*****************************************************
Setup a temporary table to fetch the data we need from
sys.dm_exec_procedure_stats.
******************************************************/
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp
(
database_id INT,
object_id INT,
[SQLText] VARCHAR(MAX),
[QueryPlan] XML,
[execution_count] bigint,
[Avg. CPU Time(s)] DECIMAL(28,2),
[Avg. Physical Reads] bigint,
[Avg. Logical Reads] bigint,
[Avg. Logical Writes] bigint,
[Avg. Elapsed Time(s)] DECIMAL(28,2),
[Total CPU Time(s)] DECIMAL(28,2),
[Last CPU Time(s)] DECIMAL(28,2),
[Min CPU Time(s)] DECIMAL(28,2),
[Max CPU Time(s)] DECIMAL(28,2),
[Total Physical Reads] bigint,
[Last Physical Reads] bigint,
[Min Physical Reads] bigint,
[Max Physical Reads] bigint,
[Total Logical Reads] bigint,
[Last Logical Reads] bigint,
[Min Logical Reads] bigint,
[Max Logical Reads] bigint,
[Total Logical Writes] bigint,
[Last Logical Writes] bigint,
[Min Logical Writes] bigint,
[Max Logical Writes] bigint,
[Total Elapsed Time(s)] DECIMAL(28,2),
[Last Elapsed Time(s)] DECIMAL(28,2),
[Min Elapsed Time(s)] DECIMAL(28,2),
[Max Elapsed Time(s)] DECIMAL(28,2),
cached_time DATETIME,
last_execution_time DATETIME,
[object] VARCHAR(MAX) NULL
)
/*****************************************************
Fetch the data we need from
sys.dm_exec_procedure_stats.
******************************************************/
insert into #temp
select
database_id,
object_id,
st.text,
ep.query_plan,
execution_count,
-- Averages
CAST(total_worker_time * 1.0 / execution_count / 1000000.0 as DECIMAL(28,2)) AS [Avg. CPU Time(s)],
total_physical_reads / execution_count AS [Avg Physical Reads],
total_logical_reads / execution_count AS [Avg. Logical Reads],
total_logical_writes / execution_count AS [Avg. Logical Writes],
CAST(total_elapsed_time * 1.0 / execution_count / 1000000.0 as DECIMAL(28,2)) AS [Avg. Elapsed Time(s)],
-- CPU Details
CAST(total_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Total CPU Time(s)],
CAST(last_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Last CPU Time(s)],
CAST(min_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Min CPU Time(s)],
CAST(max_worker_time / 1000000.0 as DECIMAL(28,2)) AS [Max CPU Time(s)],
-- Physical Reads
total_physical_reads AS [Total Physical Reads],
last_physical_reads AS [Last Physical Reads],
min_physical_reads AS [Min Physical Reads],
max_physical_reads AS [Max Physical Reads],
-- Logical Reads
total_logical_reads AS [Total Logical Reads],
last_logical_reads AS [Last Logical Reads],
min_logical_reads AS [Min Logical Reads],
max_logical_reads AS [Max Logical Reads],
-- Logical Writes
total_logical_writes AS [Total Logical Writes],
last_logical_writes AS [Last Logical Writes],
min_logical_writes AS [Min Logical Writes],
max_logical_writes AS [Max Logical Writes],
-- Elapsed Time
CAST(total_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Total Elapsed Time(s)],
CAST(last_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Last Elapsed Time(s)],
CAST(min_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Min Elapsed Time(s)],
CAST(max_elapsed_time / 1000000.0 as DECIMAL(28,2)) AS [Max Elapsed Time(s)],
cached_time,
last_execution_time,
null
from
sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ep
where
database_id <> 32767
/*****************************************************
This section of code is all about getting the object
name from the dbid and the object id.
******************************************************/
-- Declare a Cursor
DECLARE FetchObjectName CURSOR FOR
SELECT
object_id, database_id
FROM
#temp
-- Open the cursor
OPEN FetchObjectName
-- Declare some vars to hold the data to pass into the cursor
DECLARE @var1 INT,
@var2 INT
DECLARE @sql VARCHAR(MAX)
DECLARE @object VARCHAR(MAX)
-- Create a temporary table to hold the result of the dynamic SQL
IF OBJECT_ID('tempdb..#object') IS NOT NULL DROP TABLE #object
CREATE TABLE #object
(
objectname VARCHAR(MAX)
)
-- Loop through the 20 records from above and fetch the object names
FETCH NEXT FROM FetchObjectName INTO @var1, @var2
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
-- Set the SQL we need to execute
SET @sql = 'USE [' + DB_NAME(@var2) + '];
SELECT OBJECT_SCHEMA_NAME(' + CONVERT(VARCHAR(MAX),@var1) + ',' + CONVERT(VARCHAR(MAX),@var2) + ') + ''.'' + ' + 'OBJECT_NAME(' + CONVERT(VARCHAR(MAX),@var1) + ');'
-- Make sure the table is empty!
TRUNCATE TABLE #object
-- Fetch the name of the object
INSERT INTO #object
EXEC(@sql)
-- Set the object name to the local var.
SELECT @object = objectname FROM #object
-- Update the original results
UPDATE #temp
SET
[Object] = RTRIM(LTRIM(@object))
WHERE
object_id = @var1
and database_id = @var2
-- Go around the loop....
FETCH NEXT FROM FetchObjectName INTO @var1, @var2
END
CLOSE FetchObjectName
DEALLOCATE FetchObjectName
/*****************************************************
Output the final restults....
******************************************************/
SELECT TOP 50
db_name(database_id) + '.' + [Object] AS [Object],
[SQLText],
[QueryPlan],
[execution_count],
[Avg. CPU Time(s)],
[Avg. Physical Reads],
[Avg. Logical Reads],
[Avg. Logical Writes],
[Avg. Elapsed Time(s)],
[Total CPU Time(s)],
[Last CPU Time(s)],
[Min CPU Time(s)],
[Max CPU Time(s)],
[Total Physical Reads],
[Last Physical Reads],
[Min Physical Reads],
[Max Physical Reads],
[Total Logical Reads],
[Last Logical Reads],
[Min Logical Reads],
[Max Logical Reads],
[Total Logical Writes],
[Last Logical Writes],
[Min Logical Writes],
[Max Logical Writes],
[Total Elapsed Time(s)],
[Last Elapsed Time(s)],
[Min Elapsed Time(s)],
[Max Elapsed Time(s)],
cached_time,
last_execution_time
FROM
#temp
ORDER BY
[Avg. Logical Reads] DESC
