Re: Powerful server takes more than 30 minutes to run DTS package

On Oct 8, 3:09 pm, Devious Devil
<DeviousDe...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi,

Can you tell if there is a way to save the results of perfmon and some ideas
as to the kind of things other than CPU, disk IO to monitor as we don't have
permission to run either of these utilities ourselves so will have to rely on
our clients IT team to it and send us the results.

Regards,
Peter



"jhofm...@xxxxxxxxxxxxxx" wrote:
On Oct 4, 8:54 am, Devious Devil
<DeviousDe...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi,

Problem Context:
I have a group of related DTS packages each one produces a single CSV file.
I have another DTS package which calls each of the related group one after
another to genereate all the files in one go. The DTS packages are run via a
stored procedure that calls xp_cmdshell that runs DTSRun.exe. The SP is call
from a .NET 2.0 WinForms application. The proxy account has been set and is
used to run the DTS packages. A windows account for the proxy is used and has
access to the destination directory for the files.

For most of our clients it works fine however for 1 of our clients it
timesout. The timeout limit in .NET code for the export is 30 minutes.

Now for the strange bit, on our development server using a copy of the
clients database the export takes between 5 and 6 minutes (well within the 30
minute limit); our server is a 2 CPU, 2 GB RAM, Win 2003 SP1, SQL Server 2000
SP4 machine. The clients server is a 4 CPU, 4GB RAM, Win 2003 SP2, SQL Server
2000 SP4.

There doesn't seem to be anything running on the server that would impact
the SQL Server in any significant way, certainly nothing that would cause
their server to run at least 5 times slower than ours. In fact our server in
theory is under more pressure as developers from other projects also use it.

Anybody with any ideas that we could check or things we could try would be
much appreciated.

Regards,
Peter

Hi Peter,

Have you tried running SQL Profiler and Perfmon to see what it is
actually doing? It seems unlikely that it would be hitting any
resource constraints, more likely there is a permissions issue
somewhere in the chain of events.

Good Luck!
J- Hide quoted text -

- Show quoted text -

Hi Peter,

As SSIS tries to do everything in memory, performance can be severely
influenced by any memory issues. From the spec of the server I would
imagine this is not the case here, but I have seen some servers where
the service doesn't recycle RAM properly and pages all the in-memory
activities to disk.

I'm not sure about saving Perfmon results sry.

Good Luck!
J

.

Relevant Pages

  • Re: Powerful server takes more than 30 minutes to run DTS package
    ... Can you tell if there is a way to save the results of perfmon and some ideas ... I have a group of related DTS packages each one produces a single CSV file. ... For most of our clients it works fine however for 1 of our clients it ... There doesn't seem to be anything running on the server that would impact ...
    (microsoft.public.sqlserver.dts)
  • DTS / SQL Memory Leak ?
    ... I do have some DTS packages that do processing of data for a star ... "There is insufficient memory to run this query" ... I would have expected to see the DTS in this sysprocesses list with ... Server to show more than 480 MB memory in use when the current overall ...
    (microsoft.public.sqlserver.server)
  • Re: page life expectancy
    ... Do you see in PerfMon that SQL Server does utilize 6656 memory? ... Highest disk queue is the read queue to LUN ...
    (microsoft.public.sqlserver.clustering)
  • Server hang up
    ... For Server hangs; we need to explore a little bit more: ... As indicated by Vera Perfmon would be a nice place to start; ... out we might need to go to the extent of a complete memory dump. ...
    (microsoft.public.win2000.termserv.clients)
  • Re: Allocating more memory to sql server
    ... We have Win2K Adv Server and SQL 2000 EE. ... I also took perfmon counters ... for SQL server Targer Sever memory and Total Server Memory, ...
    (microsoft.public.sqlserver.setup)