Load Operation Performance | Microsoft SQL Server 2000 Administrators Companion

3 4

In this section, we'll look at three configuration options commonly used to improve the performance of load operations. Two of these options affect logging during bulk copy operations, and the other option affects locking. A bulk copy is an operation in which data is copied in large chunks; copying large chunks of data at one time is the most efficient way to reproduce data.

Logging Options

SQL Server uses a sophisticated logging mechanism to ensure that data is not lost in the event of a system failure. Logging is essential to the integrity of the data within the system, but it can significantly increase the load on the system. You can reduce the load on your system by reducing the amount of data that is logged during bulk loads.

NOTE
After a system failure, SQL Server will recover the database. All transactions that were not committed at the time of the failure will be rolled back (undone). All transactions that were committed at the time of the failure will be rolled forward (recovered). This rolling back or rolling forward will return the system to the state it was in before the failure occurred. Backup and recovery are described in detail in Chapters 32 and 33.

By default, all database insert operations are completely logged, enabling both rolling forward and rolling back of inserted data in the event of a system failure. By disabling full logging of bulk copies (that you perform by using BCP, the BULK INSERT statement, or the SELECT…INTO statement), you can reduce the amount of data logged, but only rollback operations will be supported. This option will improve the performance of bulk copies, but it will require the entire database-loading process to be restarted in the event of a system failure because the logging normally used for recovery is not done. This option will apply to staging tables only if you load these tables by using the bulk load methods described earlier.

Full logging of these bulk copy operations is disabled when all of the following conditions are met:

The database option select into/bulkcopy is set to true. Here is the syntax of the command using the sp_dboption stored procedure:
```
 exec sp_dboption database_name,   "select into/bulkcopy",   TRUE 
```
You can also configure this option with SQL Server Enterprise Manager. See Chapter 8 for details about Enterprise Manager.
The table that data is being loaded into is not replicated. (Replication is explained in Chapters 26, 27, and 28.)
The TABLOCK hint is specified. (See the section "Optional Parameters" later in this chapter for more information about this hint.) If the table that data is being loaded into has indexes defined, SQL Server does not require you to specify the TABLOCK hint.

Another database option, trunc. log on chkpt, disables the saving of log records while the option is set to true. When this option is set to TRUE, the transaction log will be truncated whenever a checkpoint occurs. This improves bulk copy performance, but it means that you will not be able to perform either a forward or backward recovery in the event of a system failure.

CAUTION
If you enable the trunc. log on chkpt option (by setting it to TRUE), you should do so only when you initially load data into the database. Completely disabling logging affects the entire database and can render the system unrecoverable. Thus, this option should never be used on a production system during normal operations, when recovery is important. If you do set the trunc. log on chkpt option to TRUE, be sure to disable it after you finish the loading operation.

To set this option by using a stored procedure, use sp_dboption with the following parameters:

 exec sp_dboption database_name,   "trunc. log on chkpt",   TRUE

NOTE
You can set additional options on the Options tab of the database Properties window, shown in Figure 24-1. Restrict Access limits access to specific roles or to a single user. Read Only disallows write access to the database. ANSI NULL Default specifies whether nullable columns are set to NULL or NOT NULL by default. Recursive Triggers simply enables recursive firing of triggers. Auto Update Statistics enables SQL Server to rebuild any out-of-date statistics during optimization. Torn Page Detection allows removal of incomplete pages. Auto Close specifies that the database be shut down after all its resources are freed and all users have logged off. Auto Shrink specifies that SQL Server will shrink database files periodically. Auto Create Statistics enables SQL Server to automatically build statistics during optimization. And Use Quoted Identifiers enforces ANSI rules regarding quotation marks.

click to view at full size.

Figure 24-1. The Options tab of the database Properties window.

Locking Option

You can also improve bulk copy performance by enabling the table lock on bulk load option. This option allows the use of a single table lock instead of numerous row locks for a bulk copy operation. You set the table lock on bulk load option by using the sp_tableoption stored procedure with the following parameters:

 exec sp_tableoption "table_name", "table lock on bulk load", TRUE

(Remember to reset the trunc. log on chkpt parameter after the load has been completed.) Because the table lock on bulk load option affects the locking mode of the table only during a bulk load, no performance degradation occurs when you are not doing bulk loads.

NOTE
To take advantage of the table lock on bulk load option, you must use the TABLOCK setting.