Database Reference
In-Depth Information
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID AND
p.Name = @ProductName
ORDER BY ProductName,Discounts ASC
OPTION (RECOMPILE)
GO
11. Then, again execute the query used in step 9 to return the compiled plans in memory,
as follows:
refcounts cacheobjtype objtype usecounts size_in_bytes
2 Compiled Plan Adhoc 1 81920
1 Compiled Plan Adhoc 1 32768
12. From the preceding result, we can see that there is no difference in the results when
the
RECOMPILE
hint is used.
We can also test the results by clearing the procedure cache from
memory by using
DBCC
FREEPROCCACHE
or
DBCC
FREESYSTEMCACHE
('ALL')
WITH
MARK_IN_USER_FOR_REMOVAL
. The
MARK_IN_
USER_FOR_REMOVAL
will remove the unused plan entries from the
cache after they become unused.
Ensure to execute these
DBCC
statements on a development platform,
not recommended for use on a production server.
This completes the required process in implementing query hints for optimized performance.
How it works...
In this recipe, we followed a different alternative of using
OPTIMIZE
FOR
UNKNOWN
and
RECOMPILE
hints. There are two types of
OPTIMIZE
FOR
hints:
OPTIMIZE
FOR
UNKNOWN
and
OPTIMIZE
FOR
UNKNOWN
. When the data is queried as many single TSQL statements and
adhoc batches, the
OPTIMIZE
FOR
UNKNOWN
option is helpful to improve the efficiency of the
plan cache for the defined workload.
Using
SP_CONFIGURE
, setting the
OPTIMIZE
FOR
UNKNOWN
to
1
will
affect the new plans, and the existing plans in cache are unaffected.
The
OPTIMIZE
FOR
UNKNOWN
hint directs the query optimizer to use the required
algorithm to use the required parameter. However, specifying
OPTIMIZE
FOR
causes the
ParameterCompiledValue
to be omitted from the showplan XML output, just as if
parameter sniffing did not happen. The resultant plan will be the same regardless of the
parameters passed, and may give more predictable query performance.
Search WWH ::
Custom Search