Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Improve SQL Server Table Variable Performance Using Trace Flag 2453


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

How can we improve the performance of queries that use SQL Server table variables?  What options are available?

Solution

When a SQL Server table variable is defined and used within your query, the related execution plan will be generated and optimized for its empty state. At runtime, the table will be filled with rows. Once that table variable is joined with other tables in the query, the initial execution plan will not be the most proper and optimal plan for its execution and it should be recompiled to generate the optimal plan for the number of rows.  As the row numbers change, the query will not recompile automatically with a table variable.

To overcome this issue that may slow down the overall query performance, Trace Flag 2453, was introduced in SQL Server 2012 SP2 and later SQL versions.  This trace flag can be used to allow triggering the table variable recompilation when the number of rows have changed in that table.

Assume that we need to create a table variable and fill it with data from the Production.TransactionHistory table for further processing, such as joining it with the source table to retrieve a specific data range as in the query below:

USE MSSQLTipsDemo
GO
DECLARE @VarTable TABLE (TransactionID INT PRIMARY KEY CLUSTERED, ProductID INT,TransactionType nchar(1));
INSERT INTO @VarTable (TransactionID, ProductID,TransactionType)
SELECT TransactionID, ProductID,TransactionType
FROM Production.TransactionHistory;

SELECT * FROM @VarTable Vart
JOIN Production.TransactionHistory TrnHist ON @VarTable.TransactionID = TrnHist.TransactionID
WHERE TrnHist.TransactionDate >'2007-08-25 00:00:00.000' 

The execution plan of the previous query will show us that a Clustered Index Scan will be performed on the table variable consuming 50% of the overall query execution:

Query Plan with 50% of the cost on a Clustered Index Scan

The query IO statistics show us that 113,443 scans were performed on the source table with 341,903 logical reads, where only 1 scan was performed on the table variable with 268 logical read as follows:

query IO statistics show us that 113443 scans were performed on the source table with 341903 logical reads

Moreover, the query time statistics inform us that the elapsed time of 1600 ms and 578 ms of CPU time was needed:

query took 1600 ms, consuming 578 ms from the CPU time:

Now, let us try the same scenario with Trace Flag 2453 enabled that will trigger the table variable recompilation when a specific number of rows are changed on that table:

USE MSSQLTipsDemo
GO
DECLARE @VarTable TABLE (TransactionID INT PRIMARY KEY CLUSTERED, ProductID INT,TransactionType nchar(1));
INSERT INTO @VarTable (TransactionID, ProductID,TransactionType)
SELECT TransactionID, ProductID,TransactionType
FROM Production.TransactionHistory;

DBCC TRACEON(2453);
SELECT * FROM @VarTable Vart
JOIN Production.TransactionHistory TrnHist ON Vart.TransactionID = TrnHist.TransactionID
WHERE TrnHist.TransactionDate >'2007-08-25 00:00:00.000'
DBCC TRACEOFF(2453);

The execution plan of the previous query will show us again that a Clustered Index Scan will be performed on the table variable, but now consuming only 21% of the overall query execution:

Clustered Index Scan will be performed on the table variable, but now consuming only 21% of the overall query execution

The query IO statistics when using Trace Flag 2453 show us that only 1 scan was performed on the source table with 793 logical reads, which is negligible compared to the previous result. Only 1 scan performed on the table variable with 269 logical reads, which is similar to the previous result as follows:

The query IO statistics in the 2453 trace flag case show us that 1 scan only was performed on the source table with 793 logical reads

Moreover, the query time statistics inform us that the query took 1495 ms of time compared with the 1600 ms in the previous case, consuming 188 ms from the CPU time, compared with the 578 ms in the previous result:

query took 188 ms, consuming 1495 ms from the CPU time

Using Trace Flag 2453 works somewhat like the OPTION (RECOMPILE) query hint with less overhead as it will trigger the recompile only when the number of changed rows exceeds a predefined threshold opposed to the OPTION (RECOMPILE) that will issue a recompile on each execution. In addition, the OPTION (RECOMPILE) will optimize the query depending on forced peek parameters, which is not the case with Trace Flag 2453.

In our simple scenario, if we try to use the OPTION (RECOMPILE) query hint to optimize the query performance instead of Trace Flag 2453:

USE MSSQLTipsDemo
GO
DECLARE @VarTable TABLE (TransactionID INT PRIMARY KEY CLUSTERED, ProductID INT,TransactionType nchar(1));
INSERT INTO @VarTable (TransactionID, ProductID,TransactionType)
SELECT TransactionID, ProductID,TransactionType
FROM Production.TransactionHistory;

SELECT * FROM @VarTable Vart
JOIN Production.TransactionHistory TrnHist ON Vart.TransactionID = TrnHist.TransactionID
WHERE TrnHist.TransactionDate >'2007-08-25 00:00:00.000'
 OPTION (RECOMPILE)

The result will be identical to the Trace Flag 2453 instance and the execution plan will be the same:

use the OPTION (RECOMPILE) query hint to optimize the query performance instead of the trace flag 2453

The same number of scans and logical reads:

Same number of logical reads as trace flag 2453

Finally, there is a small difference in the execution time and consumed CPU time as follows:

Elapsed and cpu time with OPTION(RECOMPILE)
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools