Using Trace Flag 2453 to Improve Table Variable Performance

• nirav.gandhi7

  SSC-Addicted

  Points: 415

  More actions

  January 9, 2018 at 6:13 am

  #334338

  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.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 9, 2018 at 7:12 am

  #1975069

  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

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 10, 2018 at 9:58 am

  #1975339

  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

• nirav.gandhi7

  SSC-Addicted

  Points: 415

  More actions

  January 10, 2018 at 8:33 pm

  #1975439

  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.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 11, 2018 at 6:45 pm

  #1975660

  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

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 12, 2018 at 12:45 pm

  #1975771

  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)