Conor vs. Recompiles

I have a question for you guys about how do you force a recompile for a single query today?

There are a lots of different ways that one could cause the system to recompile a query:

a) sp_recompile <object used in a query>

b)DBCC FREEPROCCACHE (with optional arguments to restrict the set of impacted queries)

c) detach/attach db (2008+), restart server, etc.

d) use a lot of memory to force plans out of memory

e) update statistics

f) change the text of the query (add a space), which works mostly except for trivial plans (which you may not care about recompiling anyways)

I am curious what other ways people use (or what you use most frequently).  If you have any interesting scenarios about how/why you recompile a plan, I’d like to hear about them, so please send them my way (conorc @ microsoft.com)

If you never have occasion to recompile queries or think it’s not useful, that’s fine too – I want to hear about those cases as well.

If there are some interesting cases, I’ll post them up anonymously if you don’t mind.

Thanks,

Conor