Home       Next>
SqlSavior.com
Performance Tuning Tips

8 Hours to 2:    Tuning a Large Data Warehouse Import in SQL Server.

In my first production DBA position, I had the opportunity to work on performance improvements for a fairly
large data warehouse load, which was taking about eight hours to complete every day. Since it depended
on data in other systems, which were not typically available until 5:00 AM, the data warehouse was
essentially unusable for over half of the workday. The original programmers were unavailable, rewriting the
system was not an option, and it had become so unreliable that the daily babysitting performed by its
creators (now absent) was all that kept the system up at all. Users and management were frustrated and
angry. It was not a pleasant situation…

So, I just added a few indexes and all was well, right?  NOT!  What follows is a compilation of a wider
range of techniques than is typically presented in most discussions of performance optimization and query
tuning. In fact many of the ideas here have nothing to do with query tuning, and some may seem quite
simplistic, but it surprised me how such simplistic remedies somehow managed to escape the original
designer’s attention! Here are ten suggestions to try when adding indexes just isn’t getting you anywhere:

1. Parallelize!

The loading system as designed consisted of three basic phases: copying tables from Oracle servers,
creating derived tables, and exporting data to other SQL Servers. This was implemented in several SQL
Server jobs, each calling numerous procedures and DTS packages. Copying the data from Oracle took
three hours and was done totally sequentially, table by table. My first change was to create another job to
run in parallel, which would copy the biggest table while the existing job handled the rest. Time saved: 1
hour. My boss was surprised, as he assumed that the system was I/O bound; that the disks were the
limiting factor. Which of course it was, but…  Even a single-processor system (ours had two processors)
can be doing something while waiting for disk operations to complete. Why not give it something to do?