Home > Memory, Uncategorized > Procedure Cache – Memory Pressure

Procedure Cache – Memory Pressure


_MG_2719Hi everyone,

Recently I was dealing with unusual behavior of Procedure Cache caused pseudo-random execution plans to be removed from memory (SQL Server 2005, 2008, 2008R2). As a result, under a large workload there was observed locks contention related to schema locks required in order to compile and insert a procedure into Procedure Cache. This is a predictable behavior of heavy loaded systems. But, the main question here is – how can we identify this type of issues related to so called Memory Pressure in Procedure Cache?

The process when SQL Server becomes pushing out execution plans is called “Memory Pressure”. There are two different types of pressure: Global and Local. But, I will not go dipper explaining in-depth this concept, and if you wish you can make you familiar with it reading relevant information in the Internet.

Description

Usually this type of troubles is difficult to identify and confirm somehow in order to undertake meaningful actions. According to my understanding, there is only one direct way which can be used in order to confirm that you have got memory pressure (here I am talking about real evidences). This is Profiler. If you specify all required events such as SP:CacheHit, SP:CacheInsert, SP:CacheRemove, SP:CacheMiss including SubeventClass you can observe whether or not execution plans are pushed out from Procedure Cache. In the case if you observe consequent SP:CacheRemove, SP:CacheInsert and query execution afterwords you may deal with Memory Pressure. You can see this behavior even if your procedures are well written and free from recompilation. The key point here is – if SQL Server detects not enough memory it will cleanup the Procedure Cache on the same thread it uses for the current quest execution. SQL Server does it on demand that is why it cannot be done asynchronously using some other processes/threads. Under different circumstances SQL Server can remove up to 16 plans from procedure cache at a time. Taking all mentioned above into account we can conclude that under Memory Pressure in order to compile an execution plan SQL Server may remove a small portion of outdated execution plans first and only after that insert a new one. Under huge workload you may observe several consequent SP:CacheRemove events which is a clear sign that you should investigate memory pressure in-depth in your environment because you may have massive execution plans removal.

Solution

I have read many internals/documents describing how SQL Server treats Procedure Cache regarding to different version of SQL Server, memory pressure thresholds, etc. As a result, I come up with a script which can be quite useful in order to determine whether or not you have Local/Global Memory Pressure in Procedure Cache without running Profiler. The script can be easily used like some sort of relatively lightweight memory monitoring querying DM Views when it is required. Be aware, this script utilizes only indirect indicators of possible Memory Pressure which are computed on base of memory distribution/usage.

The script represents further several areas which are worth to investigate or at least keep tracking:

  • Visible memory
  • Cache Pressure Limit
  • The size of all the cache stores holding plans
  • The number of buckets in these hash tables
  • The number of entries in each of those stores
  • Sizes of cache stores
  • Memory contention during descriptors allocation to Parocedure Cache
  • This wait type indicates that there are a large number of concurrent compilations

In this topic I was talking about quite a dark area of how SQL Server implements Procedure Cache maintenance under memory Pressure that is why I am more than happy to read comments from all readers and enhance/improve my understanding of this concept.

Sources

You can download T-SQL sources using the links below:

  1. Procedure Cache – Pressure Analysis.sql

.

Source:Aleksey Fomchenko (https://sqlconsulting.wordpress.com)

Reference: Aleksey Fomchenko (https://sqlconsulting.wordpress.com)

.

Advertisements
Categories: Memory, Uncategorized
  1. Alex Kostin
    March 1, 2017 at 8:56 pm

    Hi Alexsey, Good article, thanks a lot. I have tried to download the script from box, but I think link is not valid. Is there any way you can share it. Thanks
    Alex Kostin

    • March 1, 2017 at 9:50 pm

      Hi Alex.
      Yes, the link was broken in some reason.
      I uploaded the file to a more reliable storage.
      Please, try to download again.
      And good luck!

    • March 22, 2017 at 9:54 pm

      The link has been uploaded.
      Sorry for the confusion the previous one did not work.
      Good luck Alex Kostin.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: