DTS packages taking 100% processor

[datascapes]

We have an automated solution that fires off DTS packages per user requests from a web site. The packages pull data from an SQL Server 2000 db into the user's choice of formats (e.g., Access, Excel, csv, fixed width, dbf, etc.). While things are working great (no errors, etc.), the larger DTS packages take up 100% available processor. The DTS packages are being called from an ActiveX dll (VB). We queue the requests so we're not running too many at a time, but with several concurrent requests (or back to back requests), the processor stays pegged. Is there a way to force the DTS packages to not exceed a certain percentage of processor time? Other options?

 

[tlbroadbent]

Usually, pegged processor is caused by using cursors in a SQL script or procedure or by highly inefficient SQL queries that require a lot of processor time and power. How does the package work? Does it use queries or stored procedures? Have the SQL Statements been analyzed and optimized? Are the tables properly indexed? Terry L. Broadbent - DBA

http://tlbroadbent.home.attbi.com/index.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers."

 

[datascapes]

The DTS Packages are not running Stored Procs at all. They run ad hoc queries only. The problem is that the user may select from 8 different fields to filter on. That makes it next to impossible to create a Proc that will actually make a difference.

The table (there is only one, so no joins!) is rather large (approx. 1 million records). We've optimized it fairly well. The ad hoc searches only take a few seconds when the user is performing their initial search (before the DTS package is used). When they are done searching, the DTS package will take their request and fill it. That's where the processor pegs. The VB app passes in the SQL Statement and the destination file location and the package builds the output.

This all works very well under "normal" conditions, but when we began our stress-testing, we were able to request packages faster than they could be produced and the processor pegs. I does eventually catch up if we stop the test, but it can take a while.

 

[tlbroadbent]

I suspect that running several Data Transformations at the same time may cause processor usage to peg. Each package will run as separate process - each requiring its own memory and processor time. In order to export, the packages will have toload the OLEDB driver for the output format requested. These have varied processor requirements.

These packages will each request records from SQL Server which must service all at the same time. SQL Server processor usage will increase as the number of open packages increases.

Have you used Performance Monitor to monitor threads to see which are using the processor time. Knowing if SQL Server uses most of the processor time or if the DTS packages use significant resources will help you narrow down the problem and find a solution. For example, you may find that DTS pacakages exporting to Access require more resources than than those exporting to a text file.

Is you SQL Server also running the Web Server? If so, that may be a major reason for the pegging you see.

Where are the export files written? Does the DTS package write to a local or remote drive? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[KJSDAD]

Check sql server web site... there is a post sp2 bug/fix for oledb pegging processor.... Kevin Herbert
Software Developer
kevin@ioutsource.com