SQL Server 2008 : Index maintenance

4/27/2013 12:59:52 AM

The maintenance actions outlined in this section are directly associated with the corresponding analysis activities from the previous section. We'll begin with dropping unused or duplicated indexes before looking at removing index fragmentation.

1. Dropping and disabling indexes

In the previous section, we covered the use of the sys.dm_db_index_usage_stats DMV along with a number of system tables to identify indexes that are not used, used rarely, or duplicated. Once identified, the indexes are candidates for removal. The question is, how should they be removed? An index can be easily dropped using the DROP INDEX command as per this example:

DROP INDEX tablename.indexname

If the index was dropped as a result of being identified as a duplicate or suspected as being of low value, then it's possible that certain unexpected errors or performance problems eventuate from the index being dropped. For example, a query may still be referencing the index with an index hint, or a rare (but important) report is executed that relies on the (now missing) index. In such cases, if the definition of the index was not saved, that is, the index type, columns, and column order, then it may take some time to restore the index to its previous state.

In SQL Server 2005, the ALTER INDEX command was enhanced with a DISABLE option. Disabling an index has the following effects:

  • If nonclustered, the data is removed from the index but the index metadata definition remains.

  • User access to the index is not permitted.

  • The query optimizer will not consider the index.

  • If a clustered index is disabled, access to the table is prevented until the index is re-enabled via either the REBUILD or the CREATE WITH DROP_EXISTING command, both of which are covered shortly.

When it comes to removing a duplicated or low-value index, disabling the index allows its definition to be retained should the index need to be reinstated, without the maintenance overhead of keeping it. An index can be disabled using the ALTER INDEX command as follows:

Disk space and disabling indexes

When you use the index REBUILD option (covered shortly), additional temporary disk space is required for storing the old and new copies of the index. One of the advantages of disabling an index before rebuilding it is the reduction in disk space required for the rebuild operation. The estimated overhead of rebuilding a disabled index is approximately 20 percent on top of the index size. The disadvantage of disabling an index is that it's unavailable for use until the rebuild is complete. This technique may be appropriate if disk space is limited and the temporary unavailability of the index is permitted.

-- Disable an index
ALTER INDEX ixSalesOrderHeader_PurchaseOrderNumber
ON Sales.SalesOrderHeader

The other major use for disabling indexes is during data warehouse loads. The classic load process is drop indexes, load data, reinstate indexes. The problem with this approach is that the definition of the indexes needs to be known when adding them back, complicating the process somewhat, particularly when the table or index definition changes and the change is not added to the drop/re-create process. In such cases, the index creation may fail or add back an old, poorly performing version of the index. By disabling an index rather than dropping it, the index definition is not required when re-enabling it, simplifying the re-creation process.

To re-enable a disabled index, use the ALTER INDEX REBUILD or CREATE INDEX WITH DROP EXISTING command, which we'll cover shortly.

2. Removing fragmentation

In this section, we'll cover a number of different techniques for maintaining an index and the implications these have on fragmentation, user access, statistics, and transaction log usage.


The ALTER INDEX REORGANIZE option is the replacement for DBCC INDEXDEFRAG used in earlier SQL Server versions. Reorganizing an index removes fragmentation by reordering the leaf level of clustered and nonclustered indexes by aligning the physical order with the logical order. An example follows:

-- Reorganize (Defragment) an index
ALTER INDEX IX_SalesOrderHeader_SalesPersonID
ON Sales.SalesOrderHeader

The REORGANIZE method is ideal as a low-impact, online method for removing light to medium levels of fragmentation from an index. In most cases, an index with a 5- to30-percent fragmentation level (as reported by the avg_fragmentation_in_percent column in the sys.dm_index_physical_stats function) can be defragmented using this method, without any major user impact.

On the downside, REORGANIZE will not remove fragmentation from the nonleaf (intermediate) levels, nor will it update statistics. For that, we need a more thorough approach, available through ALTER INDEX REBUILD.


ALTER INDEX REBUILD is the replacement for DBCC DBREINDEX used in earlier versions of SQL Server. This command essentially drops and re-creates the index, with a number of important differences.

First, the REBUILD method reads and rebuilds every page, and consequently, its statistics, are updated in full. Second, all levels of the index are rebuilt, meaning a greater performance boost and disk space reduction compared to the REORGANIZE method. An example REBUILD command follows:

-- Rebuild an index (Online mode)
ON Sales.SalesOrderHeader

You'll note a few differences (and similarities) between this command and REORGANIZE. First, it uses the same ALTER INDEX command, but in this case ALL is used in place of a specific index in the earlier example. ALL is supported for both REBUILD and REORGANIZE as a means to operate on all of the specified table indexes rather than a specific index.

The other option specified in the above example is ONLINE = ON. Available in the Enterprise edition of SQL Server, this option rebuilds the index in a manner that allows continued user access to the index during the rebuild operation. Thus, it's ideal for rebuilding indexes in 24/7 environments.

Drop/re-create vs. REBUILD

While REBUILD will essentially achieve the same thing as dropping and re-creating an index, it offers a number of important advantages. First, the ONLINE option can be used, which allows continued user access. Second, dropping and re-creating a clustered index will cause all nonclustered indexes to be rebuilt twice; the clustered index key is included in the leaf level of each nonclustered index. When the clustered index is dropped, the table is converted to a heap, with the nonclustered indexes changing their leaf-level row locators to a row ID containing the file, page, and row number. When the clustered index is re-created, the row ID changes back to the clustered index key. When the REBUILD option is used, all of this is avoided, with the clustered index key remaining unchanged throughout.

Table 1 summarizes the appropriate index-maintenance technique based on the fragmentation level. Although indexes of any fragmentation level will benefit from the REBUILD method, in order to limit the transaction log usage (and subsequent effects on database mirroring and/or log shipping), it's recommended that only indexes with greater than 30 percent fragmentation use REBUILD, with tables having fragmentation levels lower than this value using REORGANIZE. The 30 percent level is a general recommendation only and is subject to the local nuances of the database and its environment. The overall goal is to strike a balance between transaction log size, user impact, and increased index performance.

Table 1. Fragmentation levels and options
Index fragmentationMaintenance technique



The CREATE WITH DROP_EXISTING command, an example of which follows, allows the index definition to be modified as part of the re-creation process, for example, adding additional columns or moving the index to another filegroup.

-- Rebuild an index with an altered definition
ON dbo.client(SSN, DOB)

Using CREATE WITH DROP_EXISTING is particularly useful for rebuilding clustered indexes on tables with one or more nonclustered indexes. Unlike a traditional drop/re-create, which rebuilds the nonclustered indexes twice, nonclustered indexes are rebuilt only once using this option—or not at all if the index definition remains the same. On the downside, CREATE WITH DROP EXISTING operates on a single index at a time, in contrast to the ALL option of the REBUILD/REORGANIZE commands covered earlier.

Each of the index-maintenance techniques we've covered thus far can be executed with a large number of optional parameters. Before we close this section, let's cover some frequently used ones.

Index options

Let's recap these options and cover some other commonly used ones

  • FILLFACTOR—When building an index, FILLFACTOR determines how full (as a percentage) each index page will be for existing data. By default, FILLFACTOR is 0 (which is equivalent to 100), which means index pages will fill to capacity. This has two implications: with a higher page fill rate, fewer pages need to be read, but on the downside, a high update rate will result in more page splits and fragmentation. For tables with a high update rate, a lower fill factor may increase performance and reduce fragmentation, although the trade-off is that the index (and therefore the database) will occupy more space because each page is less than 100 percent full.

  • PAD_INDEX—FILLFACTOR applies at the leaf level of index pages only. When the PAD_INDEX option is set to ON, the FILLFACTOR setting is applied to intermediate index levels as well.

  • MAXDOP—This option allows the number of CPUs to be limited to (or increased from) the default server setting; for example, if the default server MAXDOP setting is set to 1 to prevent parallel queries, MAXDOP = 0 can be specified to maximize the performance of the index operation.

  • SORT_IN_TEMPDB—By default, the temporary disk space used during index rebuild operations comes from the user database containing the index. This option directs the operation to use the tempdb database, which may improve rebuild performance, particularly when tempdb is located on dedicated disks. When using this option, take care to ensure tempdb has sufficient disk space available.

  • DATA_COMPRESSION— individual tables and indexes can be compressed, using either the ROW or PAGE method.

An example of using these options as part of an index rebuild follows:

-- Rebuild an index with various custom options
ALTER INDEX IX_SalesOrderHeader_CustomerID
ON Sales.SalesOrderHeader
   , MAXDOP = 0
  •  Boot Drive Migration
  •  WD My Passport Edge 500GB – The Smallest One Yet
  •  OCZ Vector 512GB - Jumbo-capacity SSDs
  •  Corsair Force Series GS 240GB - Blessed With Toggle-Mode NAND
  •  SQL Server : Reusing T-SQL Code - How Reusing Code Improves its Robustness
  •  SQL Server : Reusing T-SQL Code - The Dangers of Copy-and-Paste
  •  SQL Server 2005 Native XML Web Services : Example Native XML Web Services Project (part 3) - Creating the Client Application
  •  SQL Server 2005 Native XML Web Services : Example Native XML Web Services Project (part 2) - Exposing the Endpoints
  •  SQL Server 2005 Native XML Web Services : Example Native XML Web Services Project (part 1) - Creating the SQL Server Functionality
  •  SQL Server 2005 Native XML Web Services : Exposing SQL Programmability as Web Services (part 2) - Calling Native XML Web Service Endpoints from Client Applications
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone