OPTION RECOMPILE SQL CODE

Instead, SQL Server will compile that part of the query every time you run it. We can run this query for whatever input we like and we’ll still get the same QueryHash and QueryPlanHash, but SQL won’t store parameters for it. Because we are going to recompile this statement, SQL Server now doesn’t bother caching the parameter. This time when we look at properties, we see something different, or more accurately, we don’t see something.

Rerun the stored procedure again and have a look at the plan properties: EXEC GetCountyByState = 'AL' So what happens when I use Option Recompile? Let’s change our Stored Procedure: CREATE OR ALTER PROCEDURE GetCountyByState CHAR(2)) The other thing to notice is that the Runtime Value and the Compiled Value of are different. 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.

OPTION RECOMPILE SQL 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.

Option recompile sql