Bookshelf v7.5: MS SQL Server

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:

The SQL Server is running on a computer with more than one processor.
The data to be bulk copied into the EIM table can be partitioned into separate data files.

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:

Set the database option truncate log on checkpoint to TRUE using sp_dboption.(*)
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.

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.
Make sure you are not replicating the target table.
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.

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.

TempDB

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:

SPIN COUNTER. This parameter specifies the maximum number of attempts that Microsoft SQL Server will make to obtain a given resource. The default settings should be adequate in most configurations.
MAX ASYNC I/O. This parameter configures the number of asynchronous inputs/outputs (I/Os) that can be issued. The default is 32, which allows a maximum of 32 outstanding reads and 32 outstanding writes per file. Servers with nonspecialized disk subsystems do not benefit from increasing this value. Servers with high-performance disk subsystems, such as intelligent disk controllers with RAM caching and RAID disk sets, may gain some performance benefit by increasing this value because they have the ability to rapidly accept multiple asynchronous I/O requests.
MAX DEGREE OF PARALLELISM. This option is used to configure Microsoft SQL Server's use of parallel query plan generation. Set this option to 1 to disable parallel query plan generation. This setting is mandatory to avoid generating an unpredictable query plan.
LOCKS. This option is used to specify the number of locks that Microsoft SQL Server allocates for use throughout the server. Locks are used to manage access to database resources such as tables and rows. This option should be set to 0 to allow Microsoft SQL Server to dynamically manage lock allocation based on system requirements.
AUTO CREATE STATISTICS. This option allows SQL Server to create new statistics for database columns as needed to improve query optimization. This option should be enabled.
AUTO UPDATE STATISTICS. This allows Microsoft SQL Server to automatically manage database statistics and update them as necessary to ensure proper query optimization. This option should be enabled.

Siebel Enterprise Integration Manager Administration Guide
Published: 05 January 2004