CS #1 Using SQL Hint OPTION RECOMPILE for improving performance

Problem Statement 

Stored procedure CS1_RECOMPILE is running slow for some input parameters combinations and running fine for other input parameters.

     @start_prod_id INT,
        @end_prod_id   INT
    SELECT TOP 10 ProductId
    FROM bigProduct
    ORDER BY ListPrice DESC;    

    SELECT TOP 10 *
    FROM bigTransactionHistory
    WHERE ProductID BETWEEN @start_prod_id AND @end_prod_id
    ORDER BY ActualCost DESC;

Step 1: Identifying Bottleneck

Tools used to identify bottleneck: SSMS Execution Plan

Lets execute this procedure with different parameters and observe the execution plan.

--This call takes less than second to complete
EXEC CS1_RECOMPILE @start_prod_id = 1346, @end_prod_id = 1355
--While this call takes 28 secs to complete
EXEC CS1_RECOMPILE @start_prod_id = 5451, @end_prod_id = 43387



This is a common situation, which often occurs when the supplied parameters vary and for one particular parameter combination performance is acceptable but for another combination of parameters performance is not acceptable.

Also there is another similar case where in single procedure is created to satisfy all the possible parameter combinations(some of which might not be supplied at all). In this case, the WHERE clause looks something like the following:

WHERE ColumnX = ISNULL(@variableX, ColumnX)
AND ColumnY = ISNULL(@variableY, ColumnY)
AND ColumnZ = ISNULL(@variableZ, ColumnZ)

Step 2: Troubleshooting performance (Using Recompile Hint)

If we know that a statement or stored procedure returns a varying amount of data (based on the parameters supplied) then we can use the SQL Server feature OPTION (RECOMPILE) to tell SQL Server that the statement being executed should have it’s own plan created every time it executes and that prior plans (if they exist) should not be reused to execute the statement.

It also tells SQL Server that this particular plan is a “single-use plan” that should not be reused for subsequent query execution and hence this plan is not stored in cache for reuse.

     @start_prod_id INT,
        @end_prod_id   INT
    SELECT TOP 10 ProductId
    FROM bigProduct
    ORDER BY ListPrice DESC;  
    SELECT TOP 10 *
    FROM bigTransactionHistory
    WHERE ProductID BETWEEN @start_prod_id AND @end_prod_id
    ORDER BY ActualCost DESC;



As a general practice one should not try to use recompilation as solution for every problem. We should only use this if  we can’t come up with a better solution. For example, the optimal execution plan for some SQL queries can vary between table scans and nonclustered index with (bookmark) lookups because of the volatility of the parameters, combined with the fact that a better index doesn’t exist. Sometimes we can create an index (usually a covering index) and make the plan more stable or consistent without requiring recompilation. See this Case Study for more details.