Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Would rebuilding / reorganizing an index free up space in a DB? Expand / Collapse
Author
Message
Posted Friday, May 23, 2014 9:55 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:06 PM
Points: 752, Visits: 5,531
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...
Post #1574129
Posted Friday, May 23, 2014 10:14 AM This worked for the OP Answer marked as solution
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:28 PM
Points: 4,611, Visits: 4,067
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
Post #1574135
Posted Friday, May 23, 2014 10:22 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:57 AM
Points: 5,888, Visits: 13,062
As Ed said, yes it would.

---------------------------------------------------------------------

Post #1574139
Posted Friday, May 23, 2014 10:22 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:06 PM
Points: 752, Visits: 5,531
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?
Post #1574140
Posted Friday, May 23, 2014 12:03 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:28 PM
Points: 4,611, Visits: 4,067
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
Post #1574176
Posted Friday, May 23, 2014 5:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 35,772, Visits: 32,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1574263
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse