Database Reference
In-Depth Information
The results are the same as when the procedure was modified, but in this case, no modification was necessary.
You can see that a plan guide was applied within the execution plan by looking at the
SELECT
properties again
(Figure
17-20
).
Figure 17-20.
SELECT
operator properties show the plan guide
Various types of plan guides exist. The previous example is an
object
plan guide, which is a guide matched to a
particular object in the database, in this case
CustomerList
. You can also create plan guides for ad hoc queries that
come into your system repeatedly by creating a
SQL
plan guide that looks for particular SQL statements. Instead of a
procedure, the following query gets passed to your system and needs an
OPTIMIZE FOR
query hint:
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 >=1;
Running this query results in the execution plan you see in Figure
17-21
.
Figure 17-21.
The query uses a different execution plan from the one wanted
