#WITH OPTION RECOMPILE CODE#

This query has pulled out the exact same query plan as the previous query. When you look at the plan it looks the same as the previous plan, and we can check that it actually is by comparing the QueryPlanHash. We now get the following parameter information coming back: When we run the stored procedure a second time, but for a different state code: EXEC GetCountyByState = 'CO' Let’s try it: EXEC GetCountyByState = 'AL'Īnd then we can see in the plan properties, that the compiled value and the runtime value of the parameter are the same: The query plan is based off the parameter that I pass in the first time. WHERE County.State = I call this procedure for the first time, a query plan is generated and saved for next time I called this procedure.

#WITH OPTION RECOMPILE CODE#

I have created the below stored procedure to take a 2 letter State Code and select all the counties from it: CREATE OR ALTER PROCEDURE GetCountyByState CHAR(2)) I have a database that has a table of Counties and another table of States of America.

We could try indexing for these plans, we could try rewriting them in different ways, or we could just decide that we’re better off taking a hit every time it runs to compile a new query plan based on the current parameters.Īdding OPTION RECOMPILE to a SQL Statement will provide me with a new plan for the parameters passed in. The ideal query plan for each could be vastly different. You might have a batch process that runs every hour and sometimes it has only a few hundred rows, other times it has a few billion rows. When doing query tuning, sometimes the answer can be that it’s just better to get SQL Server to recompile a plan based on the data passed in.

With option recompile