A way to tell SQL Server to cache only certain types of adhoc queries
Is there a way to tell SQL Server not to cache certain types of plans? For example, if you have a large number of user requests coming from SQL Server Management Studio, is there a way to tell them that they are not cached in the plan cache, thereby consuming memory and other resources? Are there other behavioral tweaks you can make to prevent ad-hoc queries from being used on the production server?
I don't know of any ... but in general I think that in most high performance situations when you are concerned about this level of detail, you probably want to create a production database that is protected from user queries in the first place. In production, this is not a small chunk of memory for these query plans that might harm you, so users submit queries that throw massive locks and stop your work. As a major architectural issue, you can consider either:
- getting a replicated stand-alone server that users can push their ad-hoc requests to; or
- limiting user interaction with the database with a set of specified stored procedures
If you really can't do any of these, and custom query plans really let you down, you must be in a pretty unique situation. If you want to edit a question with more details, you can get better suggestions.
(Sorry I didn't answer the question you asked, hopefully someone more knowledgeable than me will do it.)
source to share
You can influence how SQL Server handles adhoc queries and plan caching by setting the "optimize for adhoc workloads" setting according to http://msdn.microsoft.com/en-us/library/cc645587.aspx
Basically, this means "the first time the adhoc request is called, do not cache the plan, but keep that the request was invoked. The second time the same adhoc request is called a plan, it will be cached."
We install this on one of our production servers and immediately free up 2GB of RAM, which says more about the current workload on that server than anything (and not that good)
source to share
I think I probably shouldn't need to worry about limiting this, but you can start here and the discussion linked there
source to share