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?

Read more: Joe Sack's SQL Server WebLog

Posted via email from jasper22's posterous