FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014
Content provided by Microsoft
Applies to: Microsoft SQL Server 2012 Service Pack 2Microsoft SQL Server 2012 EnterpriseMicrosoft SQL Server 2012 DeveloperMicrosoft SQL Server 2012 WebMicrosoft SQL Server 2012 StandardMicrosoft SQL Server 2014 DeveloperMicrosoft SQL Server 2014 DeveloperMicrosoft SQL Server 2014 EnterpriseMicrosoft SQL Server 2014 EnterpriseMicrosoft SQL Server 2014 StandardMicrosoft SQL Server 2014 StandardMore
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.
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You do not have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update:
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.
ERROR: at System.Diagnostics.Process.Kill()
at Microsoft.Support.SEOInfrastructureService.PhantomJS.PhantomJSRunner.WaitForExit(Process process, Int32 waitTime, StringBuilder dataBuilder, Boolean isTotalProcessTimeout)New URL: about:blank