Does sp_executesql WITH RECOMPILE Actually Recompile Query Plans In SQL Server?

No, No It Doesn’t

---

But it’s fun to prove this stuff out.

Let’s take this index, and these queries.

CREATE INDEX ix_fraud ON dbo.Votes ( CreationDate );

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= '20101230';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= '20101231';

What a difference a day makes to a query plan!

Hard To Digest

---

Let’s paramaterize that!

DECLARE @creation_date DATETIME = '20101231';
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date;
'

EXEC sys.sp_executesql @sql, 
                       N'@i_creation_date DATETIME', 
                       @i_creation_date = @creation_date;

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:

DECLARE @creation_date DATETIME = '20101230';
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date;
'

EXEC sys.sp_executesql @sql, 
                       N'@i_creation_date DATETIME', 
                       @i_creation_date = @creation_date
                       WITH RECOMPILE;

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:

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date
OPTION(RECOMPILE);
'

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!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.