Hello ,

is there a way to estimate the space freed by index rebuild in sql 2008 R2 ?

thanks

Hello,

I'm not sure I understand your question... What freed space?

Looking at dm_db_index_physical_stats will give you the fragmentation, and fragmentation about the best indicator of how much space your wasting that would be recovered from a rebuild.  For a deeper dive into it, you may want to read Paul Randall's work looking into the buffer pool, which is essentially just an in-memory copy of some of your indexes.  That's here: http://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/

However, your best bet is to just have regular index maintenance where this doesn't get out of hand.  If you don't have anything now, use Ola's scripts: http://ola.hallengren.com/

Hello ,

is there a way to estimate the space freed by index rebuild in sql 2008 R2 ?

thanks

Its difficult to predict if you think that all unnecessary space which fragmented index has taken would be released thats not correct.

Dont you think best way is to try it out yourself ?

Simple answer - No way. 

Steve , The question came from a Customer , so it does not have to make a sense. We already ran the index rebuild several times at their location This time they asked , I don't know why , if there's a way to predict how much space would be reclaimed Thanks

Rebuild index does not free space.. It does consume space and fix the fragmentation. 

Hallo Uri,

in general its not true what you write :).

Let's say you have a clustered index with a page density of 50% (clustered key is a GUID!). If I run an extrem workload i run into a huge amount of page splits. When I run an INDEX REBUILD afterwards and the page density has been increased to 95% i get a real benefit.

The benefit can be measured by the no of pages before the rebuild and the pages after the rebuild.

Steve , The question came from a Customer , so it does not have to make a sense. We already ran the index rebuild several times at their location This time they asked , I don't know why , if there's a way to predict how much space would be reclaimed Thanks

Stefano,

i think there is a small way you can ESTIMATE the amount of data which can be saved when rebuilding indexes.

Therefore you need to have a few measures which you have to take into consideration:

CLUSTERED INDEX:

- what is the length of a record?
- who many records will fit into one data page?

The first question may envisage the problem why you cannot predict exactly the amount of pages you will have after a REBUILD.

If your table is using variable attributes (varchar, nvarchar, ...) no prediction is possible because the data length vary.

NON CLUSTERED INDEX

- what is the size of the index keys?
- does the index have INCLUDEDd attributes

Now the prediction of the size is nearby impossible. If you have INCLUDEd attribute you have to add this space to the number of leaf level pages (INCLUDED attributes will not be stored in the b-tree).

If there is any attribute which is again varchar, nvarchar you may run into the same calculation risk as with the clustered index.

Last but not least...

- do you use PAGE or ROW compression?

:) - no prediction possible.

If the customer would like to have a number put the number of pages BEFORE the rebuild into a staging table and after the REBUILD. Now you have the difference and you can give your customer a value (before_pages - previous_pages *

Hi Uwe

>>When I run an INDEX REBUILD afterwards and the page density has been >>>increased to 95% i get a real benefit.

Actually I was under impression free space of the disk but if you talk about it I would say that Fill Factor determines how much (on leaf level)  data will  be on the index page. So what I said was not incorrect ?:-)