Really Search Cache for Execution Plans

Today’s stored procedure that you can use in your DBA database is a re-hash of a post I did a couple months ago.  The reason for re-hashing the procedure is that when I used it last week I found out there were some serious issues with its results.  Some plans were being duplicated and the performance statistics weren’t accurate because of this.  And why not fix the issues and make a post out of it.

In the previous version, I was using the execution count from sys.dm_exec_query_stats to determine how often a plan was being executed.  In the DMV though the SQL statements in the may execute in varying counts depending on conditional logic.

Instead of using the execution count from sys.dm_exec_query_stats the procedure now uses usecount from sys.dm_exec_cached_plans; which represents how often the plan was used.  The execution count represented how often the statements in the plan were executed.

The stored procedure accepts the following parameters:

• @Database: The database to search for plans within.  This value can be NULL.
• @ObjectName: The name of the procedure to search for plans for.  This value can be NULL.

The procedure can use either or none of the variables.  Obviously with both variables empty the results will be for all cached plans on the server.

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Utility')
EXEC('CREATE SCHEMA [Utility] AUTHORIZATION [dbo]')
GO

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('Utility.CachedPlanSearch'))
DROP PROCEDURE [Utility].[CachedPlanSearch]
GO

/*============================================================
Procedure: [Utility].[CachedPlanSearch]
Author: Jason Strate
Date: June 1, 2009

Synopsis:
Searches cache for all occurances of a plan based on an
procedure and or database name. Results assumes that average
execution time for returned results are less than 24 hours.

Exec Utility.CachedPlanSearch
@DatabaseName = 'msdb'
,@ObjectName = 'sp_sqlagent_get_startup_info'

============================================================
Revision History:
Date: By Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE Utility.CachedPlanSearch
(
@DatabaseName sysname = NULL
,@ObjectName sysname = NULL
)
AS

;WITH cteExecInfo
AS (
SELECT DB_NAME(st.dbid) AS database_name
,OBJECT_NAME(st.objectid, st.dbid) AS object_name
,cp.usecounts -- Use in place of qs.execution_count for whole plan count
,CAST(SUM(qs.total_worker_time)/(cp.usecounts*1.) as decimal(12,2)) AS avg_cpu_time
,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(cp.usecounts*1.) as decimal(12,2)) AS avg_io
,SUM(qs.total_elapsed_time)/(cp.usecounts)/1000 as avg_elapsed_time_ms
,st.text AS sql_text
,qs.plan_handle
FROM sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE (DB_NAME(st.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)
AND (OBJECT_NAME(st.objectid, st.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)
GROUP BY st.dbid, st.objectid, cp.usecounts, st.text, qs.plan_handle
)
SELECT cte.database_name
,cte.object_name
,cte.usecounts
,cte.avg_cpu_time
,cte.avg_io
,CONVERT(varchar, DATEADD(ms, cte.avg_elapsed_time_ms, 0), 114) AS avg_elapsed_time
,qp.query_plan
,cte.sql_text
FROM cteExecInfo cte
OUTER APPLY sys.dm_exec_query_plan(cte.plan_handle) qp
ORDER BY cte.usecounts DESC

I’ve often found this procedure useful when people complain about the execution of a procedure. Instead of pulling out performance tools and running test versions of the procedure this will provide the last plan that was used for the procedure.