What’s the Difference in Index Defrag and Rebuild?

If you’ve worked with SQL Server for very long, you’re probably familiar with the concept of indexes. Indexes can help speed up queries by allowing SQL Server to quickly locate specific data values within a table. Indexes are typically much smaller than their underlying tables so they can be searched much more efficiently. (They aren’t always more efficient, but that’s a topic for another post.) However, indexes can become fragmented over time, leading to extra work by SQL Server and reduced query performance. That’s where index defrags and index rebuilds come in.

But first, what is index fragmentation?

Index Fragmentation

Imagine your database as a library, and each book on the shelf represents a row of data. The index is like a handy card catalog system that helps you quickly find the books you need based on certain criteria, like the author’s name.

Now, when you add, update, or delete rows in your database, it can be like moving some of the books on the shelves. Over time, as the data changes, the pages that hold the index information might not be right next to each other anymore. This is what we call fragmentation.

Two types of fragmentation

There are two common types of fragmentation that can occur in a SQL Server index: internal fragmentation and external fragmentation.

1. Internal fragmentation: This type of fragmentation happens when there’s empty space on the data pages of an index. For example, if you have a bookshelf with some books that are small and others that are large, you might end up with empty spaces between them, or at the end of the shelf. In the same way, when you have rows of different sizes in your database, it can leave gaps on the data pages, causing internal fragmentation.
2. External fragmentation: External fragmentation occurs when the logical order of the index pages doesn’t match the physical order on the disk. Going back to our library analogy, let’s say you alphabetize your books by author’s name, but then you add a new book that starts with the letter “A.” You might have to put it into a different shelf because there’s no room in the “A” section. Logically it’s grouped with the other “A’s” but physically its on the other side of the library. Similarly, when new data is added or existing data is modified in your database, the index pages might have to be placed in different locations on the disk, leading to external fragmentation.

Both internal and external fragmentation can slow down the performance of your SQL Server queries. When the index becomes fragmented, it can take longer for the database engine to access the data you need, just like it would take you longer to find a book if they were scattered randomly on the shelf or in the library.

Detecting index fragmentation

How can you tell if an index is fragmented?

There are some Dynamic Management Views (DMVs) that can help

For row store indexes, the sys.dm_db_index_physical_stats DMV can be used to see the average fragmentation as a percent. It will also show the average page space used as a percent.

For columnstore indexes, you can use the sys.dm_db_column_store_row_group_physical_stats DMV to see the total rows and the deleted rows.

The Microsoft documentation has some examples of how to use each of these DMVs.

Removing index fragmentation

If fragmented indexes are hurting query performance, what can we do about it?

Regular index maintenance.

With index maintenance, we can reduce the internal and external fragmentation of the index. We can remove the extra space trapped in the index pages, and we can move related index pages physically closer to one another. We do this by either defragging the index or rebuilding it. Let’s look at each.

Index defrag

Index defrag is the process of reorganizing the physical structure of an index to remove fragmentation. This process does not involve rebuilding the entire index, but rather rearranging the data within the index to make it more compact and efficient.

Going back to our library analogy, this would be like rearranging the books on the shelves so that all of the “A’s” are together again. The same for the “B’s.” Etc.

Index defrag can be performed using the ALTER INDEX statement with the REORGANIZE option. This reorganizes individual indexes or all indexes on a particular table or database. The main advantage of index defrag is that it improves query performance without requiring much downtime or excessively disrupting ongoing operations . REORGANIZE is an online operation, meaning it doesn’t prevent queries or updates to the underlying table.

Index rebuild

On the other hand, index rebuilds involve completely rebuilding an index from scratch. This process involves creating a completely new index.

Index rebuilds are more resource-intensive than defragging and can require significant downtime. However, they can also tend to provide more drastic performance improvements, particularly for very large or heavily-used indexes.

In SQL Server 2019, index rebuilds can be performed using the ALTER INDEX statement with the REBUILD option. This statement can also be used to rebuild individual indexes or all indexes on a particular table or database. The main advantage of index rebuilds is that they can be used to further optimize index performance and improve query response times.

Rebuilds are typically considered an off-line operation, meaning that the table is not available to queries or updates while the index is being created. With Enterprise Edition, however, you can use the WITH(ONLINE=ON) option so that the existing index is available while a new one is being built.

When to defrag and when to rebuild?

Like may questions with SQL Server, it depends. It depends on your circumstances, your data, your hardware, and other factors. So there is no hard and fast rule for when to defrag with REORGANIZE and when to REBUILD.

The general consensus is if fragmentation is:

• Less than 10% – there is no need to do anything to the index.
• Between 10% and 30% – defragging the index with the REORGANIZE option is recommended.
• Greater than 30% – rebuilding the index with the REBUILD option is probably the best option.

For the SQL Servers we manage for our clients, we use Ola Hallengren’s SQL Server Index and Statistics Maintenance (hallengren.com). It’s proven to be a solid way to maintain indexes. We usually schedule index maintenance to run weekly during a period of lower activity, such as 1:00 AM Saturday morning.

Your mileage may vary though. You may only need index maintenance monthly, or you may be better off running it twice weekly. It depends.

For more information

In conclusion, index defrag and index rebuilds are both important tools for maintaining SQL Server indexes and improving query performance. Start with the general best practices and then refine as needed.

For more information, see the following.

• Indexes – SQL Server | Microsoft Learn
• Clustered and nonclustered indexes described – SQL Server | Microsoft Learn
• Maintaining indexes optimally to improve performance and reduce resource utilization – SQL Server | Microsoft Learn
• Do I Really Need SQL Server Enterprise Edition? – The SERO Group

Want to work with The SERO Group?

Index maintenance is not a magic potion that will automatically improve performance. It can help, and often does. But there are a lot of other factors that influence performance. If you’re having performance issues and would like some assistance, we’re happy to help.

 Contact us for more information about how we can help.

Want more information like this? You can also visit our LinkedIn page or sign up for a monthly newsletter.