Home > Articles > Data > SQL Server

Enhancing DTS Package Performance

  • Print
  • + Share This
Jim Samuelson and Brian Sullivan show you how to improve the performance of your DTS Packages. Performance bottlenecks can come from many sources; in some cases the bottleneck is a particular task, sometimes it can be the package in general, or it could even come from the server hardware configuration. Here we introduce some of these areas that can have significant performance impacts and what to do to solve or work around them.
This article is excerpted from SQL Server DTS, by Steve Hughes, Steve Miller, Jim Samuelson, Marcelino Santos, and Brian Sullivan.
From the author of

From the author of

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.

  • + Share This
  • 🔖 Save To Your Account