Database Reference
In-Depth Information
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID = 1));
GO
When this query is executed the first time or is recompiled for any reason, it always gets the same execution plan.
To test this, execute the procedure this way:
EXEC dbo.CustomerList
@CustomerID = 7920
WITH RECOMPILE;
EXEC dbo.CustomerList
@CustomerID = 30118
WITH RECOMPILE;
Just as earlier in the chapter, this will force the procedure to be recompiled each time it is executed. Figure
17-17
shows the resulting execution plans.
Figure 17-17.
WITH RECOMPILE
doesn't change identical execution plans
Unlike earlier in the chapter, recompiling the procedure now doesn't result in a new execution plan. Instead, the
same plan is generated, regardless of input, because the query optimizer has received instructions to use the value
supplied,
@Customerld = 1
, when optimizing the query.
This can reduce the number of recompiles, and it does help you control the execution plan generated. It requires
that you know your data very well. If your data changes over time, you may need to reexamine areas where the
OPTIMIZE FOR
query hint was used.
To see the hint in the execution plan, just look at the
SELECT
operator properties, as shown in Figure
17-18
.
