Question 3

Answer 3

Transcript

Question

What are the pros and cons of using RECOMPILE in SQL Server?

Suggested Answer

I think question three is a really fun one to answer.

RECOMPILE hints can be very useful, because they tell SQL Server, I want you to compile the perfect execution plan for the parameters that have been passed into this query, given what you know about how much data we have and how it’s distributed at the time you compile. This can solve problematic patterns where we have queries, where reusing the execution plan doesn’t work well, and we get bad performance.

But there are definitely trade-offs for this. Compiling execution plans is a CPU-intensive process. So if we do this all the time on a busy system, this can drive up our CPU usage, and eventually, our licensing costs for the SQL Server, which is one of the biggest costs. Using RECOMPILE hints also limits the amount of information we have in the SQL Server cache about how execution has been going, and what plans have been used for different versions of this query.

So we may have to take extra steps to monitor this with RECOMPILE hints in place. We do have the option to place RECOMPILE hints on individual statements, and control and be really selective about where we use these. And because of those trade-offs, I wouldn’t use RECOMPILE hints all the time.

And generally, I’ll look for, if there are ways to get consistent fast performance, while still maintaining as much information as we can about query execution, and hopefully not having to compile everything all the time.