Mystery Proccess

I have set up a job on one of my sql servers to inform me
of any processes that run for more than a specified
amount of time. It eails me and the message tells me the
SPID, Host name, login and duration. A proccess keeps on
cropping up that i cannot account for so is causing me
concern. This procces is a bulk insert, the host is one of
out test servers, it is issued using dts designer and the
login used is for the server that the job is running on. I
also return the last TSQL command issued using dbcc
inputbuffer but this is blank. I have looked on the test
server and there are no jobs.
Can any one suggest a way that i can trace the source of
this process
Hi,
Start a trace with SQL Profiler. You can even specify the minimum duration
for a statement before it is show in the trace.
Karl Gram
"Mat" wrote:

> I have set up a job on one of my sql servers to inform me
> of any processes that run for more than a specified
> amount of time. It eails me and the message tells me the
> SPID, Host name, login and duration. A proccess keeps on
> cropping up that i cannot account for so is causing me
> concern. This procces is a bulk insert, the host is one of
> out test servers, it is issued using dts designer and the
> login used is for the server that the job is running on. I
> also return the last TSQL command issued using dbcc
> inputbuffer but this is blank. I have looked on the test
> server and there are no jobs.
> Can any one suggest a way that i can trace the source of
> this process
>
|||I have never realy used SQL profier before, can you
suggest which event i should be tracing
Cheers

>--Original Message--
>Hi,
>Start a trace with SQL Profiler. You can even specify the
minimum duration[vbcol=seagreen]
>for a statement before it is show in the trace.
>--
>Karl Gram
>"Mat" wrote:
me[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
on. I[vbcol=seagreen]
test[vbcol=seagreen]
of
>.
>
|||Mat,
From the 4 default events classes only the Stored Procedures (RPC:Completed)
and TSQL (SQL:BatchCompleted) events should be enough. The rest should be
removed.
Karl Gram
"mat" wrote:

> I have never realy used SQL profier before, can you
> suggest which event i should be tracing
> Cheers
> minimum duration
> me
> the
> on
> of
> the
> on. I
> test
> of
>
|||Solved the problem..
Maybe you may wish to know the cause...
a dts package was moved from the test server to the
production server. The connectins were changed to reflect
this but the name was left as the original name (test
server)
For what ever reason, sp_who, current activity and SQL
profiler pick up the conection name as the host name for
proccesss created by scheduled dts packages.

>--Original Message--
>Mat,
>From the 4 default events classes only the Stored
Procedures (RPC:Completed)
>and TSQL (SQL:BatchCompleted) events should be enough.
The rest should be[vbcol=seagreen]
>removed.
>--
>Karl Gram
>"mat" wrote:
the[vbcol=seagreen]
inform[vbcol=seagreen]
keeps[vbcol=seagreen]
me[vbcol=seagreen]
one[vbcol=seagreen]
and[vbcol=seagreen]
source
>.
>

Mystery Proccess

I have set up a job on one of my sql servers to inform me
of any processes that run for more than a specified
amount of time. It eails me and the message tells me the
SPID, Host name, login and duration. A proccess keeps on
cropping up that i cannot account for so is causing me
concern. This procces is a bulk insert, the host is one of
out test servers, it is issued using dts designer and the
login used is for the server that the job is running on. I
also return the last TSQL command issued using dbcc
inputbuffer but this is blank. I have looked on the test
server and there are no jobs.
Can any one suggest a way that i can trace the source of
this processI have never realy used SQL profier before, can you
suggest which event i should be tracing
Cheers
>--Original Message--
>Hi,
>Start a trace with SQL Profiler. You can even specify the
minimum duration
>for a statement before it is show in the trace.
>--
>Karl Gram
>"Mat" wrote:
>> I have set up a job on one of my sql servers to inform
me
>> of any processes that run for more than a specified
>> amount of time. It eails me and the message tells me
the
>> SPID, Host name, login and duration. A proccess keeps
on
>> cropping up that i cannot account for so is causing me
>> concern. This procces is a bulk insert, the host is one
of
>> out test servers, it is issued using dts designer and
the
>> login used is for the server that the job is running
on. I
>> also return the last TSQL command issued using dbcc
>> inputbuffer but this is blank. I have looked on the
test
>> server and there are no jobs.
>> Can any one suggest a way that i can trace the source
of
>> this process
>>
>.
>|||Solved the problem..
Maybe you may wish to know the cause...
a dts package was moved from the test server to the
production server. The connectins were changed to reflect
this but the name was left as the original name (test
server)
For what ever reason, sp_who, current activity and SQL
profiler pick up the conection name as the host name for
proccesss created by scheduled dts packages.
>--Original Message--
>Mat,
>From the 4 default events classes only the Stored
Procedures (RPC:Completed)
>and TSQL (SQL:BatchCompleted) events should be enough.
The rest should be
>removed.
>--
>Karl Gram
>"mat" wrote:
>> I have never realy used SQL profier before, can you
>> suggest which event i should be tracing
>> Cheers
>> >--Original Message--
>> >Hi,
>> >
>> >Start a trace with SQL Profiler. You can even specify
the
>> minimum duration
>> >for a statement before it is show in the trace.
>> >
>> >--
>> >Karl Gram
>> >
>> >"Mat" wrote:
>> >
>> >> I have set up a job on one of my sql servers to
inform
>> me
>> >> of any processes that run for more than a specified
>> >> amount of time. It eails me and the message tells me
>> the
>> >> SPID, Host name, login and duration. A proccess
keeps
>> on
>> >> cropping up that i cannot account for so is causing
me
>> >> concern. This procces is a bulk insert, the host is
one
>> of
>> >> out test servers, it is issued using dts designer
and
>> the
>> >> login used is for the server that the job is running
>> on. I
>> >> also return the last TSQL command issued using dbcc
>> >> inputbuffer but this is blank. I have looked on the
>> test
>> >> server and there are no jobs.
>> >>
>> >> Can any one suggest a way that i can trace the
source
>> of
>> >> this process
>> >>
>> >>
>> >.
>> >
>.
>|||Hi,
Start a trace with SQL Profiler. You can even specify the minimum duration
for a statement before it is show in the trace.
--
Karl Gram
"Mat" wrote:
> I have set up a job on one of my sql servers to inform me
> of any processes that run for more than a specified
> amount of time. It eails me and the message tells me the
> SPID, Host name, login and duration. A proccess keeps on
> cropping up that i cannot account for so is causing me
> concern. This procces is a bulk insert, the host is one of
> out test servers, it is issued using dts designer and the
> login used is for the server that the job is running on. I
> also return the last TSQL command issued using dbcc
> inputbuffer but this is blank. I have looked on the test
> server and there are no jobs.
> Can any one suggest a way that i can trace the source of
> this process
>|||Mat,
From the 4 default events classes only the Stored Procedures (RPC:Completed)
and TSQL (SQL:BatchCompleted) events should be enough. The rest should be
removed.
--
Karl Gram
"mat" wrote:
> I have never realy used SQL profier before, can you
> suggest which event i should be tracing
> Cheers
> >--Original Message--
> >Hi,
> >
> >Start a trace with SQL Profiler. You can even specify the
> minimum duration
> >for a statement before it is show in the trace.
> >
> >--
> >Karl Gram
> >
> >"Mat" wrote:
> >
> >> I have set up a job on one of my sql servers to inform
> me
> >> of any processes that run for more than a specified
> >> amount of time. It eails me and the message tells me
> the
> >> SPID, Host name, login and duration. A proccess keeps
> on
> >> cropping up that i cannot account for so is causing me
> >> concern. This procces is a bulk insert, the host is one
> of
> >> out test servers, it is issued using dts designer and
> the
> >> login used is for the server that the job is running
> on. I
> >> also return the last TSQL command issued using dbcc
> >> inputbuffer but this is blank. I have looked on the
> test
> >> server and there are no jobs.
> >>
> >> Can any one suggest a way that i can trace the source
> of
> >> this process
> >>
> >>
> >.
> >
>

Mysterious Performance Degradation

Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?
index maintenance being done ?
Greg Jackson
PDX, Oregon
|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:

> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>
|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:

> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?
|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:

Mysterious Performance Degradation

Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:

> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
>|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:

> Hi,
> A batch job which updates a table in a SQL Server database took three hour
s
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the probl
em
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That migh
t
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
>|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
>|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
>

Mysterious Performance Degradation

Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
> > index maintenance being done ?
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
> >|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
> > Hi,
> > A batch job which updates a table in a SQL Server database took three hours
> > to run last week. Nothing changed except perhaps the database grew by 1%.
> > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > log file to match the production database server - this same job runs in
> > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > Time, and Processor Queue Length counters - they all fall well within
> > recommended thresholds. The difference between the two servers: the problem
> > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > know the job on my server won'f finish as fast as the same job on the Prod
> > server. But why this sudden change from 3 to 30 hours?|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
> > Dis you check the execution plan of the job on both the servers. That might
> > give you insight into if some statistics are missing etc.
> >
> > "Simi B." wrote:
> >
> > > Hi,
> > > A batch job which updates a table in a SQL Server database took three hours
> > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > log file to match the production database server - this same job runs in
> > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > Time, and Processor Queue Length counters - they all fall well within
> > > recommended thresholds. The difference between the two servers: the problem
> > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > know the job on my server won'f finish as fast as the same job on the Prod
> > > server. But why this sudden change from 3 to 30 hours?|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
> > Thanks all for your help. I was able to get it down from 41 hours to 7
> > hours...will see how I can tune it some more... =)
> >
> > "harvinder" wrote:
> >
> > > Dis you check the execution plan of the job on both the servers. That might
> > > give you insight into if some statistics are missing etc.
> > >
> > > "Simi B." wrote:
> > >
> > > > Hi,
> > > > A batch job which updates a table in a SQL Server database took three hours
> > > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > > log file to match the production database server - this same job runs in
> > > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > > Time, and Processor Queue Length counters - they all fall well within
> > > > recommended thresholds. The difference between the two servers: the problem
> > > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > > know the job on my server won'f finish as fast as the same job on the Prod
> > > > server. But why this sudden change from 3 to 30 hours?