Query Recompilation - SQL Server Query Performance Tuning - page 329

For example, if sp_recompile is called on table Test1 , all the stored procedures and triggers that refer to table
Test1 are marked for recompilation and are recompiled the next time they are executed, like so:
sp_recompile 'Test1';
You can use sp_recompile to cancel the reuse of an existing plan when executing dynamic queries with
sp_executesql . As demonstrated in the previous chapter, you should not parameterize the variable parts of a query
whose range of values may require different processing strategies for the query. For instance, reconsidering the
corresponding example, you know that the second execution of the query reuses the plan generated for the first
execution. The example is repeated here for easy reference:
DBCC FREEPROCCACHE;
--Clear the procedure cache
GO
DECLARE @query NVARCHAR(MAX);
DECLARE @param NVARCHAR(MAX);
SET @query = N'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;'
SET @param = N'@CustomerId INT';
EXEC sp_executesql @query,@param,@CustomerId = 1;
EXEC sp_executesql @query,@param,@CustomerId = 30118;
The second execution of the query performs an Index Scan operation on the SalesOrderHeader table to
retrieve the data from the table. As explained in Chapter 8, an Index Seek operation may have been preferred on the
SalesOrderHeader table for the second execution. You can achieve this by executing the sp_recompile system stored
procedure on the SalesOrderHeader table as follows:
EXEC sp_recompile 'Sales.SalesOrderHeader'
Now, if the query with the second parameter value is reexecuted, the plan for the query will be recompiled as
marked by the preceding sp_recompile statement. This allows SQL Server to generate an optimal plan for the second
execution.
Well, there is a slight problem here: You will likely want to reexecute the first statement again. With the plan
existing in the cache, SQL Server will reuse the plan (the Index Scan operation on the SalesOrderHeader table) for the
first statement even though an Index Seek operation (using the index on the filter criterion column soh.CustomerID )
would have been optimal. One way of avoiding this problem is to create a stored procedure for the query and use the
OPTION (RECOMPILE) clause on the statement. I'll go over the various methods for controlling the recompile next.
Explicit Use of RECOMPILE
SQL Server allows stored procedures and queries to be explicitly recompiled using the RECOMPILE command in three
ways: with the CREATE PROCEDURE statement, as part of the EXECUTE statement, and in a query hint. These methods
decrease the effectiveness of plan reusability, so you should consider them only under the specific circumstances
explained in the following sections.