4

We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and the server ran out of disk space so I've been up since 12:30 am.

Now I'm wondering does SQL Server even do anything when it rebuilds this index? There are no leaf pages or page splits since all the inserts go into the last page. And we never delete rows (not yet anyway).

Obviously the server tried to do something, but if it had succeeded would the table be any different? I am thinking this is just a giant no-op and a waste of time. Obviously this is not true if the table has non-clustered indexes as well. And actually if this table did have additional indexes it still doesn't NEED to rebuild the primary index first if like our table it was all just adds, right?

My DBA says he manages all the indexes at the database level, not at the table level.

Additional information

My assumption is that after the rebuild the pages on disk look the same as they did before and we're just wasting our time database shuffling.

  • 2
    Note that updates involving (say) varchar values getting bigger could still force a page split, which would still leave stuff to be defragmented, I believe. So could such values getting smaller, if it shrunk the rows on a page enough for another row to fit, I suppose. – RDFozz Sep 13 '17 at 20:54
5

Since you don't delete rows, no, rebuilding the index will provide no real value outside of updating the statistics for said index, as they are implicitly updated with a rebuild operation. A more efficient approach is to just run an UPDATE STATISTICS statement instead.

Per Benjamin Nevarez:

Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN.

To answer your other questions:

would the table be any different?

Other than located at a different page address, no, structurally it would likely be no different.

Obviously this is not true if the table has non-clustered indexes as well.

Rebuilding the clustered index doesn't automatically rebuild any non-clustered indexes associated with the table. To do this, you would need to specify the keyword ALL instead of the clustered index name. Per MS:

...using ALTER INDEX…REBUILD to rebuild a clustered index does not rebuild its nonclustered indexes by default... You have to specify ALL for the index name in order to rebuild all indexes.

And the last question:

if this table did have additional indexes it still doesn't NEED to rebuild the primary index first if like our table it was all just adds, right?

Correct. If you never modify a record after it's added to the end of the table, you'd really only ever need to update statistics in order to avoid the Ascending Key Problem as identified by Scott Hodgin's comment below.

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.