Sql Server: Does rebuilding clustered index automatically rebuild nonclustered indexes
- Posted by Sqltimes
- On January 18, 2014
- 0 Comments
When you have large tables in your database you want to plan maintenance strategically. Its a fine balance that you plan carefully to make sure that you
- Perform necessary maintenance successfully
- But not cause any blocking to table access
- Dependent nonclustered index are rebuilt (if needed) or not rebuild.
What commands does Sql Server provide to facilitate these operations.
Lets take an example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE TABLE dbo.Frag_CI( ID INT NOT NULL IDENTITY(1,1) , Frag VARCHAR(800))GOCREATE CLUSTERED INDEX PK_Frag_CI_ID ON dbo.Frag_CI (ID ASC)GOCREATE NONCLUSTERED INDEX IX_Frag_CI_Frag ON dbo.Frag_CI (Frag ASC)GOINSERT INTO dbo.Frag_CI (Frag) VALUES ('1')GO 1000 |
Now, let’s check fragmentation. We see that both the clustered and nonclustered indexes are significantly fragmented.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
select OBJECT_NAME(object_id) AS Table_Name , index_type_desc , index_depth , index_level , avg_fragmentation_in_percent , fragment_count , avg_fragment_size_in_pages , page_count , avg_page_space_used_in_percent , record_count , forwarded_record_countfrom sys.dm_db_index_physical_stats(db_id(),default,default,default,DEFAULT)where object_id = object_id('Frag_CI')GO |
CI_nCI_Rebuild_Before
Solution
Option 1 :
Rebuild the table, resulting in rebuilding both CI and nCI together.
|
1
|
ALTER INDEX ALL ON dbo.Frag_CI REBUILD |
Above code allows us to rebuild all the indexes on the table at the same time while applying all the options available to reduce the duration of overall rebuild time i.e. MAXDOP, ALLOW_PAGE_LOCKS, etc
CI_nCI_Rebuild
Option 2 (new):
Slight modification to previous ALTER INDEX command allows us to rebuild just the CLUSTERED index and not rebuild relevant non-clustered indexes.
When you run ALTER INDEX with ALL, it affects all the indexes (obviously). Without ALL keyword it only affects the selected index.
|
1
2
|
ALTER INDEX PK_Frag_Col1 ON dbo.Frag_CI REBUILDGO |
Option 3:
Here we rebuild the table, using ALTER TABLE command, but surprisingly it only rebuilds the clustered index and does not touch the nonclustered indexes. Interesting to see the difference in behavior between ALTER TABLE….REBUILD ALTER INDEX…REBUILD. Also, there is no ALL keyword option for ALTER TABLE. (Please note that PARTITION=ALL only affects the partitions selected to rebuild with in the CI).
|
1
|
ALTER TABLE dbo.Frag_CI REBUILD |
CI_Rebuild_no_nCI
Option 4:
In this, we rebuild the index using “CREATE INDEX…WITH DROP_EXISTING” option. As expected, this only rebuilds clustered index (nonclustered index is not rebuilt). In a clustered and non-clustered index setup, clustering key is present in the leaf pages of the nonoclustered index. So, when CI is rebuilt, clustering key does not change, so there is no need to rebuild nCI.
|
1
2
3
4
|
CREATE CLUSTERED INDEX PK_Frag_CI_ID ON dbo.Frag_CI (ID ASC) WITH (DROP_EXISTING = ON) GO |
CI_Rebuild_no_nCI
So, in conclusion, we see that when we rebuild CLUSTERED index, only the CI is affected. None of the relevant non-clustered indexes (nCIs) are rebuilt. Each nCI, at the leaf level has clustering key from the clustered index. Since that does not change during rebuild, the nCI does not need to be rebuilt.
Hope this helps,
_Sqltimes
0 Comments