Stored procedure CS1_RECOMPILE is running slow for some input parameters combinations and running fine for other input parameters.
CREATE PROC CS1_RECOMPILE @start_prod_id INT, @end_prod_id INT AS BEGIN 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; END; GO
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.
CREATE PROC CS1_RECOMPILE @start_prod_id INT, @end_prod_id INT AS BEGIN 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; OPTION (RECOMPILE) END; GO
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.