473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

estimation the buffer cache hit ratio

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
Sep 1 '06 #1
1 2840
Marek Wierzbicki (ma******************@azymuttttt.pl) writes:
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 1 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: lasmith3 | last post by:
This issue just happen recently. The buffer cache ratio went from > 90% to 50% and has slowly been climbing back up over 8 hours or so. Its currently @ 76%. Is this something I should take action...
9
by: Matt | last post by:
Hello I am tring to figure out why our SQL server is a bit sluggish from time to time. It is running a dual XEON, with 2.5 GB RAM, and a fast SCSI I/O sub system setup as follows. OS,...
7
by: eric | last post by:
Hi there, BP hit ratio = 1 - (BP physical reads / BP logical reads). If all the BP physical reads are asynchronous, it should mean that the pages are brought up to the bufferpool before the...
4
by: xixi | last post by:
the formula for package cache hit ratio is 1 - (package cache inserts/package cache lookups), what the result would be a effective ratio? currently we have cache inserts=25, cache lookups=35, so...
0
by: shterke | last post by:
Good day, I've been monitoring a DB2 system and noticed a low package cache hit ratio, I calculated it based on the formula in the db2 information center: ...
1
by: nkumarin001 | last post by:
Hi, Anyone please answer this question Why 99%+ Database Buffer Cache hit ratio is not OK? Regards, Naveen
0
by: Hypnotik | last post by:
My program is to simulate cache memory. I read in the info from 2 external files, 1) access 2) data in memory. When I read the information in I display the info...and it is all correct. However...
1
by: Ramchandra | last post by:
Hi , i am facing problem in package cache hit ratio its between 47-45% which is very less:- here are my configuration related to package cache:- Catalog cache size (4KB) ...
4
by: Patrick Finnegan | last post by:
Is there a DB2 setting that will force a table to be cached in the buffer pool? We have four tables that we want to cache completely in the buffer pool to ensure that all the data is read from...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.