dbcc traceon(2453,-1)

Question

dbcc traceon(2453,-1)

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
March 5, 2020 at 11:51 am

#3731351

I've been trying to fix some performance issues recently and started by swapping temp tables to table variables , testing performance gains at every step- quick win. until I hit the last table and the proc started running for about 8 or 9 minutes.
the proc was getting stuck on a delete from a table variable (which is ridiculous)
a bit of googling and I found dbcc traceon(2453,-1) - query now runs at 14 seconds. something to do with cardinality estimation in table variables
has anyone seen any drawbacks in this trace flag ??? at the minute i'm using it at session level, but is there any reason I shouldn't set it as a startup value?
MVDBA

Viewing 14 posts - 1 through 13 (of 13 total)

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

DinoRS SSCrazy Points: 2683 More actions · Answer 1

from looking around generally speaking eventually no

if you can use it at the session level, could you use it on a statement level? If so OPTION(RECOMPILE) might be better depending on how much row estimates change throughout a day. If estimates change rather quickly, that TF might essentially trigger more recompilations,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

MVDBA (Mike Vessey) wrote:

I've been trying to fix some performance issues recently and started by swapping temp tables to table variables , testing performance gains at every step- quick win. until I hit the last table and the proc started running for about 8 or 9 minutes.
the proc was getting stuck on a delete from a table variable (which is ridiculous)
a bit of googling and I found dbcc traceon(2453,-1) - query now runs at 14 seconds. something to do with cardinality estimation in table variables
has anyone seen any drawbacks in this trace flag ??? at the minute i'm using it at session level, but is there any reason I shouldn't set it as a startup value?

Mike - I'm usually working in the other direction for performance gains. I'm not alone.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

thanks for the links guys

- afraid the option(recompile) would be a bugger to implement as it's at statement level and some of this code is maybe 7000 lines long (hence why i'm TRYING to tune the damned thing) on some of the procs i'd have to guess I would need 200 option statements

MVDBA

Phil Parkin SSC Guru Points: 246987 More actions · Answer 4

This is interesting ... I usually head from table variables to temp tables. Are you finding improvements across the board (ie, for all temp tables), or just in certain scenarios?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

frederico_fonseca SSCoach Points: 16167 More actions · Answer 5

rather surprised that table variable are faster.

normally if the necessary indexes are added to the temp tables performance is similar if the table variable also has the same indexes.

As for delete being slower on the table variable - this happens when it does not have the required indexes

I would avoid the trace flag unless you can prove that it does not hurt overall performance due to excessive recompiles

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

this is just one scenario - you know how it is?. find the proc that is causing you issues.... find out that the guy who wrote it left 3 years ago, then start peeling every single onion layer away .. try 1 thing at a time and re-test.

On this one i'm ignoring my query plan (partly) because the plan gives the same(ish) cost of about 0.07 but i'm frazzled as to why that takes 33 seconds on average and 8 minutes with just one of the temp tables changed.

as for performance the first 2 table variables took it down from 1 minute 30 to 33 seconds

so phil - not across the board. just trying one thing at a time

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

frederico_fonseca wrote:

rather surprised that table variable are faster.
normally if the necessary indexes are added to the temp tables performance is similar if the table variable also has the same indexes.
As for delete being slower on the table variable - this happens when it does not have the required indexes
I would avoid the trace flag unless you can prove that it does not hurt overall performance due to excessive recompiles

this is the amusing part (and you have just maybe given me some inspiration) - the temp table didn't have indexes either... table variables can have an index when created... I just need to figure out what it should be and keep testing

or go back to temp tables and index them .

I'll keep trying and let you know the results

MVDBA

frederico_fonseca SSCoach Points: 16167 More actions · Answer 8

MVDBA (Mike Vessey) wrote:

frederico_fonseca wrote:
rather surprised that table variable are faster.
normally if the necessary indexes are added to the temp tables performance is similar if the table variable also has the same indexes.
As for delete being slower on the table variable - this happens when it does not have the required indexes
I would avoid the trace flag unless you can prove that it does not hurt overall performance due to excessive recompiles
this is the amusing part (and you have just maybe given me some inspiration) - the temp table didn't have indexes either... table variables can have an index when created... I just need to figure out what it should be and keep testing
or go back to temp tables and index them .
I'll keep trying and let you know the results

Then I advise that you add the index to the temp tables before going through the route of table variables.

and on the "slow" ones try both and use the best one.

There are cases where adding an index to a temp table either have no impact or make the overall process slow.

but by default having the index will either keep same performance or improve it so I always advise creating one by default (according to its usage obviously) and if more are needed add them - same as with any normal table.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

frederico_fonseca wrote:

MVDBA (Mike Vessey) wrote:
frederico_fonseca wrote:
rather surprised that table variable are faster.
normally if the necessary indexes are added to the temp tables performance is similar if the table variable also has the same indexes.
As for delete being slower on the table variable - this happens when it does not have the required indexes
I would avoid the trace flag unless you can prove that it does not hurt overall performance due to excessive recompiles
this is the amusing part (and you have just maybe given me some inspiration) - the temp table didn't have indexes either... table variables can have an index when created... I just need to figure out what it should be and keep testing
or go back to temp tables and index them .
I'll keep trying and let you know the results
Then I advise that you add the index to the temp tables before going through the route of table variables.
and on the "slow" ones try both and use the best one.
There are cases where adding an index to a temp table either have no impact or make the overall process slow.
but by default having the index will either keep same performance or improve it so I always advise creating one by default (according to its usage obviously) and if more are needed add them - same as with any normal table.

there are 2 things - historically we've been told to use table variables as they can "sometimes" be in memory (no one has ever given me details on when it's in memory)

secondly even modern tools like redgate's sql prompt start putting squiggly lines under temp tables and hinting that it should be a table variable.

I'll just keep trying, testing and figuring it out on a case by case basis

MVDBA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

There are some excellent papers out there discussing the pros and cons of table variables and temp tables. Here's one of my favourites: https://www.sql.kiwi/2012/08/temporary-tables-in-stored-procedures.html

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 246987 More actions · Answer 11

MVDBA (Mike Vessey) wrote:

there are 2 things - historically we've been told to use table variables as they can "sometimes" be in memory (no one has ever given me details on when it's in memory)
secondly even modern tools like redgate's sql prompt start putting squiggly lines under temp tables and hinting that it should be a table variable.
I'll just keep trying, testing and figuring it out on a case by case basis

You can find extended Redgate documentation about that rule (which I have disabled!) here.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

The trace flag can be set at the session level (add 1 line of code at the beginning of the proc). I wouldn't set this at the server level because you don't know what will end up recompiling. It could be a whole lot more than you planned on.

Some more documentation.

https://www.brentozar.com/archive/2017/02/using-trace-flag-2453-improve-table-variable-performance/

Search for "2453" in the following link. Read the warnings. Also notice that it says that you can use QUERYTRACEON for this trace flag...

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

...but, the following says you cannot. When MS documentation doesn't match, it scares the hell out of me.

https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-o

You can do what you want but there's no way that I'd enable this trace flag with a global scope.

--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)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 13

Jeff Moden wrote:

The trace flag can be set at the session level (add 1 line of code at the beginning of the proc). I wouldn't set this at the server level because you don't know what will end up recompiling. It could be a whole lot more than you planned on.
Some more documentation.
https://www.brentozar.com/archive/2017/02/using-trace-flag-2453-improve-table-variable-performance/
Search for "2453" in the following link. Read the warnings. Also notice that it says that you can use QUERYTRACEON for this trace flag...
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15
...but, the following says you cannot. When MS documentation doesn't match, it scares the hell out of me.
https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-o
You can do what you want but there's no way that I'd enable this trace flag with a global scope.

i'm with you on that idea - it's currently one of the first lines in my dev code for that proc until I figure out the stupid way the code has been built

MVDBA