Database Reference
In-Depth Information
WHERE n < 1000
)
INSERT INTO dbo.Test1
(C1,
C2
)
SELECT 1,
n
FROM Nums
OPTION (MAXRECURSION 1000) ;
GO
--Reexecute the stored procedure with a change in statistics
EXEC dbo.TestProc ; --With change in data distribution
Figure
17-14
shows the Extended Events output.
Figure 17-14.
Extended Events output showing the role of the
KEEPFIXED PLAN
option in reducing recompilation
You can see that, unlike in the earlier example with changes in data, there's no
auto_stats
event (see Figure
17-8
).
Consequently, there's no additional recompilation. Therefore, by using the
KEEPFIXED PLAN
option, you can avoid
recompilation because of a statistics change.
■
this is a potentially dangerous choice. Before you consider using this option, ensure that any new plans that
would have been generated are not superior to the existing plan and that you've exhausted all other possible solutions.
in most cases, recompiling queries is preferable, though potentially costly.
Note
