Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Space Required For Index Rebuilds


Space Required For Index Rebuilds

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45377 Visits: 39936
While working with multiple FileGroup/File combos for table partitioning and setting up for PieceMeal restores, I ran into the following problem as quoted from the MS article at http://technet.microsoft.com/en-us/library/ms179542.aspx. The embolding is mine.

When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.


This problem will easily cause the given File to double in size even if SORT IN TEMPDB = ON. Of course, when setting a File for something like an Audit table to READ ONLY and you have 1 of these files for each of more than four years, the amount of wasted disk space in each of those files really starts to add up. Essentially, you're doubling the disk space required for a huge table.

Now, I've figured out a way to keep that from happening each month when I go to set the previous month's file to READ ONLY (it would be a problem even if I didn't set it to READ ONLY, BTW), but it's complex, offline, and reasonably slow because it requires that you copy the data out of the file to another FileGroup/File, SWITCH the original data to another table on the same File/FileGroup, dropping the SWITCH table, shrinking the bloated file I just moved the data out of to 0, calculate the size of the data and indexes of the copied data, resize the previously bloated file using that number so as to avoid fragmentation caused by miniscule growth factors, and then reapply the partitioned CI to the moved data to overcome the problem of the original index staying active on the file group until the new index is created.

My question is, does anyone have a trick up their sleeve that would make rebuilding a clustered index on a single file partition any easier and without the doubling in size due to the delayed deallocation problem cited at the URL I posted at the beginning of this post? Or am I pretty much stuck with the relatively complex method I've developed for this?

As a bit of a sidebar on this subject, I'd love to ask MS "What were you thinking?" Peter Norton got this type of stuff down pat literally decades ago.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47402 Visits: 44399
Not the solution, but the reason.

When rebuilding an index, the old index is only dropped once the new one is complete. It's for ease of rollback (can just drop the part-built index structure) and performance (SQL reads the old index to create the new one, alleviating the need to do a sort or read more data than necessary or re-apply any filter in the case of a filtered index), also for online rebuilds to allow queries to read and use the index during the rebuild.

This isn't the sort space, which is what goes into TempDB when sort in TempDB is on.

You could try rebuild twice then shrink with truncate_only. Otherwise REORGANIZE which doesn't recreate the index and hence has no such space requirements


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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45377 Visits: 39936
Update... I may have answered my own question but I haven't tried it, yet. I had previously rejected the idea of using just DBCC SHRINKFILE because the TRUNCATEONLY option doesn't seem to work well on the files of the very active PRIMARY file group and, of course, not using the TRUNCATEONLY option causes massive fragmentation. Since the files that I'd be rebuilding the Clustered Index on would not have any modifications or new inserts in progress, it might just work if I apply it immediately after each index rebuild on the small partition file.

Still, if anyone has a better idea, I'd love to hear it. It would be, as my boss says, a "spec-hackular" idea.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45377 Visits: 39936
Heh... great minds think alike, Gail. You posted while I was writing. Thanks for the confirmation on the TRUNCATEONLY thing. I'll give it a try.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47402 Visits: 44399
Jeff Moden (12/8/2013)
Since the files that I'd be rebuilding the Clustered Index on would not have any modifications or new inserts in progress, it might just work if I apply it immediately after each index rebuild on the small partition file.


It won't, because the new index will be at the end of the file and the empty space at the beginning. Hence why I suggested rebuild *twice*, then the second rebuild should, hopefully reuse the space that the index was initially in, leaving free space at the end of the file which can be truncated.


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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45377 Visits: 39936
Ah. Got it. Thanks, Gail.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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