SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


tempdb: only one of several data files is growing on Index-Reorg


tempdb: only one of several data files is growing on Index-Reorg

Author
Message
WolfgangE
WolfgangE
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3461 Visits: 806
Hello there,

I'd like to understand a special behaviour.

Our tempdb-database is configured having 8 data files. As Microsoft recommends we have one data file for each core, all having same file size and grow rate.

When we start index reorganisation (NOT rebuild!) on a very big table the tempdb grows (of course).
I wonder why only 1 of the data files is growing. One might think that all the data files would grow at the same time, maybe approxemately by the same size.

Does anyone have an explanation for this behaviour?

I personally think that the reorg-process uses only one processor and so the tempdb-file assoziated with this processor at excution time will grow whereas the other files remain in their size.
By the way: If so, is there a possibility using more than one core for the reorg-process? I think it would be faster than. But the maxdop-option is only available for index-rebuild.

Thank's a lot, Wolf
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555483 Visits: 47767
WolfgangE (1/24/2013)
Our tempdb-database is configured having 8 data files. As Microsoft recommends we have one data file for each core, all having same file size and grow rate.


http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

When we start index reorganisation (NOT rebuild!) on a very big table the tempdb grows (of course).


Why 'of course'? Index reorgs shouldn't hit tempDB heavily, if at all, unless you have one of the snapshot isolation levels in use. Index reorgs use a single spare page in the user database to do the reorganisation. It's the index rebuilds (with sort in tempDB or online on) that hit TempDB hard.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


MyDoggieJessie
MyDoggieJessie
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27090 Visits: 7489
Our tempdb-database is configured having 8 data files. As Microsoft recommends we have one data file for each core, all having same file size and grow rate.
They also recommend sizing the tempdb files accordingly (for you anticipated usage) and disabling the autogrowth. Please read through the link Gail has posted.

______________________________________________________________________________
Never argue with an idiot; Theyll drag you down to their level and beat you with experience

WolfgangE
WolfgangE
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3461 Visits: 806
Thank's for the answers and the link. I'll go trough it in detail.

Yes, we have snapshot isolation set to on. I can see that during reorganisaton of our largest table the disk space of the tempdb disk is reduced dramatically (it's just because our monitoring sends a warning).
GilaMonster
GilaMonster
SSC Guru
SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555483 Visits: 47767
Right, so the TempDB usage is not due to the reorg using TempDB, it's the snapshot isolation and the row version store.

To be honest, I'm not 100% sure how the row version store interacts with multiple files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


WolfgangE
WolfgangE
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3461 Visits: 806
GilaMonster (1/24/2013)
it's the snapshot isolation and the row version store.


I'm not familiar with this term but I'll try to learn a bit more about it and why it affects our tempdb that way.

Thank you :-)
Sean Pearce
Sean Pearce
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9202 Visits: 3436
GilaMonster (1/24/2013)
To be honest, I'm not 100% sure

Shock, horror.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search