SQL Server Index Maintenance

By: Ben Snaidero

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)];