Should you ever use BOTH of the OPTIONS "Recompile" and "Optimize For" on a Single Query?

user_3402995 0
Viewed 49

I have inherited about 20 stored procedures that have every query ending in:

OPTION(RECOMPILE, OPTIMIZE FOR(@big_id = 999))

To my understanding of these options, it is telling the server to recompile this statement's execution plan every time, and telling it to use that single number to do its optimization every time as well.

So isn't it simply recompiling the EXACT SAME PLAN every time because of the "Optimize For" option?

Or am I missing some "secret sauce" in the Constant Folding that is doing something magic?

(To me it looks like it is wasting cycles with constant query recompiles)

sql-server
1 Answers

Other things such as statistics could have changed since the plan was generated, regardless of which value it was optimized for.

Saying that - its unlikely its actually worth recompiling each time. Sounds like someone managed to speed a query up once with that by coincidence and has applied it blindly to all the rest

user_21021320
Related