Running bat files in SSIS

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

Does the batch file have any prompts that require user input?|||

What is SQL Agent Service account running with?

Is it the same account that you are your DBA use - when running the package manually?

Because when you run the job - it runs with the credential of SQL Agent account.

|||Phil, thxs 4 your reply. No prompts that will require user input. The bat file will do a ftp. Thanks again.|||

How do I check what is SQL Agent Service account running my job? I know when I run the pkg manually it will run under my ID.

I do own the SQL Agent job in the Development environment so the DBA told me that my ID should be trying to execute the bat file (as I said before my ID has the permissions so there is something very strange happening). She said when I move to MO it will be owned by SA.

I can't go to MO if it is not working in DEV

Thanks for your reply.

|||If you are using the SSIS job step to run the package, it runs under the service account that the Agent is set up to use, not the job owner's account. Try setting up a proxy account - check books online for details.|||

Hi,

It's simple. Use an Execute Process Task.

Double Click the Task, Under Process Tab, set the values for the following properties:

1) Executable = cmd.exe

2) Arguments = /c C:\BatchFolder\MyFile.bat.

Make Sure when you run the job, The Service account that runs your job should have access to this batch file.

Thanks

Subhash Subramanyam

|||

Jwelch -

There is a proxy account set by the DBA already... When we add a step to a job, for type we do "Operating system (CmdExec)" and for run as we do "SQLAgentJobProxy".

So... Do you believe this proxy account is the one with no access to run the bat file?

Thanks.

|||

That would be my guess.

Also, is the bat file located on a local drive, network share with UNC path, or a mapped drive?

|||Network share with UNC path.|||Might try copying it locally and see if that makes a difference.|||

I don't have Integration Services installed locally. I will check with the DBA on the permissions mentioned here and I will let you guys know.

Thanks.

|||

Folks,

The feedback I had from my DBA was that the SQLAgentProxyAccount does have access to execute the bat file in question... This is proved when I run a dts through the same job and the same bat file is fired successfully. She also agreed that the Executable Property in the Execute Process Task step was evaluating correctly.

So, our work-around was to remove the bat file execution from the SSIS pkg and add it to the second step of the job. Dirty and scary if you think that the ftp might not be the last step of a whole process. In my case, thanks to God it is... But since we have lack of time right now, this is the solution we put in place.

I would say the issue is still not clarified but I will mark this as closed and if somebody in the future runs into the same problem a new thread could be opened.

Thanks to all.

Running bat files in SSIS

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

Does the batch file have any prompts that require user input?|||

What is SQL Agent Service account running with?

Is it the same account that you are your DBA use - when running the package manually?

Because when you run the job - it runs with the credential of SQL Agent account.

|||Phil, thxs 4 your reply. No prompts that will require user input. The bat file will do a ftp. Thanks again.|||

How do I check what is SQL Agent Service account running my job? I know when I run the pkg manually it will run under my ID.

I do own the SQL Agent job in the Development environment so the DBA told me that my ID should be trying to execute the bat file (as I said before my ID has the permissions so there is something very strange happening). She said when I move to MO it will be owned by SA.

I can't go to MO if it is not working in DEV

Thanks for your reply.

|||If you are using the SSIS job step to run the package, it runs under the service account that the Agent is set up to use, not the job owner's account. Try setting up a proxy account - check books online for details.|||

Hi,

It's simple. Use an Execute Process Task.

Double Click the Task, Under Process Tab, set the values for the following properties:

1) Executable = cmd.exe

2) Arguments = /c C:\BatchFolder\MyFile.bat.

Make Sure when you run the job, The Service account that runs your job should have access to this batch file.

Thanks

Subhash Subramanyam

|||

Jwelch -

There is a proxy account set by the DBA already... When we add a step to a job, for type we do "Operating system (CmdExec)" and for run as we do "SQLAgentJobProxy".

So... Do you believe this proxy account is the one with no access to run the bat file?

Thanks.

|||

That would be my guess.

Also, is the bat file located on a local drive, network share with UNC path, or a mapped drive?

|||Network share with UNC path.|||Might try copying it locally and see if that makes a difference.|||

I don't have Integration Services installed locally. I will check with the DBA on the permissions mentioned here and I will let you guys know.

Thanks.

|||

Folks,

The feedback I had from my DBA was that the SQLAgentProxyAccount does have access to execute the bat file in question... This is proved when I run a dts through the same job and the same bat file is fired successfully. She also agreed that the Executable Property in the Execute Process Task step was evaluating correctly.

So, our work-around was to remove the bat file execution from the SSIS pkg and add it to the second step of the job. Dirty and scary if you think that the ftp might not be the last step of a whole process. In my case, thanks to God it is... But since we have lack of time right now, this is the solution we put in place.

I would say the issue is still not clarified but I will mark this as closed and if somebody in the future runs into the same problem a new thread could be opened.

Thanks to all.

Running a 3 step dts process serially in SQL Server 2000

Hi,
I trying to run a 3 steps process in one dts package. Is there a way to run
these processes serially? It is currently running in parallel mode. This of
course is causing a blocking issue in SQL Server 2000.
Please let me know if this is possible in SQL Server 2000. And if it is,
how do I go about implementing it.
Thank you very much in advance for your help.
Thanks,
Sally
I use workflow. You select the first step, then select the second step and
hit workflow.
Russ
"Sally" wrote:

> Hi,
> I trying to run a 3 steps process in one dts package. Is there a way to run
> these processes serially? It is currently running in parallel mode. This of
> course is causing a blocking issue in SQL Server 2000.
> Please let me know if this is possible in SQL Server 2000. And if it is,
> how do I go about implementing it.
> Thank you very much in advance for your help.
> --
> Thanks,
> Sally
sql

Running a 3 step dts process serially in SQL Server 2000

Hi,
I trying to run a 3 steps process in one dts package. Is there a way to run
these processes serially? It is currently running in parallel mode. This o
f
course is causing a blocking issue in SQL Server 2000.
Please let me know if this is possible in SQL Server 2000. And if it is,
how do I go about implementing it.
Thank you very much in advance for your help.
Thanks,
SallyI use workflow. You select the first step, then select the second step and
hit workflow.
Russ
"Sally" wrote:

> Hi,
> I trying to run a 3 steps process in one dts package. Is there a way to r
un
> these processes serially? It is currently running in parallel mode. This
of
> course is causing a blocking issue in SQL Server 2000.
> Please let me know if this is possible in SQL Server 2000. And if it is,
> how do I go about implementing it.
> Thank you very much in advance for your help.
> --
> Thanks,
> Sally

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as long
to run.
"David Gugick" wrote:
> MrTim wrote:
> > If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> > to run. If I run 2 instances of it simultaneously, they will slow
> > down by a factor 5+. Reads and Duration of some transactions in
> > Profiler will jump up significanty.
> >
> > If anything you might have expected the second process to benefit
> > from data caching, but for some reason the opposite occurs.
> >
> > Anyone got any ideas why this might occur?
> > On SP3 - cannot yet install SP4 because of AWE.
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You're correct in assuming that something else is going on if n processes run
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as long
> to run.
>
> "David Gugick" wrote:
> > MrTim wrote:
> > > If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> > > to run. If I run 2 instances of it simultaneously, they will slow
> > > down by a factor 5+. Reads and Duration of some transactions in
> > > Profiler will jump up significanty.
> > >
> > > If anything you might have expected the second process to benefit
> > > from data caching, but for some reason the opposite occurs.
> > >
> > > Anyone got any ideas why this might occur?
> > > On SP3 - cannot yet install SP4 because of AWE.
> >
> > A 5 minute run is extremely long. Running two 5 minute queries will amp
> > up the cpu and disk activity accordingly and force each of them to take
> > much longer to complete - not to mention all other queries running. You
> > usually will not see a change in the number of reads or cpu values for
> > each. You should see a significant change in duration, however. A 5
> > minute operation is probably flushing all good data out of cache
> > multiple times while it scans tables. Not to mention that the disks are
> > probably quite busy during the operation, making each one take longer as
> > they contend for physical reads - which are very, very slow.
> >
> > I would suggest you get the performance of the 5 minute query down to a
> > few seconds if possible, run it off-hours, or run it off a dedicated
> > reporting server to keep it's activity from degrading the performance of
> > other queries.
> >
> > Have a look at the execution plan for the 5 minute query and see if it
> > can be tuned.
> >
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
> >
> >|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processes
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:
> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> > If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> > run.
> > If I run 2 instances of it simultaneously, they will slow down by a factor
> > 5+. Reads and Duration of some transactions in Profiler will jump up
> > significanty.
> >
> > If anything you might have expected the second process to benefit from
> > data
> > caching, but for some reason the opposite occurs.
> >
> > Anyone got any ideas why this might occur?
> > On SP3 - cannot yet install SP4 because of AWE.
> >
>
>|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What type
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.
MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as long
to run.
"David Gugick" wrote:

> MrTim wrote:
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.

> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You're correct in assuming that something else is going on if n processes run
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
[vbcol=seagreen]
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as long
> to run.
>
> "David Gugick" wrote:
|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>
|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processes
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:

> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
>
>
|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What type
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as lon
g
to run.
"David Gugick" wrote:

> MrTim wrote:
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.

> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You're correct in assuming that something else is going on if n processes ru
n
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
[vbcol=seagreen]
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as l
ong
> to run.
>
> "David Gugick" wrote:
>|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processe
s
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:

> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
>
>|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What typ
e
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Runaway CPU

For some reason, for two days now, in the morning hours
the sqlservr.exe process is using an average of 94% of the
CPU on a very powerful server.
Can anyone tell me how to determine what the problem is,
without just rebooting the server?
Here are some Buffer Manager statistics in case it helps:
Buffer cache hit ratio 3044
Buffer cache hit ratio base 3048
Page lookups/sec 405446548
Free list stalls/sec 114
Free pages 291
Total pages 207872
Target pages 207872
Database pages 190592
Reserved pages 648
Stolen pages 16989
Lazy writes/sec 1722
Readahead pages/sec 477294
Procedure cache pages 14862
Page reads/sec 616224
Page writes/sec 658045
Checkpoint pages/sec 315881
Thanks.
Allen"Allen White" <awhite_nospam@.advanstar.com> wrote in message
news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
> For some reason, for two days now, in the morning hours
> the sqlservr.exe process is using an average of 94% of the
> CPU on a very powerful server.
> Can anyone tell me how to determine what the problem is,
> without just rebooting the server?
> Here are some Buffer Manager statistics in case it helps:
> Buffer cache hit ratio 3044
> Buffer cache hit ratio base 3048
> Page lookups/sec 405446548
> Free list stalls/sec 114
> Free pages 291
> Total pages 207872
> Target pages 207872
> Database pages 190592
> Reserved pages 648
> Stolen pages 16989
> Lazy writes/sec 1722
> Readahead pages/sec 477294
> Procedure cache pages 14862
> Page reads/sec 616224
> Page writes/sec 658045
> Checkpoint pages/sec 315881
> Thanks.
> Allen
>
Turn on Profiler..
What jobs (if any) are running? Do you have maintenance plans executing
that are rebuilding indexes?
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, Rick. No maintenance plans are running. I keep a
trace running on all my production servers and have
reviewed the trace files and can see nothing that would
cause that kind of CPU activity. (The trace captures just
Batch Complete and RPC Complete activity.) I see lots and
lots of transactions, but nothing running for long periods
of time, nor anything that would be processor intensive.
Allen
>--Original Message--
>"Allen White" <awhite_nospam@.advanstar.com> wrote in
message
>news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
>> For some reason, for two days now, in the morning hours
>> the sqlservr.exe process is using an average of 94% of
the
>> CPU on a very powerful server.
>> Can anyone tell me how to determine what the problem is,
>> without just rebooting the server?
>> Here are some Buffer Manager statistics in case it
helps:
>> Buffer cache hit ratio 3044
>> Buffer cache hit ratio base 3048
>> Page lookups/sec 405446548
>> Free list stalls/sec 114
>> Free pages 291
>> Total pages 207872
>> Target pages 207872
>> Database pages 190592
>> Reserved pages 648
>> Stolen pages 16989
>> Lazy writes/sec 1722
>> Readahead pages/sec 477294
>> Procedure cache pages 14862
>> Page reads/sec 616224
>> Page writes/sec 658045
>> Checkpoint pages/sec 315881
>> Thanks.
>> Allen
>Turn on Profiler..
>What jobs (if any) are running? Do you have maintenance
plans executing
>that are rebuilding indexes?
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Runaway CPU

For some reason, for two days now, in the morning hours
the sqlservr.exe process is using an average of 94% of the
CPU on a very powerful server.
Can anyone tell me how to determine what the problem is,
without just rebooting the server?
Here are some Buffer Manager statistics in case it helps:
Buffer cache hit ratio3044
Buffer cache hit ratio base3048
Page lookups/sec405446548
Free list stalls/sec114
Free pages291
Total pages207872
Target pages207872
Database pages190592
Reserved pages648
Stolen pages16989
Lazy writes/sec1722
Readahead pages/sec477294
Procedure cache pages14862
Page reads/sec616224
Page writes/sec658045
Checkpoint pages/sec315881
Thanks.
Allen
"Allen White" <awhite_nospam@.advanstar.com> wrote in message
news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
> For some reason, for two days now, in the morning hours
> the sqlservr.exe process is using an average of 94% of the
> CPU on a very powerful server.
> Can anyone tell me how to determine what the problem is,
> without just rebooting the server?
> Here are some Buffer Manager statistics in case it helps:
> Buffer cache hit ratio 3044
> Buffer cache hit ratio base 3048
> Page lookups/sec 405446548
> Free list stalls/sec 114
> Free pages 291
> Total pages 207872
> Target pages 207872
> Database pages 190592
> Reserved pages 648
> Stolen pages 16989
> Lazy writes/sec 1722
> Readahead pages/sec 477294
> Procedure cache pages 14862
> Page reads/sec 616224
> Page writes/sec 658045
> Checkpoint pages/sec 315881
> Thanks.
> Allen
>
Turn on Profiler..
What jobs (if any) are running? Do you have maintenance plans executing
that are rebuilding indexes?
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks, Rick. No maintenance plans are running. I keep a
trace running on all my production servers and have
reviewed the trace files and can see nothing that would
cause that kind of CPU activity. (The trace captures just
Batch Complete and RPC Complete activity.) I see lots and
lots of transactions, but nothing running for long periods
of time, nor anything that would be processor intensive.
Allen

>--Original Message--
>"Allen White" <awhite_nospam@.advanstar.com> wrote in
message[vbcol=seagreen]
>news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
the[vbcol=seagreen]
helps:
>Turn on Profiler..
>What jobs (if any) are running? Do you have maintenance
plans executing
>that are rebuilding indexes?
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Runaway CPU

For some reason, for two days now, in the morning hours
the sqlservr.exe process is using an average of 94% of the
CPU on a very powerful server.
Can anyone tell me how to determine what the problem is,
without just rebooting the server?
Here are some Buffer Manager statistics in case it helps:
Buffer cache hit ratio 3044
Buffer cache hit ratio base 3048
Page lookups/sec 405446548
Free list stalls/sec 114
Free pages 291
Total pages 207872
Target pages 207872
Database pages 190592
Reserved pages 648
Stolen pages 16989
Lazy writes/sec 1722
Readahead pages/sec 477294
Procedure cache pages 14862
Page reads/sec 616224
Page writes/sec 658045
Checkpoint pages/sec 315881
Thanks.
Allen"Allen White" <awhite_nospam@.advanstar.com> wrote in message
news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
> For some reason, for two days now, in the morning hours
> the sqlservr.exe process is using an average of 94% of the
> CPU on a very powerful server.
> Can anyone tell me how to determine what the problem is,
> without just rebooting the server?
> Here are some Buffer Manager statistics in case it helps:
> Buffer cache hit ratio 3044
> Buffer cache hit ratio base 3048
> Page lookups/sec 405446548
> Free list stalls/sec 114
> Free pages 291
> Total pages 207872
> Target pages 207872
> Database pages 190592
> Reserved pages 648
> Stolen pages 16989
> Lazy writes/sec 1722
> Readahead pages/sec 477294
> Procedure cache pages 14862
> Page reads/sec 616224
> Page writes/sec 658045
> Checkpoint pages/sec 315881
> Thanks.
> Allen
>
Turn on Profiler..
What jobs (if any) are running? Do you have maintenance plans executing
that are rebuilding indexes?
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, Rick. No maintenance plans are running. I keep a
trace running on all my production servers and have
reviewed the trace files and can see nothing that would
cause that kind of CPU activity. (The trace captures just
Batch Complete and RPC Complete activity.) I see lots and
lots of transactions, but nothing running for long periods
of time, nor anything that would be processor intensive.
Allen

>--Original Message--
>"Allen White" <awhite_nospam@.advanstar.com> wrote in
message
>news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
the[vbcol=seagreen]
helps:[vbcol=seagreen]
>Turn on Profiler..
>What jobs (if any) are running? Do you have maintenance
plans executing
>that are rebuilding indexes?
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Run IN PROCESS

Sorry but how do you configure to run IN PROCESS. My task runs like this in a BAT file

dtexec /f "\\ssssagemet\xteurs\Admin\FC\Test\DTSX\Test_Alex\Rename_file_alex.dtsx"

Thanks,

aL.

What is the issue, exactly?

Run DTS within a DTS

Hi

On Enterprise Manager have created two DTS packages:

1 = Transfer Data
2 = Process Cube

In total we have loads of these.

What I want to do is to create a DTS that will run the toehr 2 DTS in sucessions.

Now when I use the Execute Package Task facility to run the 2 DTS, it fails...or just hangs for an eternity.

Is there way to run (say) 7 Execute Package Tasks without the master DTS failing?Any help please.... searched the web with no valid answers to this issues

Run a view and Export results as a CSV file

I need to automate a process for a client and not sure how I can achieve the
following. any help on this would be appreciated.
1. Run a View for the results set
2. Export this data to a csv file.
3. Run a batch job which FTP's the data to an off site server.
Note I have developed the view for my data and know how to implement the FTP
transfer using a batch file/Command. What I don't know is how to export the
results automatically at the end of the view and executing the batch job.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.762 / Virus Database: 510 - Release Date: 13/09/2004
One option is to use DTS to do this.
You can use a data pump task to select from the view. Use a
text destination connection for the destination file and set
this to be a csv file. Then use an execute process task to
execute your batch job or you can use it to execute the
command line FTP to send the files to the FTP site.
-Sue
On Wed, 15 Sep 2004 07:30:03 +1000, "Jeff Williams"
<jeff.williams@.hardsoft.com.au> wrote:

>I need to automate a process for a client and not sure how I can achieve the
>following. any help on this would be appreciated.
>1. Run a View for the results set
>2. Export this data to a csv file.
>3. Run a batch job which FTP's the data to an off site server.
>Note I have developed the view for my data and know how to implement the FTP
>transfer using a batch file/Command. What I don't know is how to export the
>results automatically at the end of the view and executing the batch job.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.762 / Virus Database: 510 - Release Date: 13/09/2004
>

Run a view and Export results as a CSV file

I need to automate a process for a client and not sure how I can achieve the
following. any help on this would be appreciated.
1. Run a View for the results set
2. Export this data to a csv file.
3. Run a batch job which FTP's the data to an off site server.
Note I have developed the view for my data and know how to implement the FTP
transfer using a batch file/Command. What I don't know is how to export the
results automatically at the end of the view and executing the batch job.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.762 / Virus Database: 510 - Release Date: 13/09/2004One option is to use DTS to do this.
You can use a data pump task to select from the view. Use a
text destination connection for the destination file and set
this to be a csv file. Then use an execute process task to
execute your batch job or you can use it to execute the
command line FTP to send the files to the FTP site.
-Sue
On Wed, 15 Sep 2004 07:30:03 +1000, "Jeff Williams"
<jeff.williams@.hardsoft.com.au> wrote:
>I need to automate a process for a client and not sure how I can achieve the
>following. any help on this would be appreciated.
>1. Run a View for the results set
>2. Export this data to a csv file.
>3. Run a batch job which FTP's the data to an off site server.
>Note I have developed the view for my data and know how to implement the FTP
>transfer using a batch file/Command. What I don't know is how to export the
>results automatically at the end of the view and executing the batch job.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.762 / Virus Database: 510 - Release Date: 13/09/2004
>

Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>

Run a DTS Win32 Process Task hidden

Anyone?
I have a picky client who does not want to see the command window open when a batch file is called from DTS.
Can anyone help me in running it hidden?If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.|||That's what I was afrain of.

Originally posted by Satya
If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.