Database Reference
In-Depth Information
Figure 17-16.
Extended Events output showing effect of the ANSI
SET
options on stored procedure recompilation
You can see that there were fewer recompilations when compared to the original
set.sql
code (Figure
17-11
).
Out of the
SET
options listed previously, the
ANSI_NULLS
and
QUOTED_ IDENTIFIER
options are saved as part of the
stored procedure when it is created. Therefore, setting these options in the connection outside the stored procedure
won't affect any recompilation issues; only re-creating the stored procedure can change these settings.
Using OPTIMIZE FOR Query Hint
Although you may not always be able to reduce or eliminate recompiles, using the
OPTIMIZE FOR
query hint can
help you get the plan you want when the recompile does occur. The
OPTIMIZE FOR
query hint uses parameter values
supplied by you to compile the plan, regardless of the values of the parameter passed in by the calling application.
For an example, examine
CustomerList
from earlier in the chapter. You know that if this procedure receives
certain values, it will need to create a new plan. Knowing your data, you also know two more important facts: The
frequency that this query will return small data sets is exceedingly small, and when this query uses the wrong plan,
performance suffers. Rather than recompiling it over and over again, modify it so that it creates the plan that works
best most of the time.
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
