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.

FROM sys.dm_exec_cached_plans 

FROM sys.dm_exec_query_stats 

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.


DECLARE @DatabaseName sysname
    ,@ObjectName sysname 

SELECT @DatabaseName = 'msdb'
    ,@ObjectName = 'sp_jobhistory_row_limiter';
WITH PlanSearch
AS (
      SELECT qp.dbid
            ,DB_NAME(qp.dbid) as DatabaseName
            ,OBJECT_NAME(qp.objectid, qp.dbid) as ObjectName
      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)
AS (
      SELECT ps.DatabaseName
        ,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
    FROM PlanSearch ps
            LEFT OUTER JOIN sys.dm_exec_query_stats qs ON ps.plan_handle = qs.plan_handle
    GROUP BY ps.DatabaseName
SELECT ps.DatabaseName
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.

3 thoughts on “Searching for Plans

  1. Pingback: SQL Server Central
  2. Excellent post! I had to tweak the code a little bit to run on SQL 2005, but when I got it running, dang it was amazing!

    Thank you for posting this article!


Comments are closed.