Table Variables And TF2453

Tara Kizer investigates Trace Flag 2453:

I recently saw a server with trace flag 2453 configured. I hadn’t come across this trace flag before, so I did a little research. Microsoft says it allows “a table variable to trigger recompile when enough number of rows are changed”. This can lead to a more efficient execution plan. Trace flag 2453 is available in SP2 or greater for SQL Server 2012, CU3 or greater for SQL Server 2014 and RTM or greater for SQL Server 2016.

I was curious how a query using a table variable performed as compared to the “same” query using:

  • trace flag 2453

  • OPTION (RECOMPILE)

  • a temporary table

Click through for a relative performance comparison.

Related Posts

Dealing With Trace Flags In The Registry

Wayne Sheffield shows us how to configure SQL Server trace flags within T-SQL using registry access commands: In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so. In this example, we will […]

Read More

Trace Flags Used With Query Store

Erin Stellato describes two Query Store trace flags: Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752.  The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728