Procedure cache (plan cache) size

Question

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.)

Answer 1

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.

Answer 2

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.