Database index fragmentation occurs when the data stored in a database index becomes disordered or inefficiently organized, leading to decreased performance and increased storage requirements.
There are a few reasons why database index fragmentation can occur:
Insertion, Updates, and Deletions: When you insert, update, or delete records in a database, it can lead to index fragmentation.
Nonsequential Key Values: If the indexed columns have nonsequential vital values, the data may not be stored contiguously within the index pages.
Page Splits: When an index page becomes full and new data needs to be inserted, the database may need to split the page into two or more pages to accommodate the latest data.
Frequent Reorganizations: If you have a maintenance plan that regularly reorganizes or rebuilds indexes, this can sometimes contribute to fragmentation if not done optimally. Rebuilding indexes too frequently or with the wrong parameters can lead to fragmentation issues.
Poorly Designed Indexes: If your database schema includes poorly designed indexes, this can contribute to fragmentation.
Index fragmentation can lead to reduced query performance, increased storage requirements, and increased I/O operations. To address fragmentation, you can regularly monitor and maintain your indexes by rebuilding or reorganizing them, choosing appropriate indexing strategies, and optimizing your database schema. Database management systems often provide tools and commands to help you manage index fragmentation effectively.
Best Practices
Run this SQL on your database to get the index fragmentation percentages.
>10 to 30% Reorganize index
> 30% Rebuild index
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
For your top 20 largest pages (see column page_count) consider index fragmentation if the fragmentation_percentage is > 10%
In on of our projects response times improved by 30 to 50% after we've rebuilt the relevant database index.
As a performance engineer we must include all layer in our performance analysis and tuning considerations. Don't ignore the often overlooked database layer in your tuning considerations.
Keep up the great work! Happy Performance Engineering!