When to Use Option Recompile in Sql

What is option recompile?

When a stored procedure is compiled the generated query plan is optimized for the current state of the database. This is really good if the relative sizes of the objects used in the stored procedure don't change very much from the last time the query plan was generated. The efficient query plan can easily be fetched from the cache and won't have to be generated again which is generally good for performance. But, this could be terrible for performance if the database objects changed drastically from the previous run.

By default the query plan is fetched from the cache and is only recompiled when the underlying tables change or SQL server is restarted. By adding the option recompile statement at the end of a stored procedure you are telling SQL to always recompile the query plan instead of fetching it from the cache.

When would I use this in practice?

The problem - We had a scheduled stored procedure taking a really long time in production. Usually a slow running query could be easily reproduced in testing, which would lead to an eventual fix. In this case the query was only slow sometimes in production. And it was not possible to reproduce in staging with the same data.

Experimentation - Normally in this situation when the problem could not be reproduced on prod data I would think it's most likely load related, outdated statistics, or even a maintenance script running at the wrong time. All of these problems were ruled out as we made sure statistics were up to date, the SQL server load was low, and no maintenance scripts were running yet the problem still occurred.

I had a feeling it had to do with a bad query plan but couldn't figure out why until I was able to luckily reproduce the issue in staging. I accidentally ran the procedure once with incorrect parameters and then a second time after putting in the correct parameters was able to duplicate the issue. The generated query plan differed greatly from what I saw previously in the quick runs. I had stumbled across what is known as "parameter sniffing".

The solution - Adding option recompile to the stored procedure of course! Parameter sniffing was the issue because the stored procedure was executing dynamic SQL and embedding parameter date values into the executed statement. This caused the performance go haywire if the query was run once on a set of dates that returned few records, then immediately after running on a different set of parameters that returned hundreds of thousands of records.

The query plan for the amount of data within those parameter ranges was cached as if the amount of data in those plans was always small. It was NOT always small after the query was executed again with a different set of parameters. Meaning the next time this script ran it used the bad query plan with different dates and puked. Option recompile fixed the problem because a new plan was generated before each run.