I have trouble with MSSQL2000 SP4 (without any hotfixes). During last two
weeks it start works anormally. After last optimalization (about few months
ago) it works good (fast, without blocks). Its buffer cache hit ratio was
about 99.7-99.8. Last day it starts work slow, there was many blocks and
dedlocks. There are no any queries, jobs and applications was added. Now
buffer cache hit ratio oscilate about 95-98. I try update statistics and
reindex some hard used tables, but there is no effect or effect is wery
short (after few hours problem return).
Mayby somene know what it could be?
Is it possible to estimate how each table (using DBCC SHOW_STATISTICS or
DBCC SHOWCONTIG or others) how the table affect on total buffer cache hit
ratio?
Marek
--
www.programowanieobiektowe.plMarek Wierzbicki (marek.wierzbickiiiii@.azymuttttt.pl) writes:
Quote:
Originally Posted by
I have trouble with MSSQL2000 SP4 (without any hotfixes). During last
two weeks it start works anormally. After last optimalization (about few
months ago) it works good (fast, without blocks). Its buffer cache hit
ratio was about 99.7-99.8. Last day it starts work slow, there was many
blocks and dedlocks. There are no any queries, jobs and applications was
added. Now buffer cache hit ratio oscilate about 95-98. I try update
statistics and reindex some hard used tables, but there is no effect or
effect is wery short (after few hours problem return).
>
Mayby somene know what it could be?
I would run Profiler and look for long-running queries. As your amount of
data grows and statistics changes, the optimizer may go for a new plan.
Theoretically, you could also run into that when the amount of data
increases over a threshold value, the memory does no longer suffice for the
typical mix of queries.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment