Way to tell SQL Server to only cache certain types of adhoc queries

Question

Is there a way to tell SQL Server to not cache certain types of plans? For example, if you have a large number of users queries coming from SQL Server Management Studio, is there a way to tell these to not be cached in the plan cache, thus taking up memory and other resources? Are there other behavioral tweaks that you can do to prevent adhoc queries from taking up resources on a production server?

Answer 1

I don't know of any ... but, in general, I think that in most high-performance situations where you'd be worried about this level of detail, you'd probably want a production database that is protected from user-submitted queries in the first place. In production, it's not the little bit of memory for these query plans that will hurt you, it's users submitting queries that issue massive locks and grind your performance to a halt. As a basic architectural issue, you might consider either:

1. getting a replicated offline server that users can hit with their ad-hoc queries; or
2. restricting user interactions with the DB to a set of given stored procedures

If you really can't do either of those, and user-submitted query plans are really getting you down, you must be in a pretty unique situation. If you want to edit the question with more details, you might get better suggestions.

(Sorry I didn't answer the question you actually asked; hopefully someone more knowledgeable than me will do so.)

Answer 2

I think it's probably unnecessary to worry about restricting this, but you can start here and the discussion linked there

Answer 3

You can affect how SQL server treats adhoc queries and the caching of plans by setting the 'optimise for adhoc workloads' setting as per http://msdn.microsoft.com/en-us/library/cc645587.aspx

In essence what this does is to say 'the first time that an adhoc query is called, don't cache the plan but store that this query has been called. The second time that the same adhoc query is called the plan will e cached.

We set this on one of our production servers and freed up 2 GB of RAM straight away, which says more about the current workload against that server than anything (and not good things at that)