Database Reference
In-Depth Information
Avoiding Recompilations Caused by Statistics Change
In the “Analyzing Causes of Recompilation” section, you saw that a change in statistics is one of the causes of
recompilation. On a simple table with uniform data distribution, recompilation because of a change of statistics may
generate a plan identical to the previous plan. In such situations, recompilation can be unnecessary and should be
avoided if it is too costly. But, most of the time, changes in statistics need to be reflected in the execution plan. I'm just
talking about situations where you have a long recompile time or excessive recompiles hitting your CPU.
You have two techniques to avoid recompilations caused by statistics change.
KEEPFIXED PLAN option.
Use the
Disable the auto update statistics feature on the table.
Using the KEEPFIXED PLAN Option
SQL Server provides a KEEPFIXED PLAN option to avoid recompilations because of a statistics change. To understand
how you can use KEEPFIXED PLAN , consider statschanges.sql with an appropriate modification to use the KEEPFIXED
PLAN option.
--Create a small table with one row and an index
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1 ;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 CHAR(50)) ;
INSERT INTO dbo.Test1
VALUES (1, '2') ;
CREATE NONCLUSTERED INDEX IndexOne ON dbo.Test1 (C1) ;
--Create a stored procedure referencing the previous table
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc ;
GO
CREATE PROC dbo.TestProc
AS
SELECT *
FROM dbo.Test1 AS t
WHERE t.C1 = 1
OPTION (KEEPFIXED PLAN) ;
GO
--First execution of stored procedure with 1 row in the table
EXEC dbo.TestProc ;
--First execution
--Add many rows to the table to cause statistics change
WITH Nums
AS (SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Nums
 
Search WWH ::




Custom Search