DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000

By Sanjay Mishra, Stephen Jiang

Technical Reviewers: Mark Souza, Mike Ruthruff, Prem Mehra, Hermann Daeubler

Project Editor: Suzanne Bonney

Applies To: SQL Server 2005

DBCC SHOWCONTIG is used to display fragmentation information for the data and indexes of the specified table.

In SQL Server 2000, the following usages of DBCC SHOWCONTIG acquire an S lock on the table, thereby restricting DML (INSERT, UPDATE, DELETE) operations.

  • When the table is a heap

  • When the table has a clustered index, but the DBCC SHOWCONTIG call specifies only the table_name parameter (with or without the WITH FAST option).

  • When the table has a clustered index, and the DBCC SHOWCONTIG call specifies the table_name parameter and the index_name or the index_id parameter; but without the WITH FAST option.

On large tables, DBCC SHOWCONTIG can take a significant amount of time. Restricting DML operations during this time can be prohibitive for the application. You can work around this problem in SQL Server 2000, if the table has a clustered index.

This problem has been resolved in SQL Server 2005. In SQL Server 2005, all usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing concurrent DML operations.

Moreover, in SQL Server 2005, it is recommended to use the table valued dynamic management function sys.dm_db_index_physical_stats instead of the DBCC SHOWCONTIG command.

This document describes the problem and the workaround in SQL Server 2000, and the solution and recommendation in SQL Server 2005.

On This Page

Test Setup
Limitation in SQL Server 2000
Workaround in SQL Server 2000
Resolved in SQL Server 2005
Recommendation in SQL Server 2005
Summary

Test Setup

Execute the following steps to reproduce the scenario:

  1. Create a table:

    USE [TESTDB]
    GO
    CREATE TABLE [dbo].[test_contig](
    [SK_Store_ID] [smallint] NOT NULL,
    [SK_Item_ID] [int] NOT NULL,
    [SK_Date_ID] [int] NOT NULL,
    [SK_Buyer_ID] [int] NULL,
    [SK_Model_Strategy_ID] [smallint] NULL,
    [Model_QTY] [int] NULL,
    [On_Hand_Qty] [int] NULL,
    [On_Order_Qty] [int] NULL,
    [Return_Qty] [int] NULL,
    [Retail_Amt] [numeric](9, 2) NULL,
    [Days_In_Stock] [tinyint] NULL,
    [ETL_Load_ID] [int] NULL,
    [Last_Modified_Date] [datetime] NULL,
    [Modified_by_User] [varchar](30) NULL
    ) ON [PRIMARY]
    GO
    CREATE UNIQUE CLUSTERED INDEX ix_test_contig ON test_contig(sk_store_id, sk_item_id, sk_date_id)
    GO
    
  2. Load significant amount of data into this table. We loaded 30 million rows in this table. This ensured that we had enough time to execute DBCC SHOWCONTIG from one session, and then switch to another session to query the catalog tables for viewing the locks being held, while DBCC SHOWCONTIG is running.

  3. Execute the following from a connection to SQL Server 2000:

    DBCC SHOWCONTIG (test_contig)

Limitation in SQL Server 2000

DBCC SHOWCONTIG, as used in the test shown previously, acquires an S lock on the table. To view the lock that is being acquired, execute the following query from another session, while the DBCC SHOWCONTIG is running:

SELECT o.name, CASE l.rsc_type
WHEN 1 THEN 'NULL'
WHEN 2 THEN 'Database'
WHEN 3 THEN 'File'
 WHEN 4 THEN 'Index'
WHEN 5 THEN 'Table'
WHEN 6 THEN 'Page'
WHEN 7 THEN 'Key'
WHEN 8 THEN 'Extent'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'Application'
               END AS Resource_Type,
               CASE l.req_mode
                WHEN 0 THEN 'NULL'
WHEN 1 THEN 'Sch-S'
WHEN 2 THEN 'Sch-M'
WHEN 3 THEN 'S'
 WHEN 4 THEN 'U'
WHEN 5 THEN 'X'
WHEN 6 THEN 'IS'
WHEN 7 THEN 'IU'
WHEN 8 THEN 'IX'
WHEN 9 THEN 'SIU'
WHEN 10 THEN 'SIX'
WHEN 11 THEN 'UIX'
WHEN 12 THEN 'BU'
WHEN 13 THEN 'RangeS_S'
 WHEN 14 THEN 'RangeS_U'
WHEN 15 THEN 'RangeI_N'
WHEN 16 THEN 'RangeI_S'
WHEN 17 THEN 'RangeI_U'
WHEN 18 THEN 'RangeI_X'
WHEN 19 THEN 'RangeX_S'
WHEN 20 THEN 'RangeX_U'
                WHEN 21 THEN 'RangeX_X'
               END AS Lock_Type
FROM testdb.dbo.sysobjects o, master.dbo.syslockinfo l
WHERE l.rsc_objid = o.id
GO

The output will show the following:

Locks held in SQL Server 2000

Name

Resource_Type

Lock_Type

test_contig

Table

S

Because DBCC SHOWCONTIG holds the S lock on the table with SQL Server 2000, other transactions trying to insert, update or delete data in the table will be blocked. This puts limitations on when the DBCC SHOWCONTIG command can be run on a table. On large tables, this can take significant amount of time. For our example test scenario, DBCC SHOWCONTIG (test_contig) took approximately two minutes to complete; but on larger tables, it can be much longer. Therefore, it is not practical to execute DBCC SHOWCONTIG during normal transaction processing hours.

An S lock is held on the table during DBCC SHOWCONTIG during the following execution as well:

DBCC SHOWCONTIG (test_contig) WITH FAST

Note that we are still using SQL Server 2000

Using the WITH FAST option means that DBCC SHOWCONTIG will perform a fast scan of the index and output minimal information. A fast scan does not read the leaf or data level pages of the index. Even though the fast scan takes less time, it still acquires an S lock on the table.

Workaround in SQL Server 2000

As a workaround in SQL Server 2000, you can modify the DBCC SHOWCONTIG execution as follows:

DBCC SHOWCONTIG (test_contig, 1) WITH FAST 
GO

By specifying index_id =1 in the DBCC SHOWCONTIG call, you ask it to check fragmentation information for the clustered index. By specifying WITH FAST, you ask it to perform a fast scan of the index and output minimal information. A fast scan does not read the leaf or data level pages of the index.

This execution of DBCC SHOWCONTIG acquires IS on the table. Therefore, it does not block the concurrent inserts, updates or deletes on the table. If you query the catalog tables by using the script in the previous section while executing DBCC SHOWCONTIG as shown in the workaround, you will get the following output:

Locks held in SQL Server 2000 with index_id=1

Name

Resource_Type

Lock_Type

test_contig

Table

IS

Note that this workaround applies only if you have a clustered index on the table.

Resolved in SQL Server 2005

The blocking issue experienced in SQL Server 2000 is resolved in SQL Server 2005. With SQL Server 2005, all executions of DBCC SHOWCONTIG acquire an IS lock on the table. Therefore, they do not block the concurrent inserts, updates or deletes on the table. While DBCC SHOWCONTIG is running, execute the following query from another session to list the locks that are being held:

SELECT o.name, l.resource_type, l.request_mode
FROM sys.objects o, sys.dm_tran_locks l
WHERE l.resource_associated_entity_id = o.object_id
GO

The output will show the following:

Locks held in SQL Server 2005

Name

Resource_Type

Lock_Type

test_contig

Table

IS

Recommendation in SQL Server 2005

DBCC commands will be deprecated in a future version of SQL Server. Therefore, it is recommended that you use the function sys.dm_db_index_physical_stats. To check the fragmentation of a table, execute the following query:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('test_contig'), NULL, NULL , 'DETAILED')
GO

While this query is running, if you check the locks that are being held by using the query in the previous section, you will get the following output:

Locks held in SQL Server 2005

Name

Resource_Type

Lock_Type

test_contig

Table

IS

Summary

In SQL Server 2000, some usages of DBCC SHOWCONTIG acquire an S lock on the table, thereby restricting DML (INSERT, UPDATE, DELETE) operations. On large tables, DBCC SHOWCONTIG can take a significant amount of time. You can work around this problem in SQL Server 2000 if the table has a clustered index.

This problem has been resolved in SQL Server 2005. In SQL Server 2005, all usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing concurrent DML operations.

Moreover, in SQL Server 2005, it is recommended to use the table valued dynamic management function sys.dm_db_index_physical_stats instead of the DBCC SHOWCONTIG command.