The other day I was tasked with creating a pretty intensive query (hey, I’m not an expert on T-SQL yet), and I came across a stored procedure that read something to the effect of:
SELECT DISTINCT [Database].[Table].[Column]
Column = @Parameter1
OR @Parameter1 IS NULL
Column2 = @Parameter2
OR @Parameter2 IS NULL
So, the first part of the query is pretty straight forward, but what the heck is that “OPTION(RECOMIPLE)?” I said to myself. After some searching, I found the answer. Then, after looking up what the answer said, and how it is actually the answer, I understood. The explanation dove so deep into the mechanics of stored procedures in SQL Server I figured it warranted a post.
As a matter of principle, I always like writing posts from the perspective of someone who has almost no experience in the subject I’m writing about. This helps me to make sure I’m not skipping an important part of the equation, as well as helps people call me out if I’m wrong. In this case, I’ll need to explain Query Plans and how they impact your server.
Firstly, a definition: A query plan is a set of instructions that direct the SQL engine on how to execute the query, e.g., which structures to use, how to get to them, whether to sort the results, etc. This plan is stored in cache for use later. The next time you execute a query, the pipeline checks cache to see if the exact query has already been executed. If so, it will use that plan rather than building a new one.
The problem here is that caching a query will unnecessarily eat up memory in situations where your queries are dynamic, as they tend to be in stored procedures. You know that, depending on which parameters are passed in, the result set could be wildly different on each execution. Therefore, adding the line “OPTION(RECOMPILE)” to the query ensures that the query plan will not be cached on execution, and will actually receive a new plan each time.