4

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: https://stackoverflow.com/questions/2596587/what-does-sql-server-trace-flag-253-do

Improve this question
1
  • 1
    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, 2012 at 14:36

3 Answers 3

Reset to default
6

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
Improve this answer
0
1

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.

Improve this answer
0

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.