Understanding SQL Server Database Fragmentation Check

Understanding SQL Server Database Fragmentation Check Fragmentation is most important topic, Let see how to identify and address index fragmentation in SQL Server. Index fragmentation affects query performance, and maintaining optimal indexes is crucial for efficient database operations. 

What Is Index Fragmentation? Index fragmentation occurs when the logical order of index pages doesn’t match the physical order of data pages. As data is modified (through inserts, updates, or deletes), free space can lead to page order mismatches, resulting in fragmentation. High fragmentation negatively impacts query execution time and resource utilization. 

Identifying Fragmentation To identify fragmentation, we’ll use the sys.dm_db_index_physical_stats dynamic management view (DMV). Here’s an efficient T-SQL statement to retrieve fragmentation details:

123456789101112SELECT db_name(database_id) as dbname,
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('database name here'), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
ORDER BY indexstats.page_count desc 

You can execute above command to get the required details.

SELECT db_name(database_id) as dbname,

dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID('database name here'), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

ORDER BY indexstats.page_count desc