March 5, 2020 at 11:51 am
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
March 5, 2020 at 12:03 pm
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,
March 5, 2020 at 12:08 pm
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.
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
March 5, 2020 at 12:41 pm
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
March 5, 2020 at 12:48 pm
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 5, 2020 at 1:00 pm
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
March 5, 2020 at 1:46 pm
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
March 5, 2020 at 1:51 pm
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
March 5, 2020 at 2:03 pm
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.
March 5, 2020 at 2:51 pm
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
March 5, 2020 at 3:00 pm
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
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
March 5, 2020 at 3:07 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 5, 2020 at 3:47 pm
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.
You can do what you want but there's no way that I'd enable this trace flag with a global scope.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2020 at 3:55 pm
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.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply