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


Would rebuilding / reorganizing an index free up space in a DB?


Would rebuilding / reorganizing an index free up space in a DB?

Author
Message
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 10946
So, testing out some code to create rebuild / reorg statements for indexes, (in QA, of course) I had a thought.

Sadly, while I could've checked this myself, I'd already started the rebuild / reorg when I thought of it, so I'm turning to the forums.

In theory, would rebuilding / reorganizing indexes also free up some space in the database? I think it should, myself, especially a rebuild, as that creates a whole new index. Of course, this would depend on the fill factor being used (0 I would think would free up the most space, other fill factors could potentially use *more* space.)

I know it won't release the space back to the file system, but that's not what I'm wondering about. I know how to get the space back to the file system, and that's not something I'm needing to do for any reason...

Thanks for satisfying my curiousity...
Jason

PS. I'd test this myself, but I can't (easily) do this at work, and I'll probably get occupied with other stuff around the house this weekend and forget about this...
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 9606
All other things being equal (fill factor, etc.) then yes it could. If you have inserts over time, then you likely have unused space in the index. Doing a reorganize or rebuild physically reorders the pages in the leaf level. It also eliminates unused space on each page (subject to the fill factor, of course) thereby compacting them.

One word of caution, though, is if you use sort_in_temp_db = off, then the space used in your operation could cause the database to grow.

You're right in that any space gained will only be free space inside the database and will not be yielded back to the operating system.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6362 Visits: 13687
As Ed said, yes it would.

---------------------------------------------------------------------
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 10946
Thanks Ed!

Yeah, I've been doing some poking around on the sort_in_tempdb...
Pretty much, do I want the DB to (potentially) grow, or TempDB?
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 9606
I generally sort in tempdb because I don't want my database to grow. Growing takes time and consumes space long-term. Also, the tempdb is usually on a different disk, so that can reduce the amount of time the index sort takes to complete. If that disk happens to be an SSD, that's even better.

All that being said, if the sort can be done in memory, the operation ignores the sort_in_tempdb option completely. I admit that some of the larger tables won't have a prayer of being able to be done in memory, but that threshold depends on how much memory your servers have and what's being consumed by SQL and everything else.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
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: 45390 Visits: 39941
There are some other optimizations and considerations to be made, as well.

Even if space and concurrent availabilty aren't issues for someone, there are a ton of optimizations and caveats to defragging indexes and I strongly recommend doing a deep dive on all the options in Books Online.

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