Database Reference
In-Depth Information
To get a query plan guide, you first need to know the precise format used by the query in case parameterization,
forced or simple, changes the text of the query. The text has to be precise. If your first attempt at a query plan guide
looked like this:
EXECUTE sp_create_plan_guide
@name = N'MyBadSQLGuide',
@stmt = 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',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@CustomerID int',
@hints = N'OPTION (TABLE HINT(soh, FORCESEEK))';
then you'll still get the same execution plan when running the select query. This is because the query doesn't look
like what was typed in for the plan guide. Several things are different, such as the spacing and the case on the
JOIN
statement. You can drop this bad plan guide using the T-SQL statement.
EXECUTE sp_control_plan_guide
@operation = 'Drop',
@name = N'MyBadSQLGuide';
Inputting the correct syntax will create a new plan.
EXECUTE sp_create_plan_guide
@name = N'MyGoodSQLGuide',
@stmt = 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 >=1;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT(soh, FORCESEEK))';
Now when the query is run, a completely different plan is created, as shown in Figure
17-22
.
