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')