Defragmented Indexes in a #DWH on #MSSQL

Reading a book on Microsoft SQL Server administration, I came upon a topic that I didn’t cared about so far, mainly because I’m usually not responsible for administrating the data warehouse.

Database indexes in Microsoft SQL Server are stored using a B-tree structure:

As you can see from the graphic, there is a root node, multiple intermediate level nodes and on the bottom, so called leaf nodes.

As long as the index is being increased (that means as long as records are added to the underlying table), no problem occurs. The only thing that can happen is that pages are split when the index page size becomes insufficient.

The problem of defragmented indexes occurs when records from the underlying table are being deleted. A delete requires an update to the index, because the record needs to be removed from the index.

For performance reasons, Microsoft SQL Server does not shrink the index, but leaves an empty “cell” in the index. It just removes the pointer to the deleted record. The more data is being deleted, the more gaps in the index occur. When an index is fragmented it takes SQL Server more time to scan through the index.

As a result, Microsoft recommends to defragment indexes on a weekly basis when the defragmentation level exceeds 30 %.

Determine the Fragmentation Level

To determine the fragmentation level of the database indexes, use the DBCC SHOWCONTIG command:

DBCC SHOWCONTIG

This command returns the fragmentation level of each index in the database.

Fix Fragmented Indexes

There are three options to defrag an index:

  1. DBCC INDEXDEFRAG
  2. DBCC DBREINDEX
  3. CREATE INDEX WITH DROP_EXISTING

DBCC INDEXDEFRAG

DBCC INDEXDEFRAG is an online operation (this means the index can be used even while fragmentation is being removed), and takes significantly less time than rebuilding an index. Unfortunately DBCC INDEXDEFRAG doesn’t always do as good of a job as dropping and recreating an index. This statement takes database name, table name and index name as parameters:

DBCC INDEXDEFRAG('pubs', 'authors', 'aunmind')

DBCC DBREINDEX

DBCC DBREINDEX rebuilds an index without dropping primary or unique constraints. This statement takes longer than DBCC INDEXDEFRAG, but it is guaranteed to remove fragmentation since it re-creates the index. Note that non-clustered indexes are NOT rebuilt when DBCC DBREINDEX is executed against the clustered index. DBCC DBREINDEX cannot change the index keys.

DBCC DBREINDEX doesn’t require specified index names; it can rebuild all indexes within a table or all indexes within the database if you omit the table name. We could rebuild all indexes on the authors table as follows:

DBCC DBREINDEX('authors')

CREATE INDEX WITH DROP_EXISTING

The third alternative is using the WITH DROP_EXISTING clause of the CREATE INDEX statement. This statement performs better than the DROP INDEX statement followed by CREATE INDEX; however you must provide the name of the existing index. If the index keys haven’t changed CREATE INDEX WITH DROP_EXISTING doesn’t have to sort data again (as would be the case if you were to drop and re-create the index). Notice also that if you rebuild a clustered index then all non-clustered indexes are rebuilt automatically to reflect new clustering keys. We could rebuild the ‘aunmind’ index on the authors table as follows:

CREATE INDEX aunmind ON authors(au_lname, au_fname) WITH DROP_EXISTING

One thought on “Defragmented Indexes in a #DWH on #MSSQL

  1. I think this is a typo: The problem of defragmented indexes occurs when records from the underlying table are being deleted… I think you meant fragmented rather than defragmented.

    Great article I have learned a lot from it.. Cheers

Leave a Reply