SQL Server Indexation—Check, Report, and Fix Index Fragmentation

What is index fragmentation?

Index fragmentation can occur throughout the lifetime of an index, typically as the data in an index gets modified. Whether these modifications are insertions, deletions, or updates, they can cause the index to become fragmented.

To understand index fragmentation, you need to first understand the index itself. Indexes are created on columns in views or tables. Their orderly structure is designed to make it easier for you to look up and locate the data you need based on the values of those columns. 

When index fragmentation occurs, data retrieval—which is critical to database functionality—can become significantly slower and less efficient. The performance degradation caused by index fragmentation can also seriously affect end users, whether they’re employees or customers.

A B-tree structure is used to store the indexes on the disk. Indexes are on-disk structures. They’re made up of keys pointing to either one of the multiple columns within the view or table. After you first create an index on a column, you can then search for the row you need based on the numbers in the index. The server conducts the search for you, first finding the necessary value in the index and then using the index itself to find the row data it’s looking for in the database. If you didn’t have an index, this process would be much slower, since the server would need to conduct a full table scan to locate the row data it needs. This would be a much slower process and require substantially more I/O.

What is fragmentation in SQL Server?

Most of the time when referring to index fragmentation, what people are referring to is fragmentation occurring in SQL Server.

SQL Server index fragmentation commonly involves two main types:

1. Logical Order Fragmentation/External Fragmentation: This form of SQL index fragmentation is the result of pages being out of order due to data being inserted, deleted, and modified over time. For example, it can happen due to an update or an insert operation making an existing record longer. If the index leaf page is full and more space is needed, SQL Server may perform a page split operation involving half the rows from the old page being put on a new page—a page likely not physically contiguous with the original page.
2. Page Density Fragmentation/Internal Fragmentation: While external fragmentation occurs when there isn’t enough space, internal fragmentation is the result of pages with too much free space. 

Why is monitoring for index fragmentation important?

Checking for and reducing index fragmentation can help you more efficiently tune indexes to improve SQL Server performance. Without insight into index fragmentation, users may experience database slowdowns. Other issues monitoring for index fragmentation can help inform include the following:

• Query slowdowns: Even minor fragmentation can accumulate over time, slowing queries and causing database bottlenecks.
• Missing insights: Without in-depth SQL Server index analysis, it’s hard to pinpoint the root causes of bottlenecks and high wait times.
• Limited tuning: When tuning relies on guesswork, it’s tempting to add hardware rather than resolve fragmentation issues, but this can lead to higher costs.

How can I optimize indexation in SQL Server?

Unfortunately, there isn’t a way to entirely avoid index fragmentation—but you can take steps to optimize indexation. One way to optimize indexation is to stay proactive in addressing SQL Server index fragmentation and other issues within your database.

There are two main ways to deal with SQL Server index fragmentation: rebuilding or reorganizing the index. The basics of each process are as follows:

1. Rebuild: In an index rebuild, you must drop the old index and reproduce it with new index pages. You can run it either offline or online, but while an index is being rebuilt, the index’s table won’t be accessible until the process is complete.
2. Reorganize: In an index reorganization, your index’s pages are reordered by expelling the unused or free space on each page.

Once either of these defragmentation processes is completed, the index should once again be contiguous, allowing for more efficient and faster access.

One of the ways you can more easily optimize your index is by using a tool like SolarWinds^® Database Performance Analyzer (DPA). DPA is built to constantly monitor your database, so you can more easily identify fragmented indexes. Additionally, DPA can alert you to anomalies and offers actionable advice on how to resolve problems, including tuning your indexes. 

How does the SQL Server index fragmentation monitoring and optimization tool work in DPA?

DPA is an index fragmentation tool designed to check the fragmentation of SQL Server and other databases.

As a database performance optimization solution, the tool helps with addressing SQL Server index fragmentation, query tuning, and other database optimization best practices. DPA gives you a powerful solution built to monitor performance issues within your index thanks to SQL index fragmentation reports, customizable alerts, and actionable advice to better inform your SQL Server index fragmentation query tuning efforts.

When it comes to addressing SQL Server index fragmentation, Database Performance Analyzer can help you do the following:

• Understand slowdowns: The Response Time Analysis feature in DPA helps you quickly gauge query performance and pinpoint fragmentation issues.
• Tune performance: DPA offers precise insights into SQL query performance to help you tune queries and indexes and improve end-user speed.
• Access table-tuning advice: The Table Tuning Advisor page in DPA displays fragmentation analysis and prioritizes inefficient queries, so you know where to direct your troubleshooting and optimization efforts. You can easily drill down into detailed data about the specific issue you’re trying to resolve right from the page and gain the information you need to resolve index fragmentation problems based on highest priority.
• Customize automatic alerts: DPA offers robust index fragmentation alerts capable of letting you choose database-specific thresholds to receive 24/7 fragmentation notifications.
• View dashboard and reports: Monitor SQL Server fragmentation through an intuitive dashboard and receive customizable reports. These SQL Server fragmentation reports can help you stay on top of historical data and current concerns with your index. These reports can also make it easier to proactively address capacity constraints and slowdowns resulting from index fragmentation.