Table Indexes are a fundamental pillar of optimizing SQL queries in a database. They significantly speed up read operations. Unfortunately, over time, indexes get fragmented, and the order of indexes deteriorates. Index fragmentation occurs because records are gradually inserted or deleted from a table, and indexes are not optimized afterward. This means that individual index pages have a lot of free space, and when querying the table, we need to scan more pages than we would if the fragmentation were in order.

Microsoft’s Recommendations for Index Fragmentation

For index maintenance, Microsoft recommends these fragmentation percentages:

  1. If it is between 5 and 30%, you should reorganize the index (REOGRANIZE).
  2. If it is greater than 30%, then index rebuilding (REBUILD) is performed.

How to Check SQL Index Fragmentation

Fragmentation can be determined in 2 ways:

  • Through the properties of a specific index or, even better,

Fragmentation of Indexes - Example

  • Using a script (see below).

Index Fragmentation through a Script

Below is a script to find fragmented indexes, i.e., indexes with fragmentation greater than 5%. You need to run the script on the database where you want to check for fragmentation. The SQL query also contains recommendations on what to do with the index (REORGANIZE vs. REBUILD).

SELECT AS DB_Schema, AS DB_Table, AS DB_Index,
     stat.avg_fragmentation_in_percent AS INDEX_Fragmentation,
         WHEN avg_fragmentation_in_percent between 5 and 30 THEN 'REORGANIZE'
         WHEN avg_fragmentation_in_percent >30 THEN 'REBUILD'
     END AS OperationToMaintenance
     sys.objects obj
     LEFT JOIN sys.schemas sch
        ON obj.schema_id= sch.schema_id
     LEFT JOIN sys.indexes ind
        ON obj.object_id=ind.object_id
     LEFT JOIN sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'LIMITED') AS stat
        ON ind.object_id=stat.object_id
        AND ind.index_id=stat.index_id
     AND ind.index_id > 0
     AND avg_fragmentation_in_percent>5

Index Maintenance – SQL Syntax

  • Index Reorganization: ALTER INDEX <index_name> ON <table_name> REORGANIZE;
  • Index Rebuilding: ALTER INDEX <index_name> ON <table_name> REBUILD;

In the next article, I will show how to automatically repair indexes through an SQL script. You can find the article here – Automatic Index fix.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *