Take the 2-minute tour ×
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.

I'm curious. Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration?

Can I run a command that basically says, "use OPTION(RECOMPILE) for all subsequent commands until I tell you to stop doing so?"

I'm acutely aware of all the performance trade-offs here, and I know this is not a step to be taken lightly. However, I'm in a unique situation where this behavior may be advantageous.

UPDATE: I found trace flag 253, but cannot find official mention of what exactly it does: http://stackoverflow.com/questions/2596587/what-does-sql-server-trace-flag-253-do

share|improve this question

migrated from stackoverflow.com Jan 26 '12 at 14:34

This question came from our site for professional and enthusiast programmers.

    
Why do you need to do this? I discovered by accident that the DAC doesn't seem to either cache execution plans or use cached plans. –  Martin Smith Jan 26 '12 at 14:36

3 Answers 3

up vote 2 down vote accepted

Execution plans do not last for the duration of a connection: they are shared across all connections.

It has to be specified per query because any plan cache/reuse issues affect only that plan.

What are you trying to do please, and why do you think it would help?

Edit, after comment

The plan must exist in cache to be used (and reused). When it's cached, it uses memory. Recompiling would use the same memory and use extra CPU etc to recompile.

You said "tons of commands" and "thousands of databases": this is your problem. And probably no "dbo." etc to help plan reuse

Thoughts:

  • buy bigger server + more RAM
  • migrate to 64 bit with as much RAM as possible
  • split databases onto different servers
  • forced parameterisation
  • check quality of queries
share|improve this answer
    
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. –  kamens Apr 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. –  kamens Apr 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. –  Andrew Apr 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. –  gbn Apr 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. –  Andrew Apr 7 '10 at 16:32

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:

CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE
AS
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.