3

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.

2

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.