By Kalman Toth, M.Phil., M.Phil., MCDBA

February 21, 2005

Indexing is not really part of the relational database architecture, yet it is the most important operational feature. Indexing was inherited from the pre-relational world: ISAM files or Indexed Sequential Access Method were the main data storage mechanism on IBM mainframes after the introduction of COBOL in the middle of 60-s. Currently DB2 relational databases are used on mainframes although ISAM never really went away on legacy systems.

Most operational problems on SQL servers revolve around indexes: what are the right indexes, how do we maintain them in top operating shape, etc.

To check the fragmentation of indexes in the entire database, execute:

Use DatabaseName
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

The result will be presented in a tabular format.

For individual table fragmentation, execute:

DBCC SHOWCONTIG (TableName) with all_indexes

In SQL Server 2005, execute:

USE AdventureWorks
GO
DBCC SHOWCONTIG ('HumanResources.Employee')
GO

The results will be in text format with labels:

DBCC SHOWCONTIG scanning 'TableName' table...
Table: 'TableName' (206334645); index ID: 7, database ID: 14
LEAF level scan performed.
- Pages Scanned................................: 62128
- Extents Scanned..............................: 7808
- Extent Switches..............................: 8098
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 95.89% [7766:8099]
- Logical Scan Fragmentation ..................: 17.39%
- Extent Scan Fragmentation ...................: 7.68%
- Avg. Bytes Free per Page.....................: 2412.0
- Avg. Page Density (full).....................: 70.20%

Index ID 1 refers to the clustered index on the table if any. To find out the index name, you have to look in the sysindexes table.

In SQL Server 2005:

select * from sys.indexes

The results above show moderate fragmentation. The page density is the result of indexing with a fill factor of 70. With time that deteriorates, indicating the need to reindex. If logical fragmentation is above 50%, you have to reindex to regain performance.

Alternative to reindexing is indexdefrag. To defrag a specific index, execute:

DBCC INDEXDEFRAG (DatabaseName, TableName, IndexName)

Defragging is on online operation. It will not lock up the table. On the other hand it will slow down the operation. It is a logged operation. So it competes with regular OLTP transactions for writing resources. Assume it takes 1 hour to defrag an index in the middle of the day. Should you do it? Probably not. If it takes only 5 minutes, you should.

Your only protection against quickly deteriorating indexes is the FILL FACTOR. During the night you should reindex with a fill factor of 70 in general. If performance issue persists, you can gradually go down to 40. Fill factor is limited help if the inserted data does not have good distribution. On the other hand, it works really great if the inserted data has a good distribution.