What is causing sql Cache Pressure

05 February,2017 by Jack Vamvas

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

 

CacheType

Total Plans

Total MBs

Avg Use Count

Total MBs – USE Count 1

Total Plans – USE Count 1

Adhoc

59470

2876.858474

14

1476.582992

27695

Prepared

5566

474.679687

3542

332.09375

4136

Proc

206

130.351562

58133

16.171875

33

UsrTab

2

0.4375

7

0.21875

1

Check

15

0.414062

5

0.164062

5

View

457

51.054687

35

0.109375

2

Trigger

8

3.09375

104

0

0

 

 

 

 

 

 

 

 

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on What is causing sql Cache Pressure


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer