May 23, 2014 at 9:55 am
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...
May 23, 2014 at 10:14 am
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.
May 23, 2014 at 10:22 am
As Ed said, yes it would.
---------------------------------------------------------------------
May 23, 2014 at 10:22 am
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?
May 23, 2014 at 12:03 pm
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.
May 23, 2014 at 5:05 pm
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply