Understanding DBCC SHOWCONTIG and DBCC INDEXDEFRAG


                                                                    

 In  SQL Server, 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: Indexed Sequential Access Method (ISAM) 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.

___________________________________________________________________________________________

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
    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.

 
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.

 

********************************************************************************************************

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: