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?

Answer: No.  Only the clustered index (if exists) and associated nonclustered indexes get rebuilt.  Heaps are ignored.

Question: In what order are the indexes rebuilt?

Answer: I tested this out using a query that I’ll show at the end of this blog post.  The order is by index_id (for example – 1,2,3,4)  – which is then clustered index first, followed by nonclustered indexes.  Since the clustered index is always “1” – this reinforces this behavior.  I only saw one exception to this rule, where the indexes were so small that they both appeared to kick off at 2010-03-09 09:52:53.230 – so index id “2” appeared before “1” – but I believe this is a precision consideration and I think the clustered index started ever so slightly before the nonclustered index.

In order to generate the answers to these questions, I used ALTER INDEX ALL against most tables in the AdventureWorks2008 database and then used the following query to identify rebuild timings and associated index types:

SELECT      OBJECT_NAME(u.object_id) objnm,

            u.index_id,

            i.name indnm,

            i.type_desc,

            u.system_scans,

            u.last_system_scan

FROM sys.dm_db_index_usage_stats u

INNER JOIN sys.indexes i ON

      u.object_id = i.object_id AND

      u.index_id = i.index_id

WHERE last_system_scan IS NOT NULL

ORDER BY OBJECT_NAME(u.object_id), last_system_scan

 

The key ingredient to this query is the last_system_scan column from sys.dm_db_index_usage_stats which gets updated after an index rebuild operation.  In my test I cleared the stats of this DMV by restarting the SQL Server instance (so if you are doing your own testing – needless to say please don’t do that in production). 

If you find any exceptions to the rule on using ALTER INDEX ALL in your own testing, drop by a comment or email.