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,
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.