Database Reference
In-Depth Information
Figure 17-18.
The Parameter Compiled Value matches the value supplied by the query hint
You can see that while the query was recompiled and it was given a value of 30118, because of the hint, the
compiled value used was 1 as supplied by the hint.
You can specify that the query be optimized using
OPTIMIZE FOR UNKOWN
. This is almost the opposite of the
OPTIMIZE FOR
hint. The
OPTIMIZE FOR
hint will attempt to use the histogram, while the
OPTIMIZE FOR UNKNOWN
hint
will use the density vector of the statistics. What you are directing the processor to do is perform the optimization
based on the average of the statistics, always, and to ignore the actual values passed when the query is optimized.
You can use it in combination with
OPTIMIZE FOR <value>
. It will optimize for the value supplied on that parameter
but will use statistics on all other parameters. As was discussed in the preceding chapter, these are both mechanisms
for dealing with bad parameter sniffing.
Using Plan Guides
A plan guide allows you to use query hint or other optimization techniques without having to modify the query or
procedure text. This is especially useful when you have a third-party product with poorly performing procedures you
need to tune but can't modify. As part of the optimization process, if a plan guide exists when a procedure is compiled
or recompiled, it will use that guide to create the execution plan.
In the previous section, I showed you how using
OPTIMIZE FOR
would affect the execution plan created on a
procedure. The following is the query from the original procedure, with no hints:
IF (SELECT OBJECT_ID('dbo.CustomerList')
) IS NOT NULL
DROP PROC dbo.CustomerList;
GO
IF (SELECT OBJECT_ID('dbo. CustomerList')
) IS NOT NULL
DROP PROC dbo. CustomerList;
GO
CREATE PROCEDURE dbo.CustomerList @CustomerID INT
AS
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;
GO
