October 29, 2009 at 12:01 am
Hi,
I was trying to find out the level of fragmentation for indexes on a particular object using the following query:
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'MyDB');
SET @object_id = OBJECT_ID(N'dbo.vwMyIndexedView');
SELECT IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS;
The indexed view has one clustered and two non-clustered indexes, so it appears that each index got four records (one per index level) as an output of the query:
Index_type_descavg_fragmentation_in_percent
CLUSTERED INDEX0.01
CLUSTERED INDEX0.19672131147541
CLUSTERED INDEX0
CLUSTERED INDEX0
NONCLUSTERED INDEX0.01
NONCLUSTERED INDEX2.08423795049935
NONCLUSTERED INDEX100
NONCLUSTERED INDEX0
NONCLUSTERED INDEX0.01
NONCLUSTERED INDEX1.2737009697496
NONCLUSTERED INDEX83.1325301204819
NONCLUSTERED INDEX0
Can anyone help me interpret the results? Why are there four records per index for every index level instead of one for the entire index and which of them should I use to get the fragmentation level.
Thanks.
October 29, 2009 at 12:41 am
In detailed mode, there's a row for each level of the index (see monday's article on indexes if you're not sure what I'm talking about). In limited mode there's only a row for the leaf level.
Generally you only need to look at the fragmentation of the leaf level (level 0). The others are most of the time too small to worry about. To interpret properly, add the index level column and the number of pages column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply