Until now I was under the impression that fragmentation in the Index is the only factor which decides whether we should rebuild the index.
As a rule of thumb we always recommend that when the avg_fragmentation_in_percent in the view sys.dm_db_index_physical_stats is between 5 and 30
We should reorganize the index and when the fragmentation is greater than 30 we should rebuild the index.
(Further reading: http://technet.microsoft.com/en-us/library/ms189858.aspx)
However recently my observation showed me that avg_fragmentation_in_percent is not the only factor which should decide the rebuild the index.
The rebuild of the index is also decided by depth of the index and in this blog post I will be talking about the same.
We all are aware that Clustered Index Keys should preferably have sequential inserts to avoid fragmentation caused due to page splits. However sequential inserts can have its own problem. One of which is blogged by Amit Banerjee
Sequential inserts can lead to logical fragmentation which is not detected as physical fragmentation by sys.dm_db_index_physical_stats. Now what do I mean by logical Fragmentation?
We all know indexes in sql server are organized as B-tree structure using Balanced Tree Algorithm. However as data gets inserted in the index, the Balanced tree structure is no longer maintained and the B-Tree structure goes out of shape which leads to Logical Fragmentation.
So even in sequential inserts as data is inserted into the index the Balanced Tree structure stretches towards right and Node splits occurs which increases the depth of the index.
The depth of the index is the maximum distance between the root node and the leaf nodes. In the below case Initially when the index was built the depth of index the depth of index is 3 however after the node splits the depth of the index increases to 4.
Practical Example
===============
use tempdb
go
create table Indexdepth(a int, b char(8000))
go
DECLARE @i int
set @i = 0
while (@i < 10000)
begin
insert into Indexdepth values(@i,‘Indexdepth’)
set @i = @i + 1
end
create unique clustered index IDX_CLUSTERED on Indexdepth(a)
select * from sys.dm_db_index_physical_stats(DB_ID(‘tempdb’),OBJECT_ID(‘dbo.Indexdepth’),1,1,null)
—— Avg_fragmentation_in_percent 0 and index_depth 3
–Let us do some sequential inserts on the index
DECLARE @i int
set @i = 100
while (@i < 500000)
begin
insert into Indexdepth values(@i,‘Indexdepth’)
set @i = @i + 1
end
select * from sys.dm_db_index_physical_stats(DB_ID(‘tempdb’),OBJECT_ID(‘dbo.Indexdepth’),1,1,null)
—— Avg_fragmentation_in_percent 0.4375 and index_depth 4
So in the above example we observed that after doing around some sequential inserts the fragmentation as reported by sys.dm_db_index_physical_stats is only 0.4% which is good and does not qualify it for the Reindexing as per our rule.
However if you observe now the depth of the index has increased from 3 to 4.
Now what is the implication of the depth of the index?
Depth of the index is nothing but the minimum number of pages read when you perform Clustered Index Seek. So if the index depth is 4 a clustered index seek operation will need to do 4 logical reads atleast as compared to 3 when the index depth is 3.
Again 1 additional logical read is fine so long as the index is not involved in Key Lookup operation which is Nonclustered index seek followed by Clustered index seek. i.e following operation
Let’s say if the Index Seek qualifies 100 rows then for each row we have a clustered index seek. So there will be 100 lookups and for each lookup there may be 1 additional logical read. So in all there might be 100 extra logical reads.
In the previous example after the index is rebuild , the index_depth is reduced to 3 again.
So to conclude, Indexes should be rebuild not only when there is more than 30% fragmentation but also when the index depth is higher than 3.
Not everyone might hit the above issue but I thought I should still share it with the people who are not aware of Importance of index depth