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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » tempdb: only one of several data files is...

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

Rate Topic

Display Mode

Topic Options

Author

Message

WolfgangE

Posted Thursday, January 24, 2013 5:47 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:56 AM
Points: 74, Visits: 419

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

Post #1411088

GilaMonster

Posted Thursday, January 24, 2013 5:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 37,665, Visits: 29,917

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 2008, MVP
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

Post #1411093

MyDoggieJessie

Posted Thursday, January 24, 2013 6:45 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:50 AM
Points: 2,035, Visits: 3,759

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; They'll drag you down to their level and beat you with experience" Wink

Post #1411111

WolfgangE

Posted Thursday, January 24, 2013 7:05 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:56 AM
Points: 74, Visits: 419

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

Post #1411120

GilaMonster

Posted Thursday, January 24, 2013 7:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 37,665, Visits: 29,917

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 2008, MVP
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

Post #1411124

WolfgangE

Posted Thursday, January 24, 2013 7:32 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:56 AM
Points: 74, Visits: 419

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 Smile

Post #1411137

Sean Pearce

Posted Thursday, January 24, 2013 12:59 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 341, Visits: 1,308

GilaMonster (1/24/2013)

To be honest, I'm not 100% sure

Shock, horror.

http://thesqlguy.blogspot.com/

Post #1411316

« Prev Topic | Next Topic »

Permissions