Query Recompilation - SQL Server Query Performance Tuning - page 331

RECOMPILE Clause with the EXECUTE Statement
As shown previously, specific parameter values in a stored procedure may require a different plan, depending upon
the nature of the values. You can take the RECOMPILE clause out of the stored procedure and use it on a case-by-case
basis when you execute the stored procedure, as follows:
EXEC dbo.CustomerList
@CustomerId = 1
WITH RECOMPILE;
When the stored procedure is executed with the RECOMPILE clause, a new plan is generated temporarily. The
new plan isn't cached, and it doesn't affect the existing plan. When the stored procedure is executed without the
RECOMPILE clause, the plan is cached as usual. This provides some control over reusability of the existing plan cache
rather than using the RECOMPILE clause with the CREATE PROCEDURE statement.
Since the plan for the stored procedure when executed with the RECOMPILE clause is not cached, the plan is
regenerated every time the stored procedure is executed with the RECOMPILE clause. However, for better performance,
instead of using RECOMPILE , you should consider creating separate stored procedures, one for each set of parameter
values that requires a different plan, assuming they are easily identified and you're dealing only with a small number
of possible plans.
RECOMPILE Hints to Control Individual Statements
While you can use either of the previous methods to recompile an entire procedure, this can be problematic if the
procedure has multiple commands. All statements within a procedure will all be recompiled using either of the
previous methods. Compile time for queries can be the most expensive part of executing some queries, so recompiles
should be avoided. Because of this, a more granular approach is to isolate the recompile to just the statement that
needs it. This is accomplished using the RECOMPILE query hint as follows:
IF (SELECT OBJECT_ID('dbo.CustomerList')
) IS NOT NULL
DROP PROC dbo.CustomerList;
GO
CREATE PROCEDURE dbo.CustomerList @CustomerId INT
AS
SELECT soh.SalesOrderNumber,
soh.OrderDate,
sod.OrderQty,
sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerId
OPTION (RECOMPILE);
GO
This procedure will appear to behave the same way as the one where the RECOMPILE was applied to the entire
procedure, but if you added multiple statements to this query, only the statement with the OPTION (RECOMPILE) query
hint would be compiled at every execution of the procedure.