Using Trace Flag 2453 to Improve Table Variable Performance

  • We have enabled trace 2453( table variable performance) in SQL(2016) Server level and fix the performance issues.

    Is there any disadvantage to enable this trace in SQL Server Level?

    Please let me know you thoughts on this.

  • nirav.gandhi7 - Tuesday, January 9, 2018 6:13 AM

    We have enabled trace 2453( table variable performance) in SQL(2016) Server level and fix the performance issues.

    Is there any disadvantage to enable this trace in SQL Server Level?

    Please let me know you thoughts on this.

    The disadvantage is documented in the Microsoft article about the trace flag:
    FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014
    “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â€.

    Tuning individual queries using temp tables or option recompile may be better options.

    Sue

  • nirav.gandhi7 - Tuesday, January 9, 2018 6:13 AM

    We have enabled trace 2453( table variable performance) in SQL(2016) Server level and fix the performance issues.

    Is there any disadvantage to enable this trace in SQL Server Level?

    Please let me know you thoughts on this.

    I have a better solution: ALMOST NEVER use table variables. Two exceptions: a) extremely high volume calls where recompilations are a known pain point, b) you need data in temp object to remain after rolling back a transaction. Both of those are exceptionally rare in my 20+ years of SQL Server consulting experience.

    The reason is table variables have no column value statistics. I can give you a one-row-one-column example using AdventureWorks where you get a bad plan with table var and good plan with temp table.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thanks. but right now we are not going to change any objects on current database.we have very stable product so we will consider your point in future.

    Thanks for your time.

  • nirav.gandhi7 - Wednesday, January 10, 2018 8:33 PM

    Thanks. but right now we are not going to change any objects on current database.we have very stable product so we will consider your point in future.

    Thanks for your time.

    Kind of interesting to hear you say that considering that you changed a trace flag that alters the SQL Server optimizer's operations and then decided to ask if there are disadvantages to it. :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • nirav.gandhi7 - Wednesday, January 10, 2018 8:33 PM

    Thanks. but right now we are not going to change any objects on current database.we have very stable product so we will consider your point in future.

    Thanks for your time.

    If you turn the Trace Flag on for the scope of a proc, that might be ok with some DBAs.  If you require folks to turn it on at the system level, folks like me won't allow the product to be installed.

    I'll also state that if you need this trace flag for your code to run efficiently, you may have less stable code than you think.

    I also share the concern that you turned the Trace Flag on without knowing the ramifications.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply