Siebel Enterprise Integration Manager Administration Guide > EIM Performance Tuning > Database Optimization Tips for EIM >

MS SQL Server

The following sections describe EIM tuning tips for the MS SQL Server database platform.

Fixing Table Fragmentation

Table and index fragmentation occurs on tables that have a lot of 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 may 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 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 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 should be at or near 100%. If it is significantly below 100%, rebuild the index.

Purging an EIM Table

When purging data from the EIM table, use the TRUNCATE TABLE statement. This is a fast, nonlogged method of deleting all rows in a table. DELETE physically removes one row at a time and records each deleted row in the transaction log. TRUNCATE TABLE only logs the deallocation of whole data pages and immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.

Parallel Data Load for EIM tables Using bcp

Microsoft SQL Server allows data to be bulk copied into a single EIM table from multiple clients in parallel, using the bcp utility or BULK INSERT statement. You should use the bcp utility or BULK INSERT statement when the following conditions are true:

These recommendations can improve the performance of data load operations. Perform the following tasks, in the order in which they are presented, to bulk copy data into SQL Server in parallel:

  1. Set the database option truncate log on checkpoint to TRUE using sp_dboption.(*)
  2. Set the database option select into/bulkcopy to TRUE using sp_dboption.

    In a logged bulk copy all row insertions are logged, which can generate many log records in a large bulk copy operation. These log records can be used to both roll forward and roll back the logged bulk copy operation.

    In a nonlogged bulk copy, only the allocations of new pages to hold the bulk copied rows are logged. This significantly reduces the amount of logging that is needed and speeds the bulk copy operation. Once you do a nonlogged operation you should immediately back up so transaction logging can be restarted.

  3. Make sure that the table does not have any indexes, or if the table has an index, make sure it is empty when the bulk copy starts.
  4. Make sure you are not replicating the target table.
  5. Make sure the TABLOCK hint is specified using bcp_control with eOption set to BCPHINTS.

    NOTE:  Using ordered data and the ORDER hint will not affect performance because the clustered index is not present in the EIM table during the data load.

  6. After data has been bulk copied into a single EIM table from multiple clients, any clustered index on the table should be recreated using DBCC DBREINDEX.


This is the database that Microsoft SQL Server uses for temporary space needed during execution of various queries. Set the initial size of the TEMPDB to a minimum of 100 MB, and configure it for auto-growth, which allows SQL Server to expand the temporary database as needed to accommodate user activity.

Configuration Parameters

Additional parameters have a direct impact on SQL Server performance and should be set according to the following guidelines:

 Siebel Enterprise Integration Manager Administration Guide 
 Published: 05 January 2004