Get Cached Plan Summary

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