Performance Monitoring
After capturing timing data for the 1 million-row tests, I ran the import again with the Win2K Performance Monitor active, logging CPU and disk utilization statistics. Graph 3 shows the CPU utilization I observed, and Graph 4 displays the I/O statistics.
The CPU utilization profile was interesting. For both bcp and DTS, CPU utilization during the import occurred in three distinct phases of approximately equal duration. During Phase 1, which roughly correlates to the time that the bulk copy operation was reading the input file and writing it to tempdb, CPU utilization averaged 44 percent. During Phase 2, which covers housekeeping activities, CPU utilization dropped to just 6 percent. During Phase 3, which reflects the completed batch being committed from tempdb to the target database table, CPU utilization averaged 46 percent, with one CPU running at 74 percent and the other at 18 percent.
When using T-SQL BULK INSERT, Phase 1 occurs within the SQL Server process and, according to my observations, takes only 82 percent of the CPU cycles that bcp or DTS requires. Note the heavy use of one CPU during Phase 3. I suspect that the more robust case (i.e., when you load several tables concurrently to different file groups on different disk devices) would use multiple processors more heavily. Also, note the reduction in Phase 3 CPU utilization during a minimally logged BULK INSERT operation—from 44 percent to 27 percent of the CPU.
I also observed disk utilization by viewing disk read and write I/Os per second and the average disk queue length for the system volume, a 3-disk drive RAID 0 array, and the two RAID arrays that hosted the SQL Server transaction log file and the database's data file. The I/Os-per-second metric showed fairly heavy I/O activity against the system volume (about 90 I/Os per second) throughout the bulk copy operation, except for a brief lull at about the three-quarter mark. This RAID 0 array also hosted the tempdb database and the ASCII data files. All I/O on the database data and transaction log arrays occurred in Phase 3.
In general, an average disk I/O queue length of more than twice the number of disk drives in an array signals a potential bottleneck (i.e., a resource that limits the operation from completing faster). The system volume (with the tempdb database and the input ASCII files) had an average disk queue length of 1.4 and, in spite of steady I/O traffic, my testing didn't heavily stress the queue. My testing also didn't stress the disk with the database transaction log file—the average disk queue length never hit 1.
My tests most heavily used the volume holding the database's data file, with average I/O queue lengths during DTS, bcp, and BULK INSERT operations of 39.6, 67.7, and 31.9 events, respectively, as Graph 4 shows. These I/O queue lengths indicate a performance bottleneck on this array because it had 12 drives. Most computer operations have a bottleneck in the I/O subsystem. By identifying the source of performance problems, you'll know which resources to address if you need to speed up the load.
A Few Surprises
I was surprised a few times during testing (to help avoid surprises, see the sidebar "Some Gotchas to Remember," page 39). First, importing the data from the fixed-field format file took considerably longer than importing the same data from the CSV format file, and after the import, the database occupied much more disk space—almost three times as much. Trailing blanks in the varchar fields in the fixed-field format file caused this inordinate space consumption.
You specify trailing blanks by setting ANSI_PADDING to ON. ON isn't the default for SQL Server 2000. ANSI_PADDING is one of seven SET options that SQL Server requires when you work with indexed views, so administrators often turn it on. The value of ANSI_PADDING in effect when you define the field determines whether to truncate trailing blanks from varchar fields. The value in effect when you add or update the field isn't relevant. I ran a script in Query Analyzer to define the database. The database's Connection Properties defaults to setting ANSI_PADDING to ON, so the option was on when I ran the database definition script. If you aren't using indexed views and want to conserve disk space and reduce processing time, set ANSI_PADDING to OFF before you create or add fields to your table.
Logging Bulk Copy Operations
My second surprise came when I realized how complex minimally logging bulk copy operations was. When you import large numbers of records into a database, complete update logging can quickly fill the transaction log. However, when you set bulk copy operations for minimal logging, SQL Server logs only extent allocations (SQL Server's 64KB, 8-page units of disk space), not the data inserted. The extent allocation information lets SQL Server roll back the transaction, but the amount of information isn't sufficient to roll the transaction forward during database recovery operations.
SQL Server 7.0 provided the Select into/bulk copy database option to support minimally logged T-SQL BULK INSERT operations. SQL Server 2000 has two recovery models, simple and bulk-logged, that can result in minimally logged BULK INSERT operations. However, these settings alone aren't sufficient to keep SQL Server from fully logging BULK INSERT operations.
To get minimal logging, you must ensure that nothing replicates the target table, the target table has no triggers, the target table is either empty or has no indexes defined, and you used the TABLOCK hint so that the BULK INSERT operation will use table-level, rather than row-level, locking. With large bulk copy operations, you must also consider the BATCHSIZE setting in effect. By default, the entire input file is one batch; SQL Server considers it to be one transaction. Until you commit that transaction, SQL Server won't release the transaction log space that holds the rollback information. (For more information about recovery models, see Kalen Delaney, Inside SQL Server, "Database Recovery Models," June 2000.)
Using the TABLOCK Bulk Copy Hint
The TABLOCK hint is necessary for SQL Server to minimally log T-SQL BULK INSERT operations. The TABLOCK hint is also one of the conditions SQL Server requires when you load data into one table from multiple clients at one time—a parallel data-load operation.
When I tested the TABLOCK option, I found that SQL Server minimally used the transaction log—only 13MB were in use after I loaded the 1 million-record table with the TABLOCK option, compared with more than 370MB after I loaded the table without the option. However, after I loaded the table with the TABLOCK option, the table occupied almost 2GB of disk space. Loading the table from the same input file without the TABLOCK option used only 228MB of disk space.
The DBCC SHOWCONTIG command, which tells you whether your indexes are fragmented, showed both versions with a scan density of 99 percent plus, indicating that both tables had very little external fragmentation. (For information about multiple types of fragmentation, see Kalen Delaney, "Keep SQL Server Up and Running," December 2000.) The difference between the tables was in their page density, the average amount of space used for each 8KB page allocated to the table. The average page density of the table with TABLOCK was only 11 percent; the table without the TABLOCK option had a page density of 99 percent.
I created the table with a clustered primary key constraint on the IDENTITY column and a FILLFACTOR of 10 percent. SQL Server uses the FILLFACTOR only when it builds an index; SQL Server ignores the FILLFACTOR when it modifies the index. (For more information about FILLFACTOR, see Kalen Delaney, Inside SQL Server, "The Fill-Factor Truth," page 29.) Thus, when you omit the TABLOCK hint, SQL Server appears to treat the T-SQL BULK INSERT operation as updates to the database. When you specify the TABLOCK hint, SQL Server seems to treat the BULK INSERT operation as an initial table load.
Note that the data disk's I/O queue length during minimally logged T-SQL BULK INSERT operations dropped to 3.7 events, which specifies the number of sequential write operations waiting and implies no disk I/O bottleneck. Additional testing with TABLOCK in effect revealed that SQL Server used the FILLFACTOR during table loading only when loading a new table. SQL Server ignored FILLFACTOR when I ran the BULK INSERT with TABLOCK on an empty table that had previously contained records. Sources at Microsoft explained this behavior as follows: "In return for disallowing updates concurrent with the BULK INSERT, TABLOCK allows some optimizations. I imagine you have a clustered index. If TABLOCK is set, we use the same algorithm internally that we use to populate the index during index creation, which is why it pays attention to the fill factor. This results in faster load times, but introduces some inconsistencies in behavior."
Which One to Use
For simple table loading when either bcp or T-SQL BULK INSERT will do the job, choose the one that best fits the way you work. You can't beat DTS's capabilities and ease of use, but you pay a performance penalty when you use SQL Server's Import/Export Wizard—which invokes the DTS Transform Data task—especially for larger tables (e.g., the cost was about 15 percent on my 1 million-row test). To combine the performance of T-SQL BULK INSERT with the convenience of a DTS package, use DTS's Bulk Insert task.
When you import very large numbers of rows into a database, the way you design the operation impacts not only the speed but also the operation's ultimate success. The batch size determines how frequently SQL Server frees log space during the bulk copy operation and helps you ensure that you won't run out of log space. Meeting the requirements for minimally logging the bulk copy operation has a huge impact on log space disk requirements and some impact on performance—yielding a 3 percent faster score in my 1 million-row, variable-length record test.