Searching for Plans

A while back I wrote a post on a procedure I created for the DBADiagnostics database that I talk about from time to time.  This procedure allowed users to search the procedure cache to find plans for procedures by database name and object name.  This had worked out pretty good until I noticed something the other day.

The procedure is using the sys.dm_exec_query_stats as the based table for the query to build itself upon.  The trouble with using this DMV is that it doesn’t have all of the plans listed from the procedure cache.  Only those that currently have stats stored for sql_handles.

About the same time I started writing this post, I noticed a post by Adam Machanic (twitter | blog) in which he puts out a warning to those using sys.dm_exec_query_stats.  In that post, he discusses how the ALTER TABLE statement can result in a batch missing this DMV.

Considering all this lends itself to the question, is there a better DMV that can be used to find plans in the procedure cache?

Use sys.dm_exec_cached_plans Instead

Another DMV that has this information in it is the DMV sys.dm_exec_cached_plans.  This DMV is designed to provide a list of all cached plans in the procedure cache.  To illustrate the difference, lets take a look at the following two queries.

SELECT COUNT(DISTINCT plan_handle) 
FROM sys.dm_exec_cached_plans 
GO 

SELECT COUNT(DISTINCT plan_handle) 
FROM sys.dm_exec_query_stats 
GO

On my system, the following results are returned:

The first value is the number of plan_handles in sys.dm_exec_cached_plans.  The second is the number of plan_handles in sys.dm_exec_query_stats.  There is a substantial difference between the two.  This is something you’d be expecting based on the information at the start of this post.

Query to Find Cached Plans

Now that I’ve outlined the issues with my original post, lets take a look at a script that will query the procedure cache to look for plans stored for stored procedures and other database objects.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

DECLARE @DatabaseName sysname
    ,@ObjectName sysname 

SELECT @DatabaseName = 'msdb'
    ,@ObjectName = 'sp_jobhistory_row_limiter';
    
WITH PlanSearch
AS (
      SELECT qp.dbid
            ,qp.objectid
            ,DB_NAME(qp.dbid) as DatabaseName
            ,OBJECT_NAME(qp.objectid, qp.dbid) as ObjectName
            ,cp.usecounts
            ,cp.plan_handle
      FROM sys.dm_exec_cached_plans cp
            CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT) qp
    WHERE cp.cacheobjtype = 'Compiled Plan'
      AND (DB_NAME(qp.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)
    AND (OBJECT_NAME(qp.objectid, qp.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)
)
,PlansAndStats
AS (
      SELECT ps.DatabaseName
        ,ps.ObjectName 
        ,ps.usecounts -- Use in place of qs.execution_count for whole plan count
        ,CAST(SUM(qs.total_worker_time)/(ps.usecounts*1.) as decimal(12,2)) AS avg_cpu_time 
        ,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(ps.usecounts*1.) as decimal(12,2)) AS avg_io 
        ,SUM(qs.total_elapsed_time)/(ps.usecounts)/1000 as avg_elapsed_time_ms
        ,ps.plan_handle 
    FROM PlanSearch ps
            LEFT OUTER JOIN sys.dm_exec_query_stats qs ON ps.plan_handle = qs.plan_handle
    GROUP BY ps.DatabaseName
        ,ps.ObjectName 
        ,ps.usecounts 
            ,ps.plan_handle
)
SELECT ps.DatabaseName
    ,ps.ObjectName
      ,ps.usecounts
      ,ps.avg_cpu_time
      ,ps.avg_io
      ,ps.avg_elapsed_time_ms
      ,qp.query_plan
      ,ps.plan_handle
FROM PlansAndStats ps
      CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp 

Executing the script above provides the following output:

The output includes the name of the object being sought and a link to the plan from the procedure cache.  Included with this is some basic performance information from sys.dm_exec_query_stats that can be useful in determining performance variations between the plans returned.

Procedure to Find Plans

For those that like to take these scripts and build stored procedures for them.  I’ve also included a script below that includes this information.