3

I have an uncompressed clustered primary key. It consumes 63.6 GB in the main part of the clustered index with 17.9 GB LOB. The table's only non-clustered index is 57.3 GB also with 17.9 GB LOB. I wish to rebuild the clustered index offline without the database growing. Running sp_spaceused with no arguments reports 66 GB in its "unallocated space" column.

Is what I wish to do possible? Or will rebuilding the compressed index force a rebuild of the non-clustered index, thus taking at least 100 GB?

Despite my best effort, I have found an answer in neither the documentation or Stack Overflow. My experiments with a small database that I had handy suggest that rebuilding the clustered index does not force a rebuild of the non-clustered indexes.

0

1 Answer 1

5

nope

Rebuilding the clustered index will not rebuild nonclustered indexes unless you're changing the clustered index key columns.

You would also need to rebuild nonclustered indexes if you were adding a clustered index to a heap table, or dropping the clustered index, to swap keys/RIDs in the nonclustered indexes depending on the scenario.

Clustered and nonclustered indexes are separate objects, but in SQL Server they are related. Nonclustered indexes inherit clustered index key columns in order to facilitate lookups, etc. That's why some clustered index changes will necessitate nonclustered index rebuilds as well.

If you want to do this without the user database growing, it may help to add the SORT_IN_TEMPDB option to your index rebuild command. That way you only need to worry about the filegroup your index will be created on having space for the new index, rather than the new index plus the intermediate sort. This may grow tempdb, depending on its current configuration.

If that's a problem, you can restart SQL Server afterwards to set tempdb back to its default file sizes. Since you're able to rebuild a ~66GB clustered index offline, I'm working under the assumption that either this is not a highly sensitive 24x7 workload, or that this is a non-essential table.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.