THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Q & A on my Plan Cache Sizing article in SQL Server Magazine

My December article in SQL Server Magazine, called Managing Plan Cache Size, expands on the information I talked about in a post last month on the limits of plan cache

A reader of the article ask some questions on the SQL Server Magazine web site, and I have decided to use my blog to post the answers.

Q:  Could you give me an illustration how to calculate target memory please? Let say there is a 64 bit system (64 bit hardware, windows 2003 64 bit enterprise edition, SQL Server 2005 Enterprise edition 64 bit, memory 30 GB), max server memory (in sp_configure) is between 16 (min value) - 30 GB (max value)).

A: Target memory refers to the maximum physical memory that can be committed to the buffer pool and ideally is the lesser of the values you’ve configured for “max server memory” and the total amount of visible physical memory available to the OS. On a 64bit system, all your memory is visible, and your max server memory is the same as your physical memory, so target ideally will be 30GB. You can see the target memory value in the metadata view sys.dm_os_sys_info (where it's called bpool_commit_target), along with the value for visible memory (called bpool_visible).

However, there are other factors that can affect the target value. The BOL entry for sys.dm_os_sys_info defines bpool_commit_target as follows:

"Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters."

The exact formula is not published. It's best to just use sys.dm_os_sys_info to see what your system's target memory value is.

 

Q:  In your article, you give an example how to calculate plan-cache pressure limit, for example for SQL Server 2005 RTM and SP1, it calculates for on 28 target memory as follows : (.75 x 8 GB) + (.5 x 20 GB) = 6 GB. Then you add to 10 GB so pressure limit is 16 GB. Where does 10 GB come from?

A: You have rephrased the expression incorrectly. This is what the article says:

                (.75 x 8 GB) + (.5 x 20 GB) = 6 GB + 10 GB = 16 GB

If you do the math, you’ll see that the first term in parentheses (.75 x 8 GB) is equal to 6 GB and the second term (.5 x 20 GB) is equal to 10 GB, so we add the 6 and the 10 together.

 

Q. You mention "plan-cache pressure limit". Does it mean that when , let's say pressure limit of 28 GB is 16 GB, 16 GB is reached then SQL Server couldn't remove plan cache to make it less than 16 GB, is my understanding right?

A: No, in the above example, we calculated the pressure limit to be 16 GB. That means when your SQL Server hits 16 GB of memory for plan cache, it will start removing plans from cache to keep cache from getting too big.

 

Q: You show how to remove a prepared query by creating a plan guide with recompile hint; is it possible to remove non-prepared query plan on the following sample:

EXEC sp_create_plan_guide @name = N'RemovePlan1', @stmt = N' SELECT * FROM Sales WHERE ContactID < 3', @type = N'SQL', @module_or_batch = NULL, @params = N', @hints = N'OPTION(RECOMPILE)';

and I issue query twice as below shown SELECT * FROM Sales WHERE ContactID < 3

When I check in syscacheobjects with the query:
    
SELECT usecounts as uses, sql FROM sys.syscacheobjects WHERE dbid = db_id('AdventureWorks');

It shows that the non-prepared query plan is reused with count equal to 2.

A: Althought it is possible to create a plan guide for an adhoc (non-prepared) query, your @stmt parameter must be absolutely exactly the same as the query you will run. In your plan guide creation, you have an extra space in front of the SELECT, and your @module_or_batch parameter is incorrect; it should be NULL. A correct plan guide can be used for adhoc queries, but it will NOT remove the adhoc plan from cache when you drop the plan guide, like a plan guide for a prepared query will. You will have to wait for the next version (SQL Server 2008) to get that behavior.

I hope this helps!

~Kalen

Published Saturday, December 15, 2007 8:00 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kasim Wirama said:

Hi Kalen

Thanks for your explanations on my questions. I am wondering which dynamic/catalog view to get information about current memory consumption for plan cache. So I can monitor memory consumed for plan cache and plan cache pressure limit.

Is there any techniques that I can use to raise plan cache pressure limit, let say I want to raise from 16 GB to 32 GB?

Thank you.

Kind regards,

Kasim Wirama

December 20, 2007 6:35 PM
 

Kalen Delaney said:

Kasim

This is really a much bigger topic that I have presented here and goes well beyond what I could put in a simple blog post. I only wanted to make the point that there wasn't a fixed upper limit. There are multiple plan caches for different types of plans, and each one would have to be examined separately.  For the bigger picture you should read my book: Inside SQL Server: Query Tuning and Optimization.

I am not aware of any way to change the pressure limit; I'd be very surprised if there was such a way.

December 31, 2007 2:21 AM
 

jabed201 @ windows 8 said:

Thanks for showing the calculation. It helps me a lot. But what is buffer pool. I want to know about it.

June 17, 2011 7:47 PM
 

Racim BOUDJAKDJI said:

Thanks for pointing out this problem few people are aware of.  

I have had a CPU going up to roof and havenot slept 48 hours to understand that some ORM was continuously causing paging within the plan when the pressure was reached due to dynamic SQL submitted: the engine kept freeing up space before running anything.  

To troubleshoot, I had to go dig directly in the plan cache and determine the basic structure of the processes causing the pain and alleviate the pain (the servers were already 96GB RAM!).

I confirm that the memory pressure is a *design* bug created on SQL 2005 SP3.  (I opened a severity A incident on this and asked for a hotfix ) The only indirect way to alleviate the problem is to identify/stop the process that is bloating the cache, set PARAMETERIZATION to FORCED and wrap up the most consuming statements into stored procedures.  Usually few statement cause most plan cache pressure.

Hope this helps.

November 20, 2014 6:42 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement