DBCC SHOWCONTIG Blocking Issues in SQL Server

By:   |   Updated: 2007-03-09   |   Comments   |   Related: More > Database Console Commands DBCCs

With most tasks that access database level objects there is always some type of locking that occurs that causes potential blocking situations.  Unfortunately even processes such as DBCC SHOWCONTIG, which allows you to get table fragmentation information, also uses locks that could cause blocking issues. With SQL Server 2005 these issues have been resolved, but this is still an issue with SQL Server 2000.  So how do you get around this issue?

SQL Server 2000

The default behavior for DBCC SHOWCONTIG is to use a Shared (S) lock.  Here is the definition of this lock:

  • Shared (S) locks allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

Because of this shared lock it is possible for a DBCC SHOWCONTIG to lock the resource and therefore creating blocking issues for other transactions that are trying to INSERT, DELETE or UPDATE data.

Since this is the default behavior of DBCC SHOWCONTIG, what can be done to resolve this issue?  In this TechNet article DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000 the authors discuss this behavior as well as ways to overcome locking issues when running DBCC SHOWCONTIG on very large tables. Basically there are three things the article states that need to be done to overcome this issue in SQL Server 2000.

  1. Specify the table name in the command
  2. The table has to have a clustered index (indexId = 1)
  3. You must use the WITH FAST option

So based on this, if we want to get fragmentation information on table OrderDetails the command would look like this:


SQL Server 2005

In SQL Server 2005 this issue has been corrected, so you can run DBCC SHOWCONTIG without causing blocking and locking issues. In addition, SQL Server 2005 also offers the new dynamic management view (DMV) sys.dm_db_index_physical_stats.  This was discussed in a previous tip SQL Server 2000 to 2005 Crosswalk - Database Fragmentation.

To See Differences

To check out the differences and the impact of this command, if you run this simple test both in SQL Server 2000 and SQL Server 2005 you can see how this has been greatly improved.

  1. Open a Query window
    1. Begin a transaction
    2. Update a record in a table, but don't commit the transaction
  2. Open a new Query window

In SQL Server 2000, you will see that the DBCC command gets blocked from the first transaction that is running.  Until you commit or rollback the transaction the process will continue to be blocked. In SQL Server 2005, the DBCC operation will complete regardless if the the first transaction is committed or rolled back.  So just from this little test you can see the improvement that was made in how this command executes.

Next Steps

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

Article Last Updated: 2007-03-09

Comments For This Article


get free sql tips
agree to terms