Tutorial :Is there an equivalent of “OPTION(RECOMPILE)” or “WITH RECOMPILE” for an entire connection?



Question:

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


Solution:1

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


Solution:2

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.


Solution:3

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »