1

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

3 Answers 3

3

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
1

You could always combine the two queries into one query and output the required information in one go:

DECLARE @iDBID AS INT
SELECT @iDBID = database_id
FROM   sys.databases AS d
WHERE  d.name = DB_NAME(DB_ID())

--PRINT @iDBID
--PRINT DB_NAME(@iDBID)

SELECT si.name,
        ddips.fragment_count,
        ddips.page_count
FROM   sys.indexes AS si
       LEFT JOIN sys.dm_db_index_physical_stats(@iDBID, NULL, NULL, NULL, 'LIMITED') AS ddips
            ON  si.[object_id] = ddips.[object_id]
            AND si.index_id = ddips.index_id
WHERE  si.index_id > 0 OPTION (MAXDOP 1) 
       -- ddips.avg_fragmentation_in_percent > 5
       -- AND ddips.page_count > 1000
1

This one might give you more detailed info :

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

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

SELECT SCHEMA_NAME(so.schema_id) AS [SchemaName],
so.name AS [TableName],
idx.name AS [IndexName],
idxstats.index_type_desc AS [Index_Type_Desc],
CAST(idxstats.avg_fragmentation_in_percent
AS decimal(5,2)) AS [Frag_Pct],
idxstats.fragment_count,
idxstats.page_count,
idx.fill_factor
INTO #IndexFragStats
FROM sys.dm_db_index_physical_stats
(@DBID, NULL, NULL, NULL, 'Limited') idxstats
INNER JOIN sys.indexes idx
ON idx.OBJECT_ID = idxstats.OBJECT_ID
AND idx.index_id = idxstats.index_id
INNER JOIN sys.objects so
ON so.object_id = idx.object_id
WHERE idxstats.avg_fragmentation_in_percent > 20
ORDER BY idxstats.avg_fragmentation_in_percent DESC

SELECT * FROM #IndexFragStats

DROP TABLE #IndexFragStats

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.