Plan Cache Pressure

Last Post 01 May 2013 07:57 PM by rerichards. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Resolved
rerichards
New Member
New Member

--
30 Apr 2013 01:39 PM
I am interested in knowing what approach one might take from a purely DMV perspective to determine pressure on the plan cache. We are running SQL 2008R2.

For example, if the cache is getting flushed because of a large plan (or plans), would a person run the following repeatedly (at least quite frequently) over a period of time and look for a significant drop in the count of cached plans?

SELECT COUNT(*) As NumberOfPlansInCache,
MAX(size_in_bytes) / 1024 / 1024 As MaxPlanSizeInMB
FROM sys.dm_exec_cached_plans

Or, is there another DMV approach that would help in analyzing plan cache pressure?
gunneyk
New Member
New Member

--
01 May 2013 06:05 PM
Well there are a lot of factors that determine how large the cache is and how many plans are in there etc. I typically don't worry too much about how many are in the cache or who large they are as SQL2008 & up does a much better job at managing that. This query will show you how many of each type are in the cache along with sizes better than what you posted.

-- Cache objects
SELECT TOP 10
LEFT([name], 20) as [name],
LEFT([type], 20) as [type],
[single_pages_kb] + [multi_pages_kb] AS cache_kb,
[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC
-- Procedure Cache
-- Object Plans (CACHESTORE_OBJCP)
-- SQL Plans (CACHESTORE_SQLCP)
-- Bound Trees(algebrizer) (CACHESTORE_PHDR)
-- Extended Stored Procedures (CACHESTORE_XPROC).

But what I would concentrate more on is which plans are not getting reused more than anything. If you have lots of compiles and or recompiles you will spend a lot of resources managing and calculating new plans. Take a look at the perf counters for compile & recompiles. You can also look at the usecounts column of the exec_cahced_plans to see which plans don't get reused. If you can fix queries that don't reuse plans you can achieve better performance all the way around and minimize any cache pressure.
rerichards
New Member
New Member

--
01 May 2013 07:57 PM
Thanks gunneyk. That's the direction I needed.
You are not authorized to post a reply.

Acceptable Use Policy