Links from the video

KB 968693 - RECOMPILE bug in SQL Server 2008

KB 2965069 - RECOMPILE bug in SQL Server 2012/2014

Transcript

If you are using recompile hints in your code, the patch level or the version you’re using of SQL Server matters. In SQL Server 2008 and then later in SQL Server 2012 and 2014, there were two separate issues that caused problems where recompile hints in some rare situations could cause incorrect results.

Like anything else, the code for this is complicated and sometimes there’s bugs in SQL Server. I don’t think this is a specific bad thing about recompile as much as any feature that we choose to use could potentially have a bug impacted. And for SQL Server general health, you want to always make sure that you’re running the latest service pack available and that you’ve tested it before you deploy it to production.

Also consider the cumulative updates that are released after service packs

Microsoft recommends that you test these and implement them to your production environment as well because they contain important fixes. The testing part of that is important. You want to make sure to test it outside of production before you put it into production, but it fixes bugs for different things.

Some of which are bugs about incorrect results and we have had those on two different occasions for recompile hints. I am not saying to never recompile.

You’ve seen me use it with DMV queries where I don’t want to leave my query behind in the plan cache. I want to hide what I did, right? I also love using recompile hints for testing purposes.

If I’m testing a stored procedure, I often want to know, hey, what plan will I get for the specific parameter values that I am passing in for this procedure?

A great way to do that is to say EXEC the procedure name and you get to specify parameters too, if they exist, and then say with recompile.

I’m saying, okay, show me what you’d optimize for these parameter values if there’s a plan already in cache. Don’t reuse it, show me what you do for me and then don’t reuse my plan for anyone else. If you were using nested stored procedures though, this only applies to the outer most procedure.

So you may have to do a little more leg work if you want to test with nested stored procedures.

Okay, how does it optimize for different values? A little bit of a gotcha there.

The biggest issue I actually have with recompile hints and the biggest problem I think is that they limit that information in the Dynamic Management Views

On many versions of SQL Server, in fact, almost every version before SQL Server 2016, our main way to troubleshoot poor performance requires looking at the execution plan cache to say, okay how long our query is taking. That’s a major tool that sys.dm_exec queries stats dmv is a big deal and really helpful. And the fact that recompile hints limit what gets in there can make tuning things much harder.

I really don’t like putting recompile hints in a header or stored procedures because it just makes things not appear in those Dynamic Management Views. If recompile hints are needed to get you consistent, fast performance, and it’s being applied to stored procedures that don’t run so often that that driving up the CPU usage is a big problem, if you do need to use the recompile hint, only put it on the statements where you absolutely need it and document why you have it in there.

You know, what problem are you solving in there? What happens if you remove that recompile hint. So that way, it’s clear why you’ve done what you’ve done and why it’s worth that limited information in the Dynamic Management Views to get the performance that you need.