How do I force SQL server to generate a new query plan ? Syntax help

Question

Post reply

How do I force SQL server to generate a new query plan ? Syntax help

mw_sql_developer

SSCoach

Points: 19445
More actions
October 30, 2015 at 9:46 am

#304644

Select A.* from A inner join B on ( A.ID= B.ID )
I know there is some key word that you use to force SQL server to generate a new query plan ?
What can that be ?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

RECOMPILE

select a.* from a inner join b on a.id = b.id option (recompile)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

mw112009 (10/30/2015)
Select A.* from A inner join B on ( A.ID= B.ID )
I know there is some key word that you use to force SQL server to generate a new query plan ?
What can that be ?
Thanks

There's a cost associated with it - recompiles are quite expensive. Why do you want to do this?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

In an OLTP database, you want to minimize recompiles, so you don't want to leave RECOMPILE hint in production. However, in a DW database where tables are bulk loaded, statistics shift frequently, and queries are occasional or ad-hoc, then a case could be made to always recompile. In a DW the overhead of plan compilation is trivial compared to duration of the query, and you want the best possible plan based on most recent statistics and parameters.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

Eric M Russell (10/30/2015)
In an OLTP database, you want to minimize recompiles, so you don't want to leave RECOMPILE hint in production.

I think that's an over generalized statement... "Catch All" procs (with multiple, optional parameters) for example, tend to benefit greatly from the use of the OPTION(RECOMPILE) hint.

In reality, it just depends on the query... Does the cost of recompilation out way the cost of using a sub-optimal execution plan?

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Jason A. Long (10/31/2015)
Eric M Russell (10/30/2015)
In an OLTP database, you want to minimize recompiles, so you don't want to leave RECOMPILE hint in production.
I think that's an over generalized statement... "Catch All" procs (with multiple, optional parameters) for example, tend to benefit greatly from the use of the OPTION(RECOMPILE) hint.
In reality, it just depends on the query... Does the cost of recompilation out way the cost of using a sub-optimal execution plan?

+1000 As with all else, "It Depends". A bad query plan that causes a multi-minute run is much more expensive that the combination of a RECOMPILE and good plan, which frequently ends up being sub-second. Of course, you can get the best of both worlds using Gail's "Catch All Query" method using dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

Jason A. Long (10/31/2015)
Eric M Russell (10/30/2015)
In an OLTP database, you want to minimize recompiles, so you don't want to leave RECOMPILE hint in production.
I think that's an over generalized statement... "Catch All" procs (with multiple, optional parameters) for example, tend to benefit greatly from the use of the OPTION(RECOMPILE) hint.
In reality, it just depends on the query... Does the cost of recompilation out way the cost of using a sub-optimal execution plan?

Not an over generalzation, I presented a DW scenario having a narrow case usage, and simply said the case for recompile all can be made, but maybe not applied. It depends on how much time, relative to data processing, the CPU would spend compiling. Ive seen some DW databases that would run only a handfull of query executions daily, and each query would run for more than an hour.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

JakDBA SSCommitted Points: 1610 More actions · Answer 7

Yes, Well you can use RECOMPILE or OPTIMIZE FOR / PLAN GUIDES query hints ... i never used these but below links helps to understand.

https://msdn.microsoft.com/en-us/library/ms190417.aspx

https://msdn.microsoft.com/en-us/library/hh213001.aspx

basically syntax used is

exec <stored proc> WITH RECOMPILE ;

or exec sp_recompile '<object_name>'

more to understand .... http://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/

best of luck

@JayMunnangi