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?