Enhancing DTS Package Performance
DTS is an easy tool to use when building data extraction and transformation and loading (ETL) processes; but, just because it is easy doesn't mean it will be the most efficient. DTS can be fast, but to support speed, you must design for speed. You can tune an existing package to make it more efficient, but if you have large volumes of data or have a small load-time requirement, performance tuning of a DTS package should not be an afterthought. In fact, it is usually best to design performance into the package from the start.
The following sections are a brief overview of some issues that can affect package performance.
General Server Performance
A well-tuned package will not run well if your destination repository is not properly tuned. If your destination operating system is a Windows-based server, you can use the Windows NT Perfmon to monitor a number of performance statistics—the most useful are CPU, memory, and disk I/O.
If CPU utilization is high and SQL Server is the destination repository, use the SQL Trace and Index Tuning Wizard to ensure that you have the correct indexes to support your package.
If you are short on memory or are doing excessive paging, consider adding RAM.
If disk I/O is high, make sure your SQL Server database-related files are well laid out, and utilize a RAID-based disk system. Whenever possible, keep the tempdb, data, and logs on separate disks. With SQL Server, you can even split the data across separate disks using filegroups.
Finally, if you are not running DB optimization as part of a regular (weekly) maintenance plan, consider running DBCC ShowConfig and DBCC ReIndex to optimize SQL internal data storage. After you do all the above checks and adjustments, if CPU utilization or disk I/O still remain high, then use the SQL Profiler to identify, analyze, and tune your application.