Introduction:
In this post, We will check the effect of creating, dropping or rebuilding clustered index or Heap on non clustered indexes on the same table.
Demo:
Let's Create a Table first
CREATE Table IndexTest
(ID int IDENTITY,
Val varchar(100)
)
Insert The records in IndexTest table
INSERT INTO IndexTest
Select RAND()
GO 15000
Create Non Cluster Index
CREATE INDEX IDX_NC_Indextest ON IndexTest(Val)-- Non Cluster Index on Val column
Check Index Fragmentation
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbtables.[name]='IndexTest'
ORDER BY
indexstats.avg_fragmentation_in_percent desc
Delete some records from Indextest table
DELETE FROM IndexTest Where ID%3=0
Create Cluster Index on IndexTest table
CREATE Clustered INDEX IDX_Clustered_Indextest ON IndexTest(ID)--Cluster Index on Val column
Check Index Fragmentation
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbtables.[name]='IndexTest'
ORDER BY
indexstats.avg_fragmentation_in_percent desc
Creating cluster index also forced all non cluster indexes to Rebuild. Check the fragmention and Number of pages
Take Away #1 Creating Cluster Index , Rebuild all the non cluster Indexes on the same table.
DELETE FROM IndexTest Where ID%5=0
ALTER INDEX IDX_Clustered_Indextest ON IndexTest Rebuild
Check Index Fragmentation
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbtables.[name]='IndexTest'
ORDER BY
indexstats.avg_fragmentation_in_percent desc
Rebuilding Cluster Index do not force non cluster index to rebuild as Non cluster Indexes are poining to key values not RID
Take Away #2 Rebuilding Cluster Index , Do not Rebuild the non cluster Indexes
Drop the cluster Index
DROP INDEX IDX_Clustered_Indextest ON IndexTest
Check Index Fragmentation
Dropping the cluster Index forced all the non cluster indexes to rebuild as they have to point to RID again
Take Away #3 Dropping Cluster Index , Rebuild all the non cluster Indexes on the same table
Delete few more records from the table
DELETE FROM IndexTest Where ID%7=0
ALTER TABLE IndexTest Rebuild
Check Index Fragmentation
Rebuilding the heap forced all the non cluster indexes to rebuild as RID changed
Take away #4 Rebuilding Heap , Rebuild all the non cluster Indexes on the same table.
So we can conclude that:
1. Creating Cluster Index , Rebuild all the non cluster Indexes on the same table
2. Rebuilding Cluster Index , Do not Rebuild the non cluster Indexes
3. Dropping Cluster Index , Rebuild all the non cluster Indexes on the same table
4. Rebuilding Heap , Rebuild all the non cluster Indexes on the same table
Hi Sauras,
very good column, thank you for it!
I was solving one problem with disabling indexes, because on customers HW is not much space nor much memory, and TempDB is on C drive and so on… so loading big bunches of data raises errors about insufficient space …etc. Only way how to load data is disable all indexes and use WITH (TABLOCK) hint in insert to make things faster and remove logging into DB LOG. So, this is nice, until point that table is not accessible as long as CLustered INdex is disabled, only way is to delete it. I was asking myself, what about the rest NonCLustered INdexes on table, shall I enable them or what to do? Answer is simple, once I start (re)creation of CLIN MSSQL internally rebuilds rest of NCLINes and probably that way it enables them. So, after Insert operation, I just create CLIN and that rest of indexes on table goin’ to be rebuilded (and there fore enabled in this step).
Only explicit rebuild of CLIN will not take effect to NCLINs as mentioned in point 2.
Cheers, Ondrej
PS: for dropping CLIN it enables (rebuilds) NCLINs and ad point 4; for explicit rebuild HEAP it also enables (rebuilds) NCLINs
Ondrej Liptak, I am happy that you liked the blog 🙂