SQL Server does a great job of optimizing processing across a wide range of runtime scenarios, while also providing a flexible configuration and execution environment. One of the tradeoffs associated with this level of flexibility is that different configurations and implementations, while logically equivalent in terms of results, can yield very different performance.

Recently while performing data conversion on a large database my team encountered a good example of this situation. As part of the conversion we needed to UPDATE several VARCHAR and one DATETIME column(s) on a 500 million row table. Due to the highly variable string lengths in the target VARCHAR columns some page splits where expected, and because all values of the target columns needed to be touched a table scan was unavoidable. As this was a onetime conversion I didn’t initially worry about its performance, and because I was using a new server with 16 CPUs and 128 GB of RAM, I assumed SQL Server would provide good performance, even with an in-place update of this size.

After running for 24 hours, I took a look at the performance monitor counters. The process was serialized on a single CPU, and disk bound on the volume containing the target table. Given the time invested I decided to let the update finish, which it did in 2 days, 11 hours.

Discussing the latency with other team members, we speculated that physical disk contention might account for a significant portion of the run time. Because we were changing all the rows in the table we had the option of using INSERT instead of UPDATE to split the read and write I\O to different disks. To set this up we scripted the table and created a work table with the same schema. We also needed to move one physical data file to a separate file group. Of course, following the update, the table also needed to be copied back to the original file group.

At this point we were ready to test the INSERT, which ran in 8 hours, an 86% reduction in run time. Additional execution time can be saved by using SELECT INTO (recovery model was simple), which is minimally- logged. You cannot specify a file group when using SELECT INTO, so to retain the split I\O performance you must set the target file group as the default before running the insert. Also any existing indexes must be rebuilt. Results are summarized in the table below:

Statement     Runtime (H:M)     % Runtime Reduction

Update           59:11                    na

Insert             8:23                      86.8

Select Into     5:02                      91.5

See the following for more information regarding recovery models and minimally-logged operations:

BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8cfea566-8f89-4581-b30d-c53f1f2c79eb.htm

MSDN: http://msdn2.microsoft.com/en-us/library/ms191244.aspx

This exercise provided an example of how certain operations can be non-optimal even with a great database like SQL Server. More importantly, it showed how certain optimizations (in this case for I\O, by allowing all reads from one disk and all writes to another), can result in great performance. If you think about it, this is the same reason we put the log files on a different drive from the data files in a database. The log drives can do simple sequential writes. Thanks to Umachandar Jayachandran and Len Wyatt for contributing to this article.

-- Robin