.NET Information center: ALTER INDEX ALL REBUILD behavior

Wednesday, March 10, 2010

ALTER INDEX ALL REBUILD behavior

I was asked a few questions about the behavior of using “ALTER INDEX ALL… REBUILD” and thought I would detail my answers here… Quick background - using the ALL argument with ALTER INDEX REBUILD instead of naming a specific index allows you to specify all indexes associated with the table.

For example – in the below command I’m rebuilding all indexes for the HumanResources.Department table:

ALTER INDEX ALL ON HumanResources.Department REBUILD;

So I received a few questions on this topic which I’ll detail here – along with the query I used to “prove” out the answers (and if you find varying results in your own testing, I would like to hear about it):

Question: When using ALTER INDEX ALL – are all indexes rebuilt at the same time?
Answer: No. Although individual index rebuilds can use parallelism, each rebuild is executed in a serial fashion.

Question: If I have a heap – does that get included too?