Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

do we have local memory pressure

By Patrick Akhamie,

/* -----EXPLANATION-----
If any single cache store grows too big, it indicates local memory pressure and SQL Server will start removing entries from that 
store only. This behavior prevents one store from using too much of the total system memory.
If a cache store reaches 75 percent of the cache plan pressure limit, described in Table 5-3, in single-page allocations or 50 
percent of the cache plan pressure limit in multipage allocations, internal memory pressure is triggered and plans will be removed 
from cache. For example, in the situation above we computed the cache plan pressure limit to be 5.4 GB. If any cache store exceeds 
75 percent of that value, or 4.05 GB in single-page allocations, internal memory is triggered. If adding a particular plan to cache 
causes the cache store to exceed the limit, the removal of other plans from cache will happen on the same thread as the one adding 
the new plan, which can cause the response time of the new query to be increased.
In addition to memory pressure occurring when the total amount of memory reaches a particular limit, SQL Server also indicates memory 
pressure when the number of plans in a store exceeds four times the hash table size for that store, regardless of the actual size of 
the plans. The queries below can be used to determine the number of buckets in the hash tables for the object store and the SQL store, 
and the number of entries in each of those stores.

Total article views: 1930 | Views in the last 30 days: 14
Related Articles

Memory Allocation

Memory Allocation


Memory pressure errors - need help.

"AppDomain is marked for unload due to memory pressure" in the SQL log file when backup to tape (TSM...


SQL Server 2005 Memory allocation

SQL Server 2005 Memory allocation


SQL Server 2005 Failover Clustering - Memory Allocation

How to set memory allocation at time of failover


Memory pressure issues

Our production server is having memory leak errors


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones