DMV Queries - Get Cached Plan Summary

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        2 Aug 2012

Description

Provides a summary of all compiled query plans currently in the plan cache
If a Database Name is supplied then the scope will be restricted to the database, otherwise all plans on the server will be returned. 

Code

Function:


DROP FUNCTION dbo.udf_GetCachedPlanSummary
GO

CREATE FUNCTION dbo.udf_GetCachedPlanSummary (@DatabaseName SYSNAME)
RETURNS @CachedPlanSummary TABLE
  
(
      
CacheObjectType             VARCHAR(100),          
      
ObjectType                  VARCHAR(100),          
      
UsageCount                  INT,            
      
SizeInBytes                 INT,
      
DatabaseName                SYSNAME NULL,
      
ParentQuery                 VARCHAR(MAX)
   )
AS
/***************************************************************
Purpose:   To retrieve a summary of all plans currently in the plan
           cache for one or all DBs on the server.
           With SQL 2005, the DB will be NULL for ad-hoc plans.
          
           The following DMVs are used:
               sys.dm_exec_cached_plans    - contains a record of all cached plans
               sys.dm_exec_sql_text        - contains text of SQL statements          
          
Author:        Jimmy May
History:   Unknown - Initial Issue
           1 Aug 2012 - ChillyDBA - Converted to a table valued function

****************************************************************/

BEGIN

   INSERT
@CachedPlanSummary
      
(
          
CacheObjectType,
          
ObjectType,        
          
UsageCount,
          
SizeInBytes,
          
DatabaseName,
          
ParentQuery
      
)  
  
SELECT
      
c.cacheobjtype          AS CacheObjectType,
      
c.objtype               AS ObjectType,          
      
c.usecounts             AS UsageCount,
      
c.size_in_bytes         AS SizeInBytes,
      
DB_NAME(t.dbid)         AS DatabaseName,
      
t.TEXT                  AS ParentQuery
  
FROM sys.dm_exec_cached_plans AS c
  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
  
WHERE c.cacheobjtype = 'Compiled Plan'
  
AND t.dbid = ISNULL(DB_ID(@DatabaseName), t.dbid)
  
ORDER BY c.usecounts DESC

   RETURN
END


Test Code:


SELECT * FROM dbo.udf_GetCachedPlanSummary(NULL)
SELECT * FROM dbo.udf_GetCachedPlanSummary('ReportServer')

ċ
udf_GetCachedPlanSummary.sql
(2k)
Andy Hughes,
Aug 2, 2012, 6:52 AM
Comments