Solving SQL Server index fragmentation issues

Index fragmentation in SQL Server refers to data fragmentation within a database. Data stored in various indexes and tables in a Structured Query Language (SQL) database can become fragmented if it isn’t stored contiguously on disk.

When data is fragmented, the database server must perform more disk I/O operations to fetch the required data, which decreases the database’s performance. Additionally, if the data being indexed is frequently updated or inserted, the index itself may become fragmented, leading to lower performance and wasted storage space.

This article explores index fragmentation in SQL Server, the issues it creates, and best practices for identifying as well as remediating index fragmentation issues.

Resolving SQL Server index fragmentation issues

This section outlines different causes of index fragmentation and strategies for identifying them. It then reviews how you can proactively mitigate SQL Server index fragmentation.

What causes index fragmentation?

Index fragmentation in SQL Server occurs when the index’s logical order mismatches with its physical order. This can occur when:

• Inserting or updating rows: When you add a new or additional row to the database table, it may appear in a different location than where it would fit in the “logical order” of the SQL Server index. Similarly, updating a row that causes it to move to a different location in the index can cause fragmentation.
• Deleting rows: Deleting a row from a table leaves a gap in the SQL Server index. Fragmentation may occur if you don’t fill this gap with new inserts.
• Resizing the data pages: Changing the size of the data pages comprising the SQL Server index changes can cause fragmentation.
• Reorganizing or rebuilding the index: Reorganizing or rebuilding an SQL Server index can cause fragmentation if done incorrectly or infrequently.

Index fragmentation is split into two subcategories: internal and external. Both are detailed below.

Internal fragmentation

Internal fragmentation refers to wasted storage space within a single allocated block of memory. For example, if you allocate a block of memory to hold a file that is 500 bytes, but the block is 1,024 bytes, then there’s an internal fragmentation of 512 bytes. This fragmentation occurs because only 500 bytes are used, while the remaining 512 bytes are wasted because they can’t be used for anything else.

External fragmentation

External fragmentation refers to wasted storage space when many small blocks of free memory are scattered throughout the storage area. It can occur when you frequently allocate and free memory blocks, which may not be contiguous. This means there may not be a single block of free memory large enough to hold a particular file, even though there’s enough free space overall.

Identifying and measuring SQL Server index fragmentation

This section explores two ways to identify and measure instances of SQL server index fragmentation.

Using database monitoring tools

For example, Site 24x7’s SQL Server monitoring tool helps you with capacity planning, performing SQL Server operations, reviewing database details, and tracking SQL Server availability. It also provides logs and allows you to analyze them to identify inefficient queries.

Using T-SQL’s sys.dm_db_index_physical_stats()

You can use Transact-SQL, called T-SQL, to identify the index fragmentation. T-SQL is Microsoft’s extension of SQL that is specific to its SQL Server, and it contains procedural programming and local variables.

T-SQL uses the sys.dm_db_index_physical_stats() function to identify index fragmentation. This function allows you to view the size of the data fragmentation and the table indexes.

The query below, when written in SQL Server Management Studio, can help compute the fragmentation and page density of rowstore indexes. Each NULL value corresponds to Database ID, Object ID, Index ID, Partition Number, and the mode. The query computes the logical fragmentation of indexes for each object in the database as avg_fragmentation_in_percent. It can also compute other metrics, such as the percentage use of internally fragmented pages. This information is output in the column called avg_page_space-used_in_percent. It can also find the total number of pages and describe the allocation unit type:

SELECT * 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)  
WHERE avg_fragmentation_in_percent > 0 
ORDER BY avg_fragmentation_in_percent DESC; 

You can pass the modes as arguments to the sys.dm_db_index_physical_stats functions. Modes determine the level of detail you get after running the command. You can use SAMPLED mode to return results quickly or DETAILED mode for more accurate results, as it scans all index pages and may take a while to return results.

This query also works for rowstore data, which is saved row-wise in memory and consists of a table organized with rows and columns. It’s the traditional way of storing table data.

The output will be:

You can use the following query to select specific columns or find the fragmentation in a specific database by inputting their IDs. If you want to find the fragmentation for a specific database, include the database ID in the query:

DECLARE @DBID int; 
SET @DBID = DB_ID('SampleDatabase’) 

SELECT * 
FROM sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL)  
WHERE avg_fragmentation_in_percent > 0 
ORDER BY avg_fragmentation_in_percent DESC

The output will be:

Note that the output corresponds to fragmentation in only one database.

Best practices for fixing index fragmentation

To prevent or fix index fragmentation in SQL Server, it’s essential to reorganize or rebuild the index periodically. You should also consider the design of the index, including the fill factor and the choice of data types and sizes for the indexed columns.

Listed below are some best practices for fixing index fragmentation.

Rebuild the index

Depending on the type of indexes and the Database Engine version, you can execute a rebuild either offline or online with the ALTER INDEX NAME_OF_INDEX ON NAME_OF_TABLE REBUILD command. Rebuilding an offline index often takes less time than an online index but it locks objects at the object level, preventing queries from accessing the table or views while the rebuild is in progress.

Until the end of the process, when a lock is held briefly to finish the rebuild, an online index rebuild doesn’t require object-level locks. Depending on the Database Engine version, you can initiate an online index rebuild as a resumable procedure. You can also put a resumable index rebuild on hold to maintain its current state.

Use the sys.dm_db_index_phyical_stats() command to calculate the fragmentation of your index. If it’s greater than 40%, you should rebuild the index.

You should initiate rebuilding only after most of the table is fragmented. Although this method is resource-intensive, it addresses all forms of fragmentation and produces an optimized tree. The operation works by creating a new copy of the index and dropping the old one, removing the fragmentation.

Reorganize the index

Reorganize operates exclusively online with the ALTER INDEX NAME_OF_INDEX ON NAME_OF_TABLE REORGANIZE command. You can begin this operation while you’re processing queries or modifications to the underlying database because you aren’t maintaining a long-term object-level lock.

Reorganizing is less resource intensive than rebuilding and is used for minor or moderate fragmentation. Reorganizing the database cleans up by physically reorganizing the leaf nodes of the index, removing logical fragmentation. However, it can’t fix extent fragmentation, which is fragmentation in the physical location within SQL Server files.

Use the sys.dm_db_index_physical_stats() command to check the fragmentation of your index. If it’s greater than 10% but less than 40%, which entails a moderate level of fragmentation, you should reorganize the index.

You can use a cleanup operation such as REORGANIZE for reorganization without locking affected tables. Rebuilding an SQL index requires more resources than reorganizing one. As a result, unless there’s a unique need to employ index rebuild, it’s the preferred method of index maintenance.

When deciding whether to reorganize or rebuild the indexes, remember that a rebuild will always give you a better result, but it uses more resources. Rebuild when the fragmentation is high, and reorganize when the fragmentation is low.

Best practices for maintaining your SQL Server index

Instead of addressing SQL Server index fragmentation issues once they arise, you should try to prevent them from occurring. Follow these best practices to help minimize the impact of SQL index fragmentation.

Use a maintenance plan

A maintenance plan is a set of tasks that you can schedule to run automatically to maintain the database. This plan can include tasks to rebuild or reorganize indexes, update statistics, and check the integrity of the database.

Use the UPDATE STATISTICS command

The UPDATE STATISTICS command updates the distribution statistics that the SQL Server Query Optimizer uses to create execution plans. Updating the statistics can help the Query Optimizer choose a more efficient execution plan (EEP), improving query performance.

Choose the correct fill-factor value

The fill factor specifies the percentage of space on each index page that should be left empty to allow for future growth. If the fill-factor value is too low, the index may quickly become fragmented. But if it’s too high, there may be wasted space on the index pages.

Use the ONLINE option

If you need to rebuild the index, use the ONLINE option so the database is still available to users. This minimizes downtime and database disruption.

Regularly monitor index fragmentation

You should monitor for index fragmentation by running a fragmentation analysis regularly to identify and solve any fragmented indexes.

Conclusion

SQL Server databases can become fragmented due to regular use, causing performance issues and wasted resources.

Following the best practices outlined in this article will help you identify, measure, and mitigate fragmentation in your SQL Server. Moreover, monitoring and maintaining your indexes will prevent fragmentation. Run fragmentation analysis regularly and take proactive steps to fix any fragmented indexes to ensure optimal database performance.