Query Recompilation - SQL Server Query Performance Tuning - page 314

SELECT wo.WorkOrderID,
wo.ProductID,
wo.StockedQty
FROM Production.WorkOrder AS wo
WHERE wo.StockedQty BETWEEN 500 AND 700;
With the current indexes, the execution plan for the SELECT statement, which is part of the stored procedure plan,
scans the index PK_WorkOrder_WorkOrderlD , as shown in Figure 17-1 .
Figure 17-1. Execution plan for the stored procedure
This plan is saved in the procedure cache so that it can be reused when the stored procedure is reexecuted. But if
a new index is added on the table as follows, then the existing plan won't be the most efficient processing strategy to
execute the query.
In this case, it is beneficial to spend extra CPU cycles to recompile the stored procedure so that you generate a
better execution plan.
Since index IX_Test can serve as a covering index for the SELECT statement, the cost of a bookmark lookup can be
avoided by using index IX_Test instead of scanning PK_WorkOrder_ WorkOrderID . SQL Server automatically detects
this change and recompiles the existing plan to consider the benefit of using the new index. This results in a new
execution plan for the stored procedure (when executed), as shown in Figure 17-2 .
CREATE INDEX IX_Test ON Production.WorkOrder(StockedQty,ProductID);
Figure 17-2. New execution plan for the stored procedure
SQL Server automatically detects the conditions that require a recompilation of the existing plan. SQL Server
follows certain rules in determining when the existing plan needs to be recompiled. If a specific implementation of
a query falls within the rules of recompilation (execution plan aged out, SET options changed, and so on), then the
statement will be recompiled every time it meets the requirements for a recompile, and SQL Server may, or may
not, generate a better execution plan. To see this in action, you'll need a different stored procedure. The following
procedure returns all the rows from the WorkOrder table:
IF (SELECT OBJECT_ID('dbo.WorkOrderAll')
) IS NOT NULL
DROP PROCEDURE dbo.WorkOrderAll;
GO
CREATE PROCEDURE dbo.WorkOrderAll
AS
SELECT *
FROM Production.WorkOrder AS wo;