What is causing sql Cache Pressure

Question: I’ve started looking at some sql memory pressure issues, and drilling down into the root cause. I can see that the query plan cache is busy , but I’d like to know how can I extract a detailed breakdown of the the cache types. Using this information – I can begin identifying activity which may be causing this pressure.

I’ve looked at the  Find Table Scans in Query Plan Cache (SQL Server DBA)   . Although this gives me some counts on query plans , it doesn’t give me a breakdown of the cache types

Answer: Having information about the cache types AND space taken up by these caches supplies some clues for the initial action you should take. If you drill in further it may lead you to code inefficiencies, that can easily be fixed.

This query by Kimberly Tripp – will identify the Cache Types . Importantly there are columns identifying where the usage of the query plan is used only once.

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

Armed with this information you can develop some strategies around dealing with the cache bloat. SQL Server has a number of configurables , and depending on your circumstances , there are different possibilities.

---Enable optimize for adhoc workloads

---Review adhoc statements and consider placing in a stored procedure

Read More

SQL Server Performance ,memory pressure and memory usage

SQL Server page life expectancy and memory bottleneck

SQL Server - Error 701 - There is insufficient system memory