Database Reference
In-Depth Information
Avoiding Recompilations
Sometimes recompilation is beneficial, but at other times it is worth avoiding. If a new index is created on a column
referred to in the
WHERE
or
JOIN
clause of a query, it makes sense to regenerate the execution plans of stored
procedures referring to the table so they can benefit from using the index. However, if recompilation is deemed
detrimental to performance, such as when it's causing blocking or using up resources such as the CPU, you can avoid
it by following these implementation practices:
Don't interleave DDL and DML statements.
•
•
Avoid recompilation caused by statistics changes.
•
Use the
KEEPFIXED PLAN
option.
Disable the auto update statistics feature on the table.
•
•
Use table variables.
SET
options within the stored procedure.
•
Avoid changing
•
Use the
OPTIMIZE FOR
query hint.
Use plan guides.
•
Don't Interleave DDL and DML Statements
In stored procedures, DDL statements are often used to create local temporary tables and to change their schema
(including adding indexes). Doing so can affect the validity of the existing plan and can cause recompilation when the
stored procedure statements referring to the tables are executed. To understand how the use of DDL statements for
local temporary tables can cause repetitive recompilation of the stored procedure, consider the following example:
IF (SELECT OBJECT_ID('dbo.TempTable')
) IS NOT NULL
DROP PROC dbo.TempTable
GO
CREATE PROC dbo.TempTable
AS
CREATE TABLE #MyTempTable (ID INT,Dsc NVARCHAR(50))
INSERT INTO #MyTempTable
(ID,
Dsc)
SELECT pm.ProductModelID,
pm.[Name]
FROM Production.ProductModel AS pm; --Needs 1st recompilation
SELECT *
FROM #MyTempTable AS mtt;
CREATE CLUSTERED INDEX iTest ON #MyTempTable (ID);
SELECT *
FROM #MyTempTable AS mtt; --Needs 2nd recompilation
CREATE TABLE #t2 (c1 INT);
SELECT *
FROM #t2;
--Needs 3rd recompilation
GO
EXEC dbo.TempTable; --First execution
