May 7, 2012 at 12:03 am
Hi All
A question regarding the sys.dm_db_index_physical_stats DMV
This is the test code:
SELECT SO.name,
SI.name IndexName
,SI.type_desc IndexType
,IPS.Avg_Fragmentation_In_Percent
, IPS.index_level
, SI.index_id
FROM sys.indexes SI
INNER JOIN sys.objects SO
ON SO.object_id = SI.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID('AdventureWorks'), OBJECT_ID('Person.Contact'), 1 , NULL, N'DETAILED')IPS
ON IPS.OBJECT_ID = SI.object_id
AND IPS.index_id = SI.index_id
where SI.name is not null
Regarding Index levels:
Root: Highest Number
Leaf: 0
Intermediate: Anything inbetween the Root and Leaf numbers
My question is: How can there be more that 1 "0" Levels in an index?
When I execute the above code against the AdventureWorks database, it shows 2 "0" levels for the given index
Is this normal
Thanks
Derek
May 7, 2012 at 1:16 am
derekr 43208 (5/7/2012)
When I execute the above code against the AdventureWorks database, it shows 2 "0" levels for the given indexIs this normal
I think it is normal. I think you saw 2 "0" levels for different index_type/alloc_unit..
index_type_descalloc_unit_type_descindex_depthindex_level
CLUSTERED INDEXROW_OVERFLOW_DATA10
CLUSTERED INDEXLOB_DATA10
NONCLUSTERED INDEXIN_ROW_DATA20
CLUSTERED INDEXIN_ROW_DATA30
NONCLUSTERED INDEXIN_ROW_DATA20
May 7, 2012 at 1:19 am
Suresh B. (5/7/2012)
derekr 43208 (5/7/2012)
When I execute the above code against the AdventureWorks database, it shows 2 "0" levels for the given indexIs this normal
I think it is normal. I think you saw 2 "0" levels for different index_type/alloc_unit..
index_type_descalloc_unit_type_descindex_depthindex_level
CLUSTERED INDEXROW_OVERFLOW_DATA10
CLUSTERED INDEXLOB_DATA10
NONCLUSTERED INDEXIN_ROW_DATA20
CLUSTERED INDEXIN_ROW_DATA30
NONCLUSTERED INDEXIN_ROW_DATA20
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply