SQL Server – Script to Check Plan Cache Bloat

SQL Server will look at each query and check if it has a plan cache already compiled for.  If it has, then it will reuse the plan in the cache. If it does not have, then an execution plan is compiled and added to the plan cache.

When identical queries ran over many times, its plan will be reused. This will save resources from having to compile repeatedly.

Now what happens when you have hundreds or thousands of different queries that are only executed once? In this case there are no execution plan to be reused. This is what we call PLAN CACHE BLOAT.

Plan cache bloat is bad because it steals memory from the buffer pool that are better used for database pages. When you execute the script  below, and if you see “Adhoc” Cache type to be on top, then that means that there are a lot of queries that are executed once and are causing plan cache bloat.

SELECT 
    objtype AS [CacheType]
    , count_big(*) AS [Total Plans]
    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
    , avg(usecounts) AS [Avg Use Count]
    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM 
    sys.dm_exec_cached_plans
GROUP BY 
    objtype
ORDER BY 
    [Total MBs - USE Count 1] DESC
GO

Plan cache bloat can be prevented by enabling “Optimize for Adhoc Workloads” by only caching the full execution plan of queries that are known to be used more than once.

To enable the optimize for ad hoc workloads setting, use sp_configure, as shown below.

EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter