Performance Degrading - Help

I have a SQL Server 2005 database that has been having problems since
monday. There have been no database changes for about a month, so
this strange behavior is recent. Unfortunately, our Test box is not a
good mirror of production, so tests to narrow down this problem are
tough to perform.
Our database performance has degraded to one twentieth of what it had
been. We have a package that calls a sproc 200 times and logs the
processing of the transactions Each record originally took about 2
seconds, now it is up to 30. It is this package that we use to get the
metrics of our performance but the general performance (for all
transactions) has slowed.
The crazy part of this problem is that Reorganize/Rebuild indexes
TEMPORARILY fixes the problem. After reorganizing/rebuilding indexes,
the package completes quickly. If you run the package 10 times, the
performance slowly deteriorates until it is again about 30 seconds per
record.
We tried to restore the version of the database to our TEST box to see
if it recreates a problem and to test whether it is a hardware issues.
The problem seems to continue, although our TEST box is SO Slow (not my
fault) that it is impossible to make a true test. Also SP! is on TEST,
not on PROD. (SP1 caused SERIOUS problems with our IIS packages and
had to be uninstalled in the interest of getting business done.) We
are planning to restore a backuup of last week's db during off hours to
prove/disprove that it is a hardware issue.
Has anyone heard of this kind of issue before? Our PROD box is
adequate in size and this DB is not especially large. Our indexes are
simple (more than just FK/PK).
Any guidance would be appreciated. We have a small shop here and we do
not have the luxury of having 3 or 4 consistent environments. I know
some people are going to give up on me and just say 'SP1 will fix it'.
(We plan to try that.) but if anyone has any good or original
thoughts, I could use them.
Ciao.If you reboot the server and then run the package again. Will it be fast? It
not,
It is very likely the query optimizer chooses wrong a query plan. You can
force
the query use the right plan. If it runs fast again after reboot the ser,
then the
queries sent to the server by the package caused the server memory leaking.
Lijun
<magkip@.hotmail.com> wrote in message
news:1159476780.524068.89360@.m73g2000cwd.googlegroups.com...
> I have a SQL Server 2005 database that has been having problems since
> monday. There have been no database changes for about a month, so
> this strange behavior is recent. Unfortunately, our Test box is not a
> good mirror of production, so tests to narrow down this problem are
> tough to perform.
> Our database performance has degraded to one twentieth of what it had
> been. We have a package that calls a sproc 200 times and logs the
> processing of the transactions Each record originally took about 2
> seconds, now it is up to 30. It is this package that we use to get the
> metrics of our performance but the general performance (for all
> transactions) has slowed.
> The crazy part of this problem is that Reorganize/Rebuild indexes
> TEMPORARILY fixes the problem. After reorganizing/rebuilding indexes,
> the package completes quickly. If you run the package 10 times, the
> performance slowly deteriorates until it is again about 30 seconds per
> record.
> We tried to restore the version of the database to our TEST box to see
> if it recreates a problem and to test whether it is a hardware issues.
> The problem seems to continue, although our TEST box is SO Slow (not my
> fault) that it is impossible to make a true test. Also SP! is on TEST,
> not on PROD. (SP1 caused SERIOUS problems with our IIS packages and
> had to be uninstalled in the interest of getting business done.) We
> are planning to restore a backuup of last week's db during off hours to
> prove/disprove that it is a hardware issue.
> Has anyone heard of this kind of issue before? Our PROD box is
> adequate in size and this DB is not especially large. Our indexes are
> simple (more than just FK/PK).
> Any guidance would be appreciated. We have a small shop here and we do
> not have the luxury of having 3 or 4 consistent environments. I know
> some people are going to give up on me and just say 'SP1 will fix it'.
> (We plan to try that.) but if anyone has any good or original
> thoughts, I could use them.
> Ciao.
>|||On 28 Sep 2006 13:53:00 -0700, magkip@.hotmail.com wrote:
>I have a SQL Server 2005 database that has been having problems since
>monday. There have been no database changes for about a month, so
>this strange behavior is recent. Unfortunately, our Test box is not a
>good mirror of production, so tests to narrow down this problem are
>tough to perform.
Do you use cursors?
What changed a month ago?
Do you see any blocking with sp_who2?
J.sql