Reasons for not Globally enabling Trace Flag 2453 - Cardinality on Table Variables

I've been doing some reading on the improvements that Trace Flag 2453 can give on the performance of Table Variables by maintaining statistics and was wondering what would be the reasons you would advise to not turn this on globally.

I understand there will be some overhead of recompiles if the number of rows being used substantially differs - but to me, this would appear similar to any other query, and be preferred behaviour if it is caching better query plans.

Is there other reasons why this would not be preferred behaviour to generate statistics on table variables, similar to temp tables?

Primarily I'm thinking about replacing a few places where we use XML to bring in data sets and use Table-Valued-Parameters instead. We do use temp tables for the most part.

The main reasons not to enable TF 2453 globally are:

• You have queries that deliberately used table variables for the one-row guess.

• You absolutely must avoid recompilations as much as possible.

In many cases, you will still find that using other solutions (temporary tables, recompile hints) are better overall. Trace flag 2453 benefits a relatively small class of queries for which a table variable is the absolute best choice, but table cardinality is important without needing full statistics.

Somewhat related reading:

• Interleaved execution for multi-statement table valued functions

• Public Preview of Table Variable Deferred Compilation in Azure SQL Database

• What's New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

StackExchange Database Administrators Q#218465, answer score: 2