ansaurus

Question

Is there an equivalent of "OPTION(RECOMPILE)" or "WITH RECOMPILE" for an entire connection?

Answer 1

+2 A:

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

gbn 2010-04-07 15:37:58

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 2010-04-07 15:52:10

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 2010-04-07 15:52:38

@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 2010-04-07 16:02:02

@Andrew: what OP would need is "use and discard immediately", rather than recompile, to stop it lingering in the cache.

gbn 2010-04-07 16:09:22

I agree with all of these suggestions, and we're well down the road of moving towards them as well as having researched them extensively. Forced parameterization and quality of queries is not the problem, because a single database gets ~98% query plan cache hit ratio. It's just the number of DB's. And I recognize that my question is not the ideal solution, but I'm trying to fully investigate. It sounds like the answer is "no".

kamens 2010-04-07 16:10:54

@kamens: unless there is a really obscure trace flag that MS support know about, I'd say "no" as you suspect. It sounds like you have researched it...

gbn 2010-04-07 16:22:57

@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 2010-04-07 16:32:32

@Andrew: interesting, but obvious now. And I really should have known that...

gbn 2010-04-07 16:34:32

@gbn Shucks...thanks, but not what I was hoping for ;)

kamens 2010-04-07 17:37:10

See my update about trace flag 253

kamens 2010-04-07 23:27:52

Answer 2

+1 A:

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.

Andrew 2010-04-07 15:45:26

Answer 3

A:

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

HLGEM 2010-04-26 15:18:14

ansaurus

tags:

views:

answers:

Is there an equivalent of "OPTION(RECOMPILE)" or "WITH RECOMPILE" for an entire connection?

related questions