Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

DBCC SHOWCONTIG Blocking Issues in SQL Server


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

Problem
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?

Solution
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:

DBCC SHOWCONTIG (OrderDetails, 1) WITH FAST
GO

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
    1. Run DBCC SHOWCONTIG

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



Last Updated: 2007-03-09


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
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools