SQL Server Index Maintenance

Overview

We talked about the various types of indexes you can create in SQL Server and in this section, we look at things you need to do to maintain your indexes.

What is fragmentation

Fragmentation can actually take on two different forms when it comes to indexes. As the name suggests, fragmentation simply means that the 8KB page segments that are used to store data are either scattered in some non-sequential order on disk or the data within the 8KB page itself is scattered and sparsely populated within each individual page. The former is often called logical (external) fragmentation and the latter is usually referred to as internal fragmentation.

Logical (external) fragmentation

When an index is initially built all of the 8KB pages that make up the index are in a contiguous order. As data is inserted, updated and deleted, new pages need to be added (either due to a page split or simply more space is needed) and this causes these new 8KB pages to be placed elsewhere on disk. This leads to more random disk IO which is slower than sequential IO and can also make SQL Server's read-ahead feature much less efficient.

Internal fragmentation

This type of fragmentation also occurs because of DML statements being executed against the table but in this case the fragmentation is related to the amount of empty space that exists on a given page. As data is inserted, updated and deleted space within the pages can become unused. More empty space on pages requires more disk IO when data need to be read.

How can I find out if my indexes are fragmented?

Luckily for us since SQL Server 2008 getting information on the fragmentation levels of your index has been much easier than it what is previous versions. For older versions we had to use DBCC SHOWCONTIG and we had to call this utility for each table we wanted to analyze. Now we can simply use the system DMV, sys.dm_db_index_physical_stats, and with one query get all the fragmentation information we need for all our indexes, etc. The following columns in this view give you the percent amount of fragmentation for each type of fragmentation for all the indexes in your database. You can then use this information to decide on whether or not an index requires some sort of maintenance to reduce the fragmentation.

sys.dm_db_index_physical_stats

avg_fragmentation_in_percent -> Logical fragmentation
avg_page_space_used_in_percent -> Internal fragmentation

When to rebuild indexes?

When/how often index maintenance should be performed is a question many DBA's try to answer. Performing an index rebuild or reorg when it's not necessary (eg. no performance impact on queries based on current level of fragmentation) can be a waste of resources and actually contribute to poor performance since you end up doing a lot of unnecessary IO. A good rule of thumb for when a reorganize or when a rebuild should be run is below but, as I mentioned above, if your queries aren't experiencing any slowness then it might not be necessary and you could maybe wait until there are higher levels of fragmentation before doing any index maintenance.

Reorganize when fragmentation > 5-10%
Rebuild when fragmentation > 30%

How to rebuild indexes?

Once you determine that your index does in fact require maintenance then they can be reorganized or rebuilt using the syntax below. Note that index reorgs are always executed as an online operation whereas index rebuilds can be done either offline or online (online is only available in Enterprise Edition).

-- Reorganize
  ALTER INDEX ALL ON #TableName# REORGANIZE;
  ALTER INDEX #IndexName# ON #TableName# REORGANIZE;
-- Rebuild
  ALTER INDEX ALL ON #TableName# REBUILD [WITH (ONLINE=ON)];
  ALTER INDEX #IndexName# ON #TableName# REBUILD [WITH (ONLINE=ON)];

<< Previous

Next >>

Comments For This Article

Thursday, January 2, 2020 - 10:31:27 AM - Jeff Moden Back To Top (83600)

MS makes REORGANIZE sound like the best thing since round wheels. It turns out, it's not the benign kitten they make it out to be. It crushes the transaction log file (especially on larger indexes) even compared to doing a REBUILD in the FULL RECOVERY model. If you can slip into the BULK LOGGED recovery model, REBUILD runs a good 10 times faster and is truly minimally logged.

Also, I know how the "starter" recommendations of REORGANIZEing between 5-10% and REBUILDing at 30% got started but it should be labeled as an absoute worst practice. It turns out that the REBUILD part of it waits too long and allows a huge amount of damage in the form of page splits and the ensuing fragmentation they cause. The REORGANIZE part of it should actually be removed, even on the Standard Edition of SQL Server becuase it removes free space from the critical area between the Fill Factor if one less than 0/100 is assigned and the 100% page fullness at the most critical time where REBUILD would clear that area out.

MS (or I should say, Paul Randal, because he wrote that part of BOL) DOES provide a warning near their recommendation on the very link you provide in your previous note. To be clear, here's the exact quote from the tip highlighted in Green in that article... and almost EVERYONE ignores it and insist that the 5-30% recommendations are the best practice.

These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment. For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. The performance benefit is less noticeable for indexes that are used primarily for seek operations. Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.

I have several SQLSaturday presentations where I prove the fallacy of using the current "Best Practices" for index maintenance and demonstrate (with code and a method not previously shown by anyone in the world) just how bad REORGANIZE is for your indexes. For example, I demonstrate how inserting 1,000 rows per simulated hour for 10 hours per simulated day for a simulated year can be done on a random GUID keyed 123 byte Clustered Index (includes the row header space used) with absolutely ZERO pages splits (not even supposed "good" ones, which are still "bad") and absolutely no fragmentation literally for months at a time. I also demonstrate how REORGANIZE perpetuates page splits and the ensuing fragmentation every day, which is the actual totally hidden reason why people recommend not using GUIDs for index keys.

Tuesday, November 20, 2018 - 6:38:36 AM - ben snaidero

@Philip van Gass The following link explains the difference between a rebuild and a reorg. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017. Here is another reference - https://www.mssqltips.com/sqlservertip/4470/script-to-manage-sql-server-rebuilds-and-reorganize-for-index-fragmentation/.

Thanks for reading.

Sunday, November 18, 2018 - 9:56:04 AM - Philip van Gass	Back To Top (78284)
What is the difference between a reorganization and a rebuild of indexes?