Understanding DBCC (Database Console Commands) is a step toward becoming a better and well-rounded DBA for Teamcenter. In my last post, I covered the basics of DBCC DBREINDEX, a command that rebuilds your indexes to remove fragmentation. In this post, I will cover the DBCC INDEXDEFRAG command, which helps you fix your page fragmentation issues without completely locking your database.
DBCC DBREINDEX (discussed in my last post) completely locks the table when it rebuilds the table's clustered index. This means that while it is rebuilding, users cannot query the database, update rows, delete, or perform any other operations. If the table is big and storage is slow, running DBCC DBREINDEX can lock the table from several minutes to hours, making this command not ideal for production databases.
The simplest way to call DBCC INDEXDEFRAG looks like this:
DBCC INDEXDEFRAG (Kulshan, Place, PK_Place)
DBCC INDEXDEFRAG defragments index pages. However, it goes about the process differently, performing an in-place update of the pages to defragment them. This shuffling does not require locking the table for the entirety of the process, which allows users to access the table. One way this is accomplished is that DBCC INDEXDEFRAG doesn't defragment pages that are in use. Therefore, it is possible to have some page fragmentation after DBCC INDEXDEFRAG runs.
DBCC INDEXDEFRAG performs a series of short transactions to defragment the index, unlike DBCC DBREINDEX which executes in one complete transaction. With DBCC INDEXDEFRAG, these short transactions can produce large transaction log files. One technique to deal with large transaction log files is to decrease the backup interval for transaction backups while DBCC INDEXDEFRAG is running. This will keep the transaction log files from growing too large.
DBCC INDEXDEFRAG only defragments the pages within the extents; it does not defragment the extents. If you have high extent fragmentation, you need to run DBCC DBREINDEX. See the blog post about DBCC SHOWCONTIG to determine your level of extent fragmentation. DBCC INDEXDEFRAG can make extent switching worse (the extent switching statistic is explained in the blog post about DBCC SHOWCONTIG). This is because a page order scan, which might be in order after DBCC INDEXDEFRAG, might switch between more extents.
Let's look at a sample table before defragmentation. Here is the output from DBCC SHOWCONTIG before DBCC INDEXDEFRAG.
DBCC SHOWCONTIG scanning 'Place' table... Table: 'Place' (1029279122); index ID: 1, database ID: 11 TABLE level scan performed. - Pages Scanned................................: 62 - Extents Scanned..............................: 10 - Extent Switches..............................: 14 - Avg. Pages per Extent........................: 6.2 - Scan Density [Best Count:Actual Count].......: 53.33% [8:15] - Logical Scan Fragmentation ..................: 98.39% - Extent Scan Fragmentation ...................: 60.00% - Avg. Bytes Free per Page.....................: 865.7 - Avg. Page Density (full).....................: 89.30%
And here is the output from DBCC SHOWCONTIG after DBCC INDEXDEFRAG on the clustered index.
DBCC SHOWCONTIG scanning 'Place' table... Table: 'Place' (1029279122); index ID: 1, database ID: 11 TABLE level scan performed. - Pages Scanned................................: 61 - Extents Scanned..............................: 10 - Extent Switches..............................: 10 - Avg. Pages per Extent........................: 6.1 - Scan Density [Best Count:Actual Count].......: 72.73% [8:11] - Logical Scan Fragmentation ..................: 13.11% - Extent Scan Fragmentation ...................: 60.00% - Avg. Bytes Free per Page.....................: 747.2 - Avg. Page Density (full).....................: 90.77%
Notice that in these example DBCC SHOWCONTIG outputs, the logical scan fragmentation is reduced; However, the extent scan fragmentation remains the same.
DBCC commands provide enormous benefits to the well-informed DBA. I hope that you have found this series of posts about the DBCC commands informative.
Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic Web sites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com