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.

There are certain connections to my database server that issue tons of commands for thousands of different databases (so they can't all share a cache of the same plan), and as a result the server runs out of memory for the query plan cache, and all queries start to thrash the cache. There are large sets of commands for which I am willing to trade CPU (and performance) in order to not spoil the query plan cache for other connections which must be more responsive.
–
kamensApr 7 '10 at 15:52

These queries that I'm willing to recompile every time are always grouped by connection, which is why I'm wondering if there is a way to force a connection to not insert any query plans into the cache.
–
kamensApr 7 '10 at 15:52

@GBN - technically the recompile means it will eject it from memory after use, thus freeing up the memory - but the problem is exactly as you put it, thousands of databses and commands, and the chances are there is little plan cache reuse going on. I would definately investigate a switch to forced parameterization mode - it can often stop a plan cache from being flooded.
–
AndrewApr 7 '10 at 16:02

@Andrew: what OP would need is "use and discard immediately", rather than recompile, to stop it lingering in the cache.
–
gbnApr 7 '10 at 16:09

1

@Gbn, recompile does discard (Certainly does in 2005) - tested it to be certain (we know what the BoL can be like) and the BoL is correct in stating it as "Instructs the SQL Server 2005 Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed." Gut feeling said it would leave it in the cache invalidated, but it appears not.
–
AndrewApr 7 '10 at 16:32

I'm not aware of a connection string setting or trace flag you could use to achieve this. Turning it off for the whole server (in a very hacky fashion by constantly issue freeprocache commands) is possible, but very horrid and I can't imagine wanting to ever do it.

As GBN says, they are shared amongst all users of the system who issue the exact same text (or post-parameterized text) on the same ansi settings etc.

Not sure why it needs to be recomplied but until you get to the bottom of this consider making the proc recompile every time it is run with the following code, at least that way it won't fail every morning: