SQL Detect Fragmentation With DMV

Dynamic management view (DMV) sys.dm_db_index_physical_stats return size and fragmentation information for data and indexes for table and view. Here is some of the details about the DMV and the script I use to identify fragmentation.

The dmv take 5 parameters, sys.dm_db_index_physical_stats (YourDatabaseID, YourObjectID, YourIndexID, PartitionNumber, mode)

- If all 5 parameters are set to NULL, it return information of all indexes in the whole instance.
- If you set the first parameter to databaseID and the rest NULL, it returns information of all indexs in the database.
- Specifying database ID and object ID (table) show the information of all indexes of the table.
- Specifying database ID, object ID, and index ID return particular index information.
- If partition number is provided, it return information of the particular index on the partition.
- There are three different mode: LIMITED, SAMPLED and DETAILD. The default (NULL) is LIMITED.

Limited mode is the fastest mode by scanning the smallest number of pages, and return NULL for compressed page count. Sampled mode returns an estimate value of the compressed page count by sampling 1% sample of all pages.

Detailed mode scan all pages and returns actual value of all statistics including compressed page count. However, it is the slowest among this three mode.

USE YourDatabase;
GO

SELECT 
 DB_NAME(ps.database_id) AS dbName
 , OBJECT_NAME(ps.object_id) AS obName
 , ix.name AS ixName
 , ps.avg_fragmentation_in_percent AS fragmentPct
 , ps.page_count AS [pageCount]
 , ps.index_type_desc AS indType
 , ps.alloc_unit_type_desc AS allocType
 , ix.fill_factor
FROM sys.dm_db_index_physical_stats (DB_ID('YourDatabase'), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes ix ON
 ps.index_id = ix.index_id
 AND ps.object_id = ix.object_id
ORDER BY avg_fragmentation_in_percent DESC;

avg_fragmentation_in_percent	Logical fragmentation for indexes or extent for heaps
page_count	Total number of index or data pages
index_type_desc	Description of index type (clustered, non-clustered, etc)
alloc_unit_type_desc	IN_ROW_DATA (regular data like integer and character), LOB_DATA (Large object like varbinary(max), ROW_OVERFLOW_DATA (data that start off on IN_ROW_DATA and grow too large to fit in 8K page)
fill_factor	Fill factor percentage

The above query join the dmv with sys.index to return the fill factor. This give me an idea if fill factor setting was part of the reason causing fragmentation. It also sort the result with the highest fragmentation. Please be aware that if you do not specify object or index parameter, it may take considerable time to run the query if your database is large.

You could find list of indexes of a specific table indexes fragmentation by specifying the objectID or use OBJECT_ID if you already know the object name.

USE YourDatabase;
GO

SELECT  *
FROM sys.dm_db_index_physical_stats (DB_ID('YourDatabase'), OBJECT_ID('YourTable'), NULL, NULL, 'DETAILED');

Make sure you are in the same database if you use the OBJECT_ID system function, or use three part name like 'YourDatabase.YourSchema.YourTable'.

After determine the fragmentation, you can use this information to maintain index by REBUILD or/and REORGANIZE the index. I blog here about REBUILD and REORGANIZE.