time based performance decay

[boyopeg@gmail.com]

I am working on an application with a sql server backend. the sql
performance always begins great, but decays quickly over time. if i
restart the sql service the performance shoots back up. The
degredation is gradual and slows to a crawl after about 8 days. During
this time I monitor several values from the sysperfinfo table and see
some interesting results. for example, i see the page splits/sec rise
from single digits to ~8000 after 6 days. i have tried rebuilding
indexes to reduce this number but it does not go down--ever. i have
tried dozens of solutions (including new hardware) none of which seem
to fix the problem.

i can provide more details if anyone is willing to help
thx.

Noah Peters

[Erland Sommarskog]

Re: time based performance decay

(boyopeg@gmail. com) writes:[color=blue]
> I am working on an application with a sql server backend. the sql
> performance always begins great, but decays quickly over time. if i
> restart the sql service the performance shoots back up. The
> degredation is gradual and slows to a crawl after about 8 days. During
> this time I monitor several values from the sysperfinfo table and see
> some interesting results. for example, i see the page splits/sec rise
> from single digits to ~8000 after 6 days. i have tried rebuilding
> indexes to reduce this number but it does not go down--ever. i have
> tried dozens of solutions (including new hardware) none of which seem
> to fix the problem.[/color]

I have a feeling that the page-split counter is not the wrong track.
It could be an accumulated counter.

Rather I would look for blocking. That and run Profiler to find slow
queries. But since a restart helps, blocking seems as a likely cause.
A good tool to troubleshoot blocking issues, but also a good tool to
see what is going on is aba_lockinfo, which you can download from my
website at http://www.sommarskog.se/sqlutil/aba_lockinfo.html. I often
use this one to get a quick idea of what performance hogs there might
be. The idea is that although it only gives a snapshot, anything that
runs for a long time is likely to turn up. And, as I said, it can
reveal blocking.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: time based performance decay

[posted and mailed, please reply in news]

boyopeg@gmail.c om writes:[color=blue]
>I am working on an application with a sql server backend. the sql
>performance always begins great, but decays quickly over time. if i
>restart the sql service the performance shoots back up. The
>degredation is gradual and slows to a crawl after about 8 days. During
>this time I monitor several values from the sysperfinfo table and see
>some interesting results. for example, i see the page splits/sec rise
>from single digits to ~8000 after 6 days. i have tried rebuilding
>indexes to reduce this number but it does not go down--ever. i have
>tried dozens of solutions (including new hardware) none of which seem
>to fix the problem.
>
>i can provide more details if anyone is willing to help
>thx.[/color]

I eavesdropped an discussion among some other SQL Server MVPs, and
learnt about another possible cause for this: if you have an application
that submits dynamically built SQL statements, and don't parameterize
them, and the statements are too complex to auto-parameterized, you may
end up with lots of similar plans in the cache, leading to degradation
when the optimizer is searching for reusable plans. This can paricularly
happen if the machine has lots of memory, and there is no other
pressure on the cache.

Whether this could be the case I have no idea, but a Profiler trace
could show what sort of behaviour the application has.

One way to test whether somehhing like this, is to issue a
DBCC DROPCLEANBUFFER S, which flushes the cache. If you then see
a performance boost, that's a hint.

That is not to say that my previous suggestion to examine blocking
was useless.




--
Erland Sommarskog, Stockholm, esquel@sommarsk og.se

[boyopeg]

Re: time based performance decay

So far we have been unable to satisfactorily resolve this problem. By
removing all indexes the problem never occurs, but performance suffers
in general without the indexes. Even with very minimal indexes the
problem still happens.

[Erland Sommarskog]

Re: time based performance decay

boyopeg (boyopeg@gmail. com) writes:[color=blue]
> So far we have been unable to satisfactorily resolve this problem. By
> removing all indexes the problem never occurs, but performance suffers
> in general without the indexes. Even with very minimal indexes the
> problem still happens.[/color]

Interesting. I have to say that I don't really have a clue how this could
happen, so I have asked around among MVP colleagues to see if any of them
has any ideas.

Just to make sure: you have ruled out blocking?

A shot in the dark would be to turn off auto-stats.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: time based performance decay

Erland Sommarskog (esquel@sommars kog.se) writes:[color=blue]
> boyopeg (boyopeg@gmail. com) writes:[color=green]
>> So far we have been unable to satisfactorily resolve this problem. By
>> removing all indexes the problem never occurs, but performance suffers
>> in general without the indexes. Even with very minimal indexes the
>> problem still happens.[/color]
>
> Interesting. I have to say that I don't really have a clue how this could
> happen, so I have asked around among MVP colleagues to see if any of them
> has any ideas.[/color]

One suggestion was to check the SQL Server errorlog and the Event log for
access violations and the like. Frequent crashes can lead to dead spots
within SQL Server.

Of course, since it's very difficult to do remote diagnostiscs over the
newsgroup, the most effective may be to open a case with Microsoft. Unless,
it can be resolved as a bug, it may not be cheap. But the performance
degradation may also be costly to you.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp