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?
Read more: Joe Sack's SQL Server WebLog
Posts
