Sql-server – understanding index fragmentation statistics

indexsql servert-sql

I'm using the query outlined in this stack exchange answer, reproducing it here for convenience…

Here is a simple query to check fragmentation on your existing
indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count 
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats 
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY 
d.fragmentation DESC

DROP TABLE #indexDefragList

This will return a list of all indexes in your current DB with their
fragmentation in %.

The output from this query is confusing me.

I've limited the results to cover just a single index to simplify my question…

results table

Some indexes are listed once, others appear repeated in varying numbers with different statistics.

What I'm struggling to understand is, why are some indexes returning multiple rows with different fragmentation and page_count statistics.

Is this a result of some implementation detail of MS-SQL indexes, if so how should I reconcile the different fragmentation numbers? Can you explain these results to me please?

Or, is this a problem with my database somehow reproducing multiple versions of indexes when all i need is one? Is this a problem i need to worry about??

Or, is it a problem with the query?

Thanks x

Best Answer

Your query is slightly off, you need to include the index id in your join on sys.indexes

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count 
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats 
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
AND d.indexID = i.index_id
ORDER BY 
i.object_id,d.fragmentation DESC

DROP TABLE #indexDefragList
Related Question