buffer cache hit ratio estimation

Hi
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.plChances are you are now scanning a table(s) where as before you were doing
seeks. You need to profile the queries to see which are the poorly
performing ones (ones with high duration and reads) and address why.
Andrew J. Kelly SQL MVP
"Marek Wierzbicki" <marek.wierzbickiiiii@.azymuttttt.pl> wrote in message
news:ed8rmq$1h4p$1@.news2.ipartners.pl...
> Hi
> 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.pl|||> Chances are you are now scanning a table(s) where as before you were doing
> seeks. You need to profile the queries to see which are the poorly
> performing ones (ones with high duration and reads) and address why.
profiler didn't show any long time and expansive queries, but some simple
queries work about minute (not secounds like earlier)
Marek|||Then you need to find out why. Are there high disk queues? High CPU? Is
there blocking for those queries? You have to narrow it down so you can
start addressing the real issue. Maybe these will help:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Tempdb in 2005:
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
Physical Database Storage:
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/PhysDBStor.doc
--
Andrew J. Kelly SQL MVP
"Marek Wierzbicki" <marek.wierzbickiiiii@.azymuttttt.pl> wrote in message
news:edh8j6$4jj$1@.news2.ipartners.pl...
>> Chances are you are now scanning a table(s) where as before you were
>> doing seeks. You need to profile the queries to see which are the poorly
>> performing ones (ones with high duration and reads) and address why.
> profiler didn't show any long time and expansive queries, but some simple
> queries work about minute (not secounds like earlier)
>
> Marek
>|||> Then you need to find out why. Are there high disk queues? High CPU? Is
> there blocking for those queries? You have to narrow it down so you can
> start addressing the real issue. Maybe these will help:
I will try Your links. during hi trouble periods disk quees is over 50-60
Marek