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.

What is the Difference Between Currying and Partial Application?

Currying is the act of breaking apart functions with multiple parameters into multiple one parameter functions.

Here is a simple function with multiple parameters that is not curried.

// multiple parameter function
let add x y = x + y 

Here is the same function but curried.

// curried function
let add x = 
  let addInner y = x + y 
  addInner

As you can see, the curried function is made up of another function with the first parameter baked in. Because this function is curried it can now be partially applied (you actually get this automatically with any multi parameter function in F#). Partial application is a powerful technique in functional programming if used correctly. For instance, here is a simple partial application example for the function above.

// partially applied functions
let add4 = add 4 
let add5 = add 5 

add4 1 // returns 5
add5 1 // returns 6

The add4 function partially applies the add function, which means it returns the addInner function with 'x' baked in. So the add4 function becomes:

addInner y = 4 + y

This example is trivial, how could this be used in a useful way in production code? Often in our production code we have to retrieve something from the database, like a customer, and then continue with the business operation

Business logic function:

let businessLogic (dbFun:int->Customer) customerId = 
  let customer = dbFun customerId
  // other logic

This business logic function takes as a parameter a function that retrieves a customer from the database. But the database function below needs a connection string as well (passing connection string info in makes managing connection strings easier).

Database function:

let getCustomerFromDb connectionString customerId = 
  // get customer from real database

This function has the function signature of string->int->Customer. We use partial application to turn it into an int->int so it can be used in the businessLogic function.

let customerId = 5

// partially applied get customer function
let entryPoint = businessLogic (getCustomerFromDb "RealConnection") customerId 

In a non functional language it is necessary to use dependency injection to achieve this same thing. Partial application achieves the same goal but is simpler and doesn't require extra setup code.