A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
Database indexing highly depends on the index fragmentation.
Index fragmentation refers to the condition where the logical order of pages in an index does not match the physical order on disk. This can lead to slower performance, especially for large indexes or queries that scan ranges of data.
📊 How Fragmentation Affects Performance
- More disk I/O to read index pages.
- Slower range scans and seek operations.
- Poor cache utilization (because of wasted space).
- Especially noticeable on large tables and during backups/rebuilds.
In our environment we have set up database indexing to overcome this index fragmentation.
FRAGMENTATION REPORT:
We have set up an SQL job to retrieve the fragmentation report of each index for our SCL DB.
Job: check_fragmentation_SCL
Frequency: Every 2 hours
This job utilizes system DMV ‘sys.dm_db_index_physical_stats’ to provide the index fragmentation status.
Sample job code:
SELECT
SCHEMA_NAME(o.schema_id),
o.name,
i.name,
ps.avg_fragmentation_in_percent,
ps.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ”LIMITED”) ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
JOIN sys.objects o ON o.object_id = i.object_id
WHERE i.index_id > 0
AND ps.avg_fragmentation_in_percent > 30
AND o.type = ”U”’; — Only user tables
FRAGMENTATION REPORT EMAIL:
To overcome this fragmentation, we have an SQL job with the following details:
Job: index_rebuild_SCL
Job Frequency: Each Sunday at 5AM
Job History:
This job works on the code below:
BEGIN
SET NOCOUNT ON;
DECLARE @TableName NVARCHAR(255), @IndexName NVARCHAR(255), @SQL NVARCHAR(MAX);
DECLARE cur CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(ix.object_id) + ‘.’ + OBJECT_NAME(ix.object_id) AS TableName,
ix.name AS IndexName
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’) AS stats
JOIN
sys.indexes AS ix ON stats.object_id = ix.object_id AND stats.index_id = ix.index_id
WHERE
stats.avg_fragmentation_in_percent > 30
AND ix.index_id > 0;
OPEN cur;
FETCH NEXT FROM cur INTO @TableName, @IndexName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘ALTER INDEX [‘ + @IndexName + ‘] ON ‘ + @TableName + ‘ REBUILD’;
EXEC sp_executesql @SQL;
FETCH NEXT FROM cur INTO @TableName, @IndexName;
END
CLOSE cur;
DEALLOCATE cur;
END
So, we are rebuilding indexes each Sunday at 5AM.
Additionally, to improve the performance during the weekdays production hours, we utilize our fragmentation report to check if any of the indexes are crossing the threshold mark i.e. 50% in our case.
When we do see such indexes fragmentated: