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:
[code type="SQL"]
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 [/code]
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
