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
Showing posts with label slow. Show all posts
Showing posts with label slow. Show all posts
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. 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
Friday, February 17, 2012
DTS Import Slow from As/400
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jmWhat's the data in on the AS/400?|||JDE.. But the file isn't vanilla JDE.. Actually, I just create a File using query first, then I use DTS to import/translate..|||Can't you dump the data on the 400 then just copy and load it?|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm
COULD BE YOUR NETWORK SERVICES, CHECK OUT HOW FAST IS WORKING YOUR NETWORK, IF YOU ARE WORKING ON WINDOWS, CHECK OUT TERMINAL SERVICES|||are there any tranformations?
how is your package transforming the data?
are you using activex operations to perform the x-foms?
activex is single threaded in dts that could slow down operations
what is happpening on the SQL Side once you import the data
are those tables indexed and do they have enabled constraints on them?
constraint checking can slow down an import
so can an index load.
Disable your constraints and indexes before you perfom a bulk load|||Thanks for all the suggestions..
I will look into each of them..
To answer one of the questions, yes I'm using terminal services..
thx
jm|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm
We pull data out of the AS/400 here and there are two areas which will be the cause of performance problems.
1)The speed of the network connection.
2)the size of the tables you are transforming.
Not much you can do apart from increase your network bandwith and the cards on the SQL Server and the AS/400 (I think we have Gigabit Ethernet cards on each end)
For every thousand rows is takes approx three seconds - so if you are around this then you're doing OK.
I can run that same import from a local access database and it will
run three times faster.
thx
jmWhat's the data in on the AS/400?|||JDE.. But the file isn't vanilla JDE.. Actually, I just create a File using query first, then I use DTS to import/translate..|||Can't you dump the data on the 400 then just copy and load it?|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm
COULD BE YOUR NETWORK SERVICES, CHECK OUT HOW FAST IS WORKING YOUR NETWORK, IF YOU ARE WORKING ON WINDOWS, CHECK OUT TERMINAL SERVICES|||are there any tranformations?
how is your package transforming the data?
are you using activex operations to perform the x-foms?
activex is single threaded in dts that could slow down operations
what is happpening on the SQL Side once you import the data
are those tables indexed and do they have enabled constraints on them?
constraint checking can slow down an import
so can an index load.
Disable your constraints and indexes before you perfom a bulk load|||Thanks for all the suggestions..
I will look into each of them..
To answer one of the questions, yes I'm using terminal services..
thx
jm|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm
We pull data out of the AS/400 here and there are two areas which will be the cause of performance problems.
1)The speed of the network connection.
2)the size of the tables you are transforming.
Not much you can do apart from increase your network bandwith and the cards on the SQL Server and the AS/400 (I think we have Gigabit Ethernet cards on each end)
For every thousand rows is takes approx three seconds - so if you are around this then you're doing OK.
Subscribe to:
Posts (Atom)