FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014

Applies to: SQL Server 2012 EnterpriseSQL Server 2012 DeveloperSQL Server 2012 Web


When you populate a table variable with many rows and then join it with other tables, the query optimizer may choose an inefficient query plan, which may lead to slow query performance.


After you apply this hotfix, you can turn on trace flag 2453 to allow a table variable to trigger recompile when enough number of rows are changed. This may allow the query optimizer to choose a more efficient plan.

The issue was first fixed in the following cumulative update or/and Service Packs for SQL Server.

Cumulative Update 3 for SQL Server 2014

Service pack 2 for SQL Server 2012

More Information

When you use a table variable in a batch or procedure, the query is compiled and optimized for the initial empty state of table variable. If this table variable is populated with many rows at runtime, the pre-compiled query plan may no longer be optimal. For example, the query may be joining a table variable with nested loop since it is usually more efficient for small number of rows. This query plan can be inefficient if the table variable has millions of rows. A hash join may be a better choice under such condition. To get a new query plan, it needs to be recompiled. Unlike other user or temporary tables, however, row count change in a table variable does not trigger a query recompile. Typically, you can work around this with OPTION (RECOMPILE), which has its own overhead cost.
The trace flag 2453 allows the benefit of query recompile without OPTION (RECOMPILE). This trace flag differs from OPTION (RECOMPILE) in two main aspects.
(1) It uses the same row count threshold as other tables. The query does not need to be compiled for every execution unlike OPTION (RECOMPILE). It would trigger recompile only when the row count change exceeds the predefined threshold.
(2) OPTION (RECOMPILE) forces the query to peek parameters and optimize the query for them. This trace flag does not force parameter peeking.

Note this trace flag must be ON at runtime. You cannot use this trace flag with QUERYTRACEON. This trace flag must be used with caution because it can increase number of query recompiles which could cost more than savings from better query optimization.


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.