SQL Server Best Practices Article
Published: October 11, 2006
Writers: Sanjay Mishra, Stephen Jiang
Technical Reviewers: Mark Souza, Mike Ruthruff, Prem Mehra, Hermann Daeubler
Project Editor: Suzanne Bonney
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. For more information, please refer to the DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000 whitepaper.
