Option Recompile

By Greg No comments

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. 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. The ideal query plan for each could be vastly different. 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.

Adding OPTION RECOMPILE to a SQL Statement will provide me with a new plan for the parameters passed in. Let’s take a look.

I have a database that has a table of Counties and another table of States of America. 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 (@StateCode CHAR(2))
AS
BEGIN
	SELECT		*
	FROM		County
	INNER JOIN	Population
		   ON	Population.CountyId = County.Id
	WHERE		County.State = @StateCode
END

When I call this procedure for the first time, a query plan is generated and saved for next time I called this procedure. The query plan is based off the parameter that I pass in the first time. Let’s try it:

EXEC GetCountyByState @StateCode = 'AL'

And then we can see in the plan properties, that the compiled value and the runtime value of the parameter are the same:

When we run the stored procedure a second time, but for a different state code:

EXEC GetCountyByState @StateCode = 'CO'

we now get the following parameter information coming back:

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. This query has pulled out the exact same query plan as the previous query. The other thing to notice is that the Runtime Value and the Compiled Value of @StateCode are different.

So what happens when I use Option Recompile? Let’s change our Stored Procedure:

CREATE OR ALTER PROCEDURE GetCountyByState (@StateCode CHAR(2))
AS
BEGIN
	SELECT		*
	FROM		County
   INNER JOIN	Population
		   ON	Population.CountyId = County.Id
	WHERE		County.State = @StateCode
	OPTION		(RECOMPILE)
END

Rerun the stored procedure again and have a look at the plan properties:

EXEC GetCountyByState @StateCode = 'AL'

This time when we look at properties, we see something different, or more accurately, we don’t see something. The Parameters section has disappeared. Because we are going to recompile this statement, SQL Server now doesn’t bother caching the parameter. 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. Instead, SQL Server will compile that part of the query every time you run it.

In our trivial example it doesn’t matter which state is run first and cached, every State will fetch the Counties in the exact same way. But go back to the hourly batch load I was talking about at the start. SQL Server sometimes gets a few rows back, and sometimes get billions of rows back, and you want to get a different query plan depending on which one was passed in. Option Recompile might be your answer.

Leave a Reply