Does sp_executesql WITH RECOMPILE Actually Recompile Anything?

No, No It Doesn’t

But it’s fun to prove this stuff out.

Let’s take this index, and these queries.

What a difference a day makes to a query plan!

Curse the head

Hard To Digest

Let’s paramaterize that!

This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan.

That’s an example of how parameters are sniffed.

Sometimes, that’s not a good thing. Like, if I passed in 2008-12-30, we probably wouldn’t like a lookup too much.

One common “solution” to parameter sniffing is to tack a recompile hint somewhere.

Recently, I saw someone use it like this:

Which… gives us the same plan. That doesn’t recompile the query that sp_executesql runs.

You can only do that by adding OPTION(RECOMPILE) to the query, like this:

A Dog Is A Cat

Chalk this one up to “maybe it wasn’t parameter sniffing” in the first place.

I don’t usually advocate for jumping right to recompile, mostly because it wipes the forensic trail from the plan cache.

There are some other potential issues, like plan compilation overhead, and there have been bugs around it in the past.

Thanks for reading!

1 thought on “Does sp_executesql WITH RECOMPILE Actually Recompile Anything?”

Leave a Reply

Your email address will not be published. Required fields are marked *