DBCC SHOWCONTIG Blocking Issues in SQL Server
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.
- Specify the table name in the command
- The table has to have a clustered index (indexId = 1)
- 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
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.
- Open a Query window
- Begin a transaction
- Update a record in a table, but don't commit the transaction
- Open a new Query window
- 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.
- 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
Last Updated: 2007-03-09
About the author
View all my tips