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: