Query Recompilation - SQL Server Query Performance Tuning - page 336

Disable Auto Update Statistics on the Table
You can also avoid recompilation because of a statistics update by disabling the automatic statistics update on the
relevant table. For example, you can disable the auto update statistics feature on table Test1 as follows:
EXEC sp_autostats
'dbo.Test1',
'OFF' ;
If you disable this feature on the table before inserting the large number of rows that causes statistics change, you
can avoid the recompilation because of a statistics change.
However, be cautious with this technique, since outdated statistics can adversely affect the effectiveness of the
cost-based optimizer, as discussed in Chapter 12. Also, as explained in Chapter 12, if you disable the automatic update
of statistics, you should have a SQL job to manually update the statistics regularly.
Using Table Variables
One of the variable types supported by SQL Server 2014 is the table variable. You can create the table variable data
type like other data types by using the DECLARE statement. It behaves like a local variable, and you can use it inside a
stored procedure to hold intermediate result sets, as you do using a temporary table.
You can avoid the recompilations caused by a temporary table if you use a table variable. Since statistics are not
created for table variables, the different recompilation issues associated with temporary tables are not applicable
to it. For instance, consider the script used in the section “Identifying the Statement Causing Recompilation.” It is
repeated here for your reference.
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc ;
GO
CREATE PROC dbo.TestProc
AS
CREATE TABLE #TempTable (C1 INT) ;
INSERT INTO #TempTable
(C1)
VALUES (42) ;
-- data change causes recompile
GO
EXEC dbo.TestProc ; --First execution
Because of deferred object resolution, the stored procedure is recompiled during the first execution. You can
avoid this recompilation caused by the temporary table by using the table variable as follows:
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc;
GO
CREATE PROC dbo.TestProc
AS
DECLARE @TempTable TABLE (C1 INT);