Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues

OPTION (RECOMPILE) when used in the correct manner can give a performance boost though Constant Folding and also help deal with parameter sniffing issues. However, before going into OPTION (RECOMPILE) and how Constant Folding works first a bit of background.

Plan Caching Issues

The first time a stored procedure is run it will compile into the plan cache. This allows the SQL engine to reuse the plan in the cache multiple times without needing to spend time and CPU cycles on recompilation. This works great most of the time, however with stored procedures that may deal with different amounts of data depending on the parameters passed in, then storing the plan in the cache can be problematic. This is because the plan stored will be the plan that was generated with the parameter values on first run. This information may be seen in the properties of the execution plan. In this example the plan was first run with the parameter set to 'A Value' so this plan was put into the plan cache, and then subsequently run with the parameter set to 'A Different Value'.

The first parameter used to compile the plan into the plan cache may not be the best parameter to compile the plan with. Other parameters that might be passed in in subsequent runs may not run well with the compiled plan. This is known as a parameter sniffing issue.

For example, if a company has one very large customer and several smaller customers they will likely have a database table with a large number of records for that one customer and a much smaller set for the other customers.

If the databased were to query based on a small customer it would likely do a LOOP JOIN and the plan would be cached meaning any subsequent runs of the plan will also perform a LOOP JOIN. Although a LOOP JOIN is performant on smaller data sets it is not performant for larger data sets. In this scenario any searches on the larger customer would then be non performant (as the query should be using a MERGE JOIN or HASH JOIN)

To demonstrate here is a demo set of tables with some random data and a parametrized stored procedure for querying.

Within the random data 100 records have been added with the marker 'Marker' so there are records that can be queried for demo purposes to return a small dataset. This is equal to having a database table with poor uniformity of data.

IF EXISTS (SELECT * FROM sysObjects WHERE Name = 'tabdemo1')
DROP TABLE tabdemo1
IF EXISTS (SELECT * FROM sysObjects WHERE Name = 'tabdemo2')
DROP TABLE tabdemo2
IF EXISTS (SELECT * FROM sysObjects WHERE Name = 'sptabdemoproc')
DROP PROCEDURE sptabdemoproc
GO
CREATE TABLE tabdemo1 (ID INT PRIMARY KEY NOT NULL IDENTITY (1,1), String Varchar(150), ID2 INT)
CREATE TABLE tabdemo2 (ID INT PRIMARY KEY NOT NULL IDENTITY (1,1), String Varchar(150), ID2 INT)
GO
SET NOCOUNT ON
GO
INSERT INTO tabdemo1 (String, ID2) VALUES (NEWID(), RIGHT(CHECKSUM(NEWID()),3))
INSERT INTO tabdemo2 (String, ID2) VALUES (NEWID(), RIGHT(CHECKSUM(NEWID()),3))
GO 50000
INSERT INTO tabdemo2 (String, ID2) VALUES ('Marker', RIGHT(CHECKSUM(NEWID()),3))
GO 100
SET NOCOUNT OFF
GO

CREATE NONCLUSTERED INDEX [IXtabdemo1ID2] ON tabdemo1(ID2)
CREATE NONCLUSTERED INDEX [IXtabdemo2ID2] ON tabdemo2(ID2)
CREATE NONCLUSTERED INDEX [IXtabdemo2String] ON [dbo].[tabdemo2] ([String])

GO

CREATE PROCEDURE sptabdemoproc (@string varchar(150)) AS

SELECT  COUNT(*)
FROM    tabdemo1 AS A
        INNER JOIN tabdemo2 AS B ON A.ID = B.ID2
WHERE   (B.String = @string OR @string IS NULL)

GO

To demonstrate parameter sniffing issues first run the stored procedure with the parameter set to 'Marker'. This will define a query pattern stored in the cache that is expecting a small amount of data to be processed.

EXEC sptabdemoproc 'Marker'  

As the predicate is set to use ‘Marker’ as the filter the optimizer has rightly decided to use a LOOP JOIN as this is the most performant way to return the data. However now this plan is cached calling the stored procedure with 'Null' (returning a count all the records) the cached plan will still perform a LOOP JOIN. Looking at the time it takes to execute by using 'SET STATISTICS TIME ON' the plan takes around 50 milliseconds to run. To get to a plan that would work better with 'Null' as the parameter the procedure will need to be recompiled. Recompiling (using EXEC sp\_Recompile sptabdemoproc) and running again using 'Null' will give an optimal plan again bringing the run time from around 50 milliseconds to around 13 milliseconds (The plan is now performing a HASH MATCH for the aggregation which is more optimal for larger data sets.)  Recompiling the stored procedure again and running for a single value the execution (once compiled) will take around 6 milliseconds

 EXEC sp_Recompile sptabdemoproc 
SET STATISTICS TIME ON
EXEC sptabdemoproc 'Marker'

From this it can be seen that one plan works best when the parameter is set to 'Null' and one works best when the parameter is set to 'Marker'. Under normal conditions both can not be in a compiled state and held in the cache. To resolve this issues of different plans needed for different parameters there are some solutions.

OPTIMIZE FOR

The OPTIMIZE FOR option may be used on a statement to tell it to compile with a specific variable.

ALTER PROCEDURE sptabdemoproc (@string varchar(150)) AS

SELECT  COUNT(*)
FROM    tabdemo1 AS A
        INNER JOIN tabdemo2 AS B ON A.ID = B.ID2
WHERE   (B.String = @string OR @string IS NULL)
OPTION  (OPTIMIZE FOR (@string = 'Marker')) 

GO

This will help if the plan should always favor a particular parameter such as 'Marker', but in that case this will always run slow when passing in 'Null'.

An UNKNOWN value may also be supplied for the OPTION

ALTER PROCEDURE sptabdemoproc (@string varchar(150)) AS

SELECT  COUNT(*)
FROM    tabdemo1 AS A
        INNER JOIN tabdemo2 AS B ON A.ID = B.ID2
WHERE   (B.String = @string OR @string IS NULL)
OPTION  (OPTIMIZE FOR UNKNOWN) 

GO

This will give a plan that will work OK with any parameter but not necessarily the most performant plan

WITH RECOMPILE

This is a procedure level option to set the procedure to recompile after each run. This means that the plan will always recompile and so avoid cached plans being reused. Using this method will take up CPU cycles though compilation but a better plan will likely be generated if there are parameter sniffing issues.

ALTER PROCEDURE sptabdemoproc (@string varchar(150)) WITH RECOMPILE AS 

SELECT  COUNT(*)
FROM    tabdemo1 AS A
        INNER JOIN tabdemo2 AS B ON A.ID = B.ID2
WHERE   (B.String = @string OR @string IS NULL)

GO

Looking at the time statistics with SET STATISTISCS TIME ON  and using the 'Marker' parameter this procedure will take around 2 to 3 milliseconds longer for the recompile, this equates to a total of around 15 milliseconds.

Looking at how the plan is forming it is still talking into consideration the fact that several parameters may be used. This can be seen by looking at the predicate in the execution plan. The predicate is still considering the two execution modes, either a value being passed in or a 'Null' value. While a good plan will be chosen the influence of considering these two execution modes in the plan will have an impact.

OPTION (RECOMPILE)

OPTION (RECOMPILE) is a statement level command that has some very distinct advantages over WITH RECOMPILE. It does not require the whole stored procedure to be recompiled so in a large stored procedure time and CPU cycles are not spent on unnecessary compiles. It uses Constant Folding which can have generate a far superior plan.

ALTER PROCEDURE sptabdemoproc (@string varchar(150)) AS

SELECT  COUNT(*)
FROM    tabdemo1 AS A
        INNER JOIN tabdemo2 AS B ON A.ID = B.ID2
WHERE   (B.String = @string OR @string IS NULL)
OPTION  (RECOMPILE)

GO

Constant folding is a technique the optimizer uses to remove any unnecessary code to help improve performance. Constant Folding does this by removing unnecessary variables and simplifying the query before compiling the plan. In the example in the demo when the parameter comes into the plan it is either set to a string or a 'Null'. Constant Folding will remove whichever is not used. So if 'Marker' is passed in as the parameter then Constant folding will simply remove the ‘OR String IS Null’ from the predicate:

Using OPTION (RECOMPILE) in this case gives by far the best execution. The plan compile time is reduced to around 1 millisecond and execution time is reduced to around 2 milliseconds, making the total execution time of the stored procedure around 3 milliseconds. This is achieved because without the need to think about the ‘IS NULL’ a plan is produced that no longer needs to scan the index but can seek, which is a much more performant operation.

Running the stored procedure with 'Null' also benefits taking the total plan time down from around 13 milliseconds to around 7 milliseconds.

Here are the two plans for comparison, before OPTION (RECOMPILE) and after OPTION (RECOMPILE) was set

Before OPTION (RECOMPILE)

After OPTION (RECOMPILE)

In the OPTION (RECOMPILE) plan the optimizer has Constant Folded out the predicate (as the ‘B.String = @String’ is unnecessary). Due to this it is able to calculate the COUNT aggregation in stream, (rather than making a hash table of the aggregates) making this a much more performant plan.

OPTION (RECOMPILE) is not a silver bullet for performance. It can be an extremely powerful performance optimizing technique but it will not always give a faster plan in all cases due to the compile overhead. If there are no parameters then there will be no benefits from Constant Folding and there will be no parameter sniffing issues so it will just add a compilation overhead.

OPTION (RECOMPILE) with temporary tables

One important side effect when using OPTION (RECOMPILE) is the impact it will have on temporary tables. If a temporary table is created with an OPTION (RECOMPILE) statement then the optimizer will need to recompile every statement it touches.

The following query takes  around 50 milliseconds and has  no SQL Server parse and compile time after first run (as the plan is cached)

CREATE PROCEDURE sptabdemoprocB AS

SELECT  A.*
INTO    #TMP
FROM    tabdemo1 AS A
        INNER JOIN tabdemo2 AS B ON A.ID = B.ID2

SELECT COUNT(*) FROM #TMP AS A
INNER JOIN tabdemo2 AS t ON A.ID = t.ID

SELECT MIN(A.ID) FROM #TMP AS A
INNER JOIN tabdemo2 AS t ON A.ID = t.ID

SELECT MAX(A.ID) FROM #TMP AS A
INNER JOIN tabdemo2 AS t ON A.ID = t.ID

GO

Adding an OPTION (RECOMPILE) to the procedure adds an overhead of 15 milliseconds compile time as everything that touches the temporary table has to be recompile. Importantly it will not implement Constant Folding on any statements that are recompiled without an explicit OPTION (RECOMPILE) statement.

When using OPTION (RECOMPILE) on a temporary table it is best to make sure it is also added on all the other statements that are using it in the same execution to benefit from Constant Folding, as follows:

CREATE PROCEDURE sptabdemoprocB AS

SELECT  A.*
INTO    #TMP
FROM    tabdemo1 AS A
        INNER JOIN tabdemo2 AS B ON A.ID = B.ID2
OPTION  (RECOMPILE)

SELECT COUNT(*) FROM #TMP AS A
INNER JOIN tabdemo2 AS t ON A.ID = t.ID
OPTION  (RECOMPILE)

SELECT MIN(A.ID) FROM #TMP AS A
INNER JOIN tabdemo2 AS t ON A.ID = t.ID
OPTION  (RECOMPILE)

SELECT MAX(A.ID) FROM #TMP AS A
INNER JOIN tabdemo2 AS t ON A.ID = t.ID
OPTION  (RECOMPILE)

GO

To sum up OPTION (RECOMPILE) is a tool that can give some massive performance benefits in parameterized stored procedures though Constant Folding and is also able to combat parameter sniffing. For very small and simple OLTP style stored procedures it will add a recompile burden.## Comments

• Anonymous
  August 10, 2016
  Good article.Doesn't WITH Recompile in a proc do constant folding?
  • Anonymous
    August 10, 2016
    Hi MadAdminThank you for your comment. WITH RECOMPILE at the procedure level unfortunately does not do constant folding with passed in parameters. It will do standard constant folding (as will non-recompiled procedures). This is detailed at https://technet.microsoft.com/en-us/library/ms175933(v=sql.105).aspxThanks
• Anonymous
  November 28, 2016
  Could these techniques be applied to an environment that doesn't use stored procedures? Our shop is .NET, C# using an ORM to generate sql. Many of the most problematic queries contain numerous parameters and very similar queries may be run on small customers, then large.
  • Anonymous
    November 28, 2016
    The comment has been removed