3

I have a table which is 170 GB and it has 98 GB of free/empty space. The free space was created by a massive deletion. If I run DBCC REINDEX or ALTER INDEX REBUILD, will the index use that free space first or will the free space be ignored and table will just grow?

  • 1
    Its always not guaranteed to decrease the space it can also increase the space specially if you use ONLINE index rebuild. If its is working for you its good – Shanky Jan 18 '15 at 11:41
2

The whole point of an INDEX REBUILD is to make the processing of data more efficient. This means that when possible it reorganizes the data to reduce the number of pages (according to the fill factor and other limitations) used for that index (whether it is a PK, unique, or non-unique index)and to order the rows of data more efficiently within the used pages and extents.

Therefore, it will reuse existing pages as the first choice. This should result in less storage being used. Of course if the indexes are modified, this could expand the amount of storage needed, depending on the modification.

Using tempdb to rebuild indexes can be a performance boost, but is unlikely to dramatically affect the required storage.

In this case you should definitely rebuild the indexes.

Note that this will not reduce the physical size of the .mdf and .ndf files. It will free up space that can be used as tables in the database expand in size.

1

I tested this and on my system it used the free space first. You can choose to rebuild the index using tempdb.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.