Fixing Table Fragmentation

Table and index fragmentation occurs on tables that have many insert, update, and delete activities. Because the table is being modified, pages begin to fill, causing page splits on clustered indexes. As pages split, the new pages might use disk space that is not contiguous, hurting performance because contiguous pages are a form of sequential input/output (I/O), which is faster than nonsequential I/O.

Before running Siebel EIM, it is important to defragment the tables by executing the DBCC DBREINDEX command on the table's clustered index. This applies especially to those indexes that will be used during Siebel EIM processing, which packs each data page with the fill factor amount of data (configured using the FILLFACTOR option) and reorders the information on contiguous data pages. You can also drop and recreate the index (without using the SORTED_DATA option). However, using the DBCC DBREINDEX command is recommended because it is faster than dropping and recreating the index, as shown in the following example:

DBCC SHOWCONTIG scanning '**S_GROUPIF' table...
Table: '**S_GROUPIF' (731969784); index ID: 1, database ID: 7
TABLE level scan performed.
Pages Scanned................................: 739
Extents Scanned..............................: 93
Extent Switches..............................: 92
Avg. Pages per Extent........................: 7.9
Scan Density [Best Count:Actual Count].......: 100.00% [93:93]
Logical Scan Fragmentation ..................: 0.00%
Extent Scan Fragmentation ...................: 1.08%
Avg. Bytes Free per Page.....................: 74.8
Avg. Page Density (full).....................: 99.08%
DBCC execution completed. If DBCC printed error messages, contact the system 
administrator.

To determine whether you need to rebuild the index because of excessive index page splits, look at the Scan Density value displayed by DBCC SHOWCONTIG. The Scan Density value must be at or near 100%. If it is significantly below 100%, then rebuild the index.