Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding the query plans from the procedure cache (because of memory pressure), I'd like to allocate enough memory to the procedure cache. According to this only 10% of the "visible target" memory in the 4Gb-64GB range is used for the procedure cache; I'd like this rate to be higher.

Is there any way to have some control on the size of the procedure cache? SQL Server 2008 or 2012; maybe some Enterprise Edition features?

Also, does this "10% of the visible target memory" rule also apply, if I explicitly set the min. memory for the SQL Server?

(I am aware of the KEEP PLAN / KEEPFIXED PLAN hints; I'm also aware of the advatages of preparing the statements over ad-hoc queries, so please don't recommend these possibilities. I'm specifically curious about controlling the procedure cache size.)

share|improve this question
1  
What about using the "optimize for ad hoc workloads" option? This places much smaller stubs in the cache until the same plan is used more than once, and only then does it cache the whole thing. Under the default configuration, and especially in cases where a lot of dynamically assembled SQL is in place, single-use plans often waste a lot of space in the cache. If you reduce the amount of wasted space these occupy, you leave more room for plans that are used more than once, and those are precisely the ones you want to keep around anyway. –  Aaron Bertrand Dec 16 '12 at 22:14
    
thanks, but I don't have ad-hoc queries that would waste the procedure cache resources –  slobo Dec 16 '12 at 22:27
    
Then can you please explain what you mean by "dinamycally-assembled, complicated SQL queries"? That sounds a lot like ad hoc queries to me. (And that's true whether you are submitting ad hoc SQL from an application or constructing ad hoc SQL from within a stored procedure.) See this post to see if you have these plans for real rather than just your interpretation of what the setting means: sqlskills.com/blogs/kimberly/post/… –  Aaron Bertrand Dec 16 '12 at 23:00
    
Well, I have a mechanism that dynamically produces queries. When a query is produced, sometimes it matches the structure of a previous query; and sometimes it has a new structure. As the query plan creation phase takes much time, I'd like to reuse the previous query plans as much as possible. I have plenty of phisycal memory, so it would be quite reasonable to use it for this purpuse. –  slobo Dec 16 '12 at 23:51
2  
"I do not want to change my question" .. that reeks of "I asked a bad question"... Which is grounds for closing as unhelpful to anyone else in the future. –  jcolebrand Dec 18 '12 at 22:44
show 7 more comments

migrated from stackoverflow.com Dec 21 '12 at 4:05

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 5 down vote accepted

You can't control plan cache size manually. The following Connect item describes both the fact that they won't be adding a knob any time soon and also that Resource Governor might be marginally useful in a few narrow use cases:

http://connect.microsoft.com/SQLServer/feedback/details/293188/amount-of-ram-for-procedure-cache-should-be-configurable

However, as I've alluded to in the comments, there are other ways you can make the plan cache more effective (e.g. use the "optimize for ad hoc workloads" setting). Kimberly explains this in greater detail, but essentially, this setting places much smaller stubs in the cache until the same plan is used more than once, and only then does it cache the whole thing. Under the default configuration, and especially in cases where a lot of dynamically assembled SQL is in place, single-use plans often waste a lot of space in the cache. If you reduce the amount of wasted space these occupy, you leave more room for plans that are used more than once, and those are precisely the ones you want to keep around anyway.

share|improve this answer
    
The MS issue was closed in 2007. Are you sure that in the 2012 edition (or 2012 Enterprise Edition) still there is no such possibility? –  slobo Dec 17 '12 at 2:28
    
Yes, I am sure. Memory management is greatly improved in SQL Server 2012, but there is still no control exposed for size of the plan cache. (Though I do believe Sybase does have a similar server configuration option.) –  Aaron Bertrand Dec 17 '12 at 2:29
    
Do you maybe know how the "10% of the visible target memory" rule works, if I also set the min. memory for the SQL Server? For example: If I have 16GBytes of RAM, and I set the min. memory for SQL Server to 14 GBytes, what will be the max. size of the procedure cache? Will it be 4GBytes*75% + (16-4)GBytes*10% = 4.2GBytes (regardless of the min. memory setting)? –  slobo Dec 17 '12 at 3:13
    
@slobo Since you can't control it, I still think you're asking the wrong question - and not that I'm too lazy to do the math, I just think it's pointless - if the answer is 4.2GB or 4.4GB or 3.95GB, what are you going to do differently? <shrug> –  Aaron Bertrand Dec 18 '12 at 23:07
    
I was hoping that if I set the min. memory for the SQL Server, the calculation is different, and the procedure cache would be much bigger. –  slobo Dec 19 '12 at 2:24
show 1 more comment

FYI, if you need to find out the size of your procedure cache, run the following command:

dbcc memorystatus

It will tell you the size of your procedure cache measured in pages - to convert to KB multiply this figure by 8.

It also tells you how many procedures are cached which can be useful for a variety of reasons including if you're trying to figure out if there are too many ad-hoc queries running against the database.

If you want to adjust the percentage memory consumed by the procedure cache then adjust the MAX memory (not the min memory) available to the server. 75% of the first 4GB is available for the procedure cache and 10% of everything else. I don't see any advantage to do this however. You could, in theory, install multiple instances of SQL Server with max memory of 4GB to get an allowance of 75% of memory to be used by the procedure cache.... I don't see the advantage but then I don't know your system. It sounds like you've got a LOT of business logic in the database layer.

share|improve this answer
    
Actually I have no business logic in the database tier, but I have really big SQL SELECTs, and I have many of them, and they are used repeatedly. –  slobo Dec 21 '12 at 21:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.