DBCC SHOWCONTIG Blocking Issues in SQL Server

By: Greg Robidoux | Read Comments | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

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

• If you have large tables where DBCC SHOWCONTIG has caused issues, try the above steps to still be able to get your fragmentation information
• Begin using the new DMV sys.dm_db_index_physical_stats to get fragmentation information for SQL Server 2005
• Take a look at this article, it is a short read but give some great insight DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000
• Take a look at the differences for getting fragmentation information between SQL Server 2000 and 2005 in this tip SQL Server 2000 to 2005 Crosswalk - Database Fragmentation

Related Tips: More | Become a paid author

Last Update: 3/9/2007

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! SQL Monitor – For database professionals who need results on Day One. Try it online. Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. Demystify TempDB Performance and Manageability