Sql-server – Will reindexing use the empty space or just grow table

disk-spaceindexsql serversql-server-2012

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?

Best Answer

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.