Hello everyone..
I would like to request your precious help!!
Im having a little problem of performance in the package DTS of SQL SERVER 2000!
Ive made the mapping between the data with DDQ and ive made the inserts and the updates with Stored Procedures.. but the performance is very weak and is too slow!!!
How can i optimize this?
Thank you all!Hello LULU,
Have you tried to implement query parallelism?..it's a good method to improve the performance!..
Tom B.
SAS International Headquarters
Tom Berranger, Manager
SAS Institute GmbH
P.O. Box 105340
Neuenheimer Landstr. 28-30
D-69043 Heidelberg
GERMANY
ps: I see that you work with SQL Server?...i work for a multinational that works with this tool and we are looking for junior consultant in this area.
So if you are interested..please let me know...you have just to send a mail for this forum.
thanks.
Originally posted by LULU
Hello everyone..
I would like to request your precious help!!
Im having a little problem of performance in the package DTS of SQL SERVER 2000!
Ive made the mapping between the data with DDQ and ive made the inserts and the updates with Stored Procedures.. but the performance is very weak and is too slow!!!
How can i optimize this?
Thank you all!
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
DTS Performance Issues?
I have roughly ~80 machines periodically performing DTS transfers to a central server. The central server apparently can't handle this load. When watching in the performance monitor, after about one hour of being online, disk use is steadily pegged at 100% and the server becomes unresponsive. How do I track this down further and further diagnose/resolve this performance issue.
I can look at the current activity window but I don't know how to interpret or use the results.
thanks!Roger,
I wonder would it be easier to pull the data rather than push from the 80+ clients ? At least the server would be able to schedule them sequentially.
How much data are we talking about here ? Improvements might be made via indexing etc on the server table side ?
Mark|||Just a thought, may be you can export the data to a media as files and have the server import the data from the media as file import one by one.
I can look at the current activity window but I don't know how to interpret or use the results.
thanks!Roger,
I wonder would it be easier to pull the data rather than push from the 80+ clients ? At least the server would be able to schedule them sequentially.
How much data are we talking about here ? Improvements might be made via indexing etc on the server table side ?
Mark|||Just a thought, may be you can export the data to a media as files and have the server import the data from the media as file import one by one.
DTS performance
Hi,
Can anyone advise me of a quick way to estimate the time taken by DTS to
import a table (24 columns x 700,000 rows) from JD Edwards (running on
AS400) into SQL Server (new table and no manipulation involved)?
Many thanks,
Steve"Steve McDonald" <ajones1@.nsw.bigpond.net.au> wrote in message news:<TBbRa.7157$wU5.924@.news-server.bigpond.net.au>...
> Hi,
> Can anyone advise me of a quick way to estimate the time taken by DTS to
> import a table (24 columns x 700,000 rows) from JD Edwards (running on
> AS400) into SQL Server (new table and no manipulation involved)?
> Many thanks,
> Steve
There are plenty of factors which may vary from one environment to
another, so the most reliable way to find out would simply be to test
it. In theory, you could work out the volume of data (average row
length x number of rows), then divide that by your network speed, add
some time for DTS overhead etc., but in practice I think you wouldn't
get a very accurate answer.
Simon
Monday, March 19, 2012
DTS package runs slow when run from SQLAgent
So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
OsolageHi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. I
f
you have done the former then I would expect similar times and any
degredation would be network related.
John
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
OsolageHi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. I
f
you have done the former then I would expect similar times and any
degredation would be network related.
John
DTS package runs slow when run from SQLAgent
So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
OsolageHi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
OsolageHi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John
DTS package runs slow when run from SQLAgent
So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
Osolage
Hi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
Osolage
Hi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John
Subscribe to:
Posts (Atom)