Hello,
I’ve been asked the question in title at least twice in the past month, but the question is really incomplete – I would rather be asked “I have too many single use plans taking up a lot of memory in my server, can I do something about it?”.
The answer would be the classic “it depends”, and it depends on the answer to some more questions:
So for the sake of this post, let’s say that for a given SQL Server we conclude that a great deal of memory is being taken by single use plans – for this scenario, ~10% of total committed memory, which is 30GB on a 32GB box.
Checking for clock hand moves over the CACHESTORE_SQLCP (where plans for adhoc and prepared plans are), we find these are frequent – but at the moment not representing a severe memory issue in the server. With almost 4GB of memory being used by single use plans, I find that this value it is too much of a waste, when that memory could be used more efficiently.
In many of these cases we might default to thinking about single use adhoc code being executed, right? And while the recommendation might usually be to refactor the code to run in stored procedures instead of running adhoc code (other strategies do exist to leverage plan-reuse as much as possible like sp_executesql ), if a more administrative solution is needed, we have the “ Optimize for Ad hoc Workloads ” server option at our disposal. This will alleviate the scenario I started out by describing – think of this as a workaround to make conservative use of memory, when your workload relies heavily on adhoc code leading to plan cache bloating.
But what if you look in the sys.dm_exec_cached_plans , and you find the most prevalent single use plans are not *real* adhoc plans, but rather prepared plans? In this case the “ Optimize for Ad hoc Workloads ” server option doesn’t fit our needs. So is there any kind of administrative workaround for this scenario?
Below is the code I used in a couple occasions as a temporary workaround until a permanent, code-based solution could be implemented that would limit the high rate of single use plans. This can be used inside a job, that you schedule according to your needs, and we set what is the threshold of memory taken by prepared plans we consider to be the limit, after which we trigger a cache clear operation using DBCC FREESYSTEMCACHE (specifying WITH MARK_IN_USE_FOR_REMOVAL), and output a summary of what was done. Please see note below before using this .
NOTE : keep in mind that clearing the cache regularly must not be considered a usual DBA task, and I must stress that the downside is that ALL plans in the selected cache will be cleared, not only the single use plans. This may mean higher compilation rate and CPU usage due to uncached incoming queries.
Kimberly Tripp ( Blog | Twitter ) has a nice post on this topic here .
Download code here: ClearSingleUse.sql
Until next time!
Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.