Transcript

There’s one hint in SQL Server that is more popular than all of the other hints, probably all of the other hints combined. That hint is the recompile hint. You can come across tons of articles on the internet suggesting that you use the recompile hint to fix all sorts of problems in your query.

You can use the recompile hint in a couple different ways

You can put it in the header of a stored procedure. You can also use it as a hint on a specific T-SQL statement. This statement might be part of a stored procedure. It might be a parameterized query. It could be all sorts of things.

So, it’s very flexible and you can use these in lots of places.

When you use a recompile hint, you’re saying…

If there’s already an execution plan in the cache for this query, don’t use it. Give me a nice fresh plan.

Figure out a crafted plan for me and make it special.

And also, don’t reuse my plan for anyone else who comes along afterwards.

This plan is really for me.

Now, that’s part of why you’ll find this as a suggestion for lots of ills around SQL Server because it gives you this crafted, specific plan just for the query that you’re running and for the specific values that you’re passing in to any parameters in that query.

But, there’s tradeoffs

Compiling execution plans requires a lot of CPU time on the SQL Server. And the more complex your query, the more the optimizer may have to think about all the different ways that it could possibly run this and make sure that it’s choosing the best way to optimize it. So if you use recompile hints in a lot of your code, there are times where you’ll see CPU usage rising.

And, it’s kind of a gradual creep depending on how frequent the queries that you’ve run execute. How often is it actually compiling a new plan for them? But, you can really drive up CPU usage if you do this a lot over time.

We pay for SQL Server licensing by the core so it is literally expensive to drive up that CPU usage and it can cost you real licensing dollars in the long run.

I like to think of a recompile hint as almost like teaching your dog to bark. You give it the command to bark and it’s really cute when it happens the first time. But if you start giving it really frequent commands to bark, suddenly you are deafened in your living and just this environment where there’s just barking overwhelming everything and it is hard to get anything done.

Let’s take a look at a demo of recompile hints.