By: Greg Robidoux | Comments | Related: > 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
Below we will cover how this works in SQL Server 2000 and SQL Server 2005.
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 a TechNet article 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 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 Between SQL 2000 and SQL 2005
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.
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 the differences for getting fragmentation information between SQL Server 2000 and 2005 in this tip SQL Server 2000 to 2005 Crosswalk - Database Fragmentation
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
