January 9, 2009 at 12:38 pm
Comments posted to this topic are about the item Look at the size of my BCHR
January 9, 2009 at 2:23 pm
BWAA-HAAA!!! "I'll show you mine if you show me yours."
I've never looked, but I'm just betting that the BCHR is nearly 100% for cursors... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2009 at 2:55 pm
I have rarely seen anything below 99%, so I don’t pay much attention to it.
I look more at Average Page Life Expectancy and Page Reads/Sec. These seem to be more volatile and let you know how much thrashing of data pages in and out of memory the server is doing and how much of a bottleneck getting data from disk is.
January 10, 2009 at 11:14 am
I've honestly stopped checking this at many servers since they're usually > 90%. However I have seen it in the 80s or lower and more memory almost always solves this and gives a little boost.
January 10, 2009 at 11:32 am
well its one of the default performance counters in MOM, which raises a critical alert if it goes below 90% for more than 15 mins (by default) so I guess MS still consider it an indicator of performance.
For myself i admit its nice to see it up high and its something I track as part of memory monitoring but I would only really worry about it if it was ALWAYS low.
---------------------------------------------------------------------
January 11, 2009 at 12:50 am
Hi,
Since not all memory requests will reflect in the counter you could have > 90% and still have memory issues (and with issues I don't mean low on memory; over consumption is more often the case :P).
But as Steve pointed out, if you encounter a low BCHR and add some memory, there will usually be a performance boost whereas a high number doesn't necessearily mean all is well.
There could be memory issues elsewhere in the bufferpool - anything that goes through the Memory Broker for instance (large memory requests, queries with sort/hash operators, memory for parallel plans etc.) or in the memory area formerly known as mem_to_leave.
I tend to ignore it completely when it's high.
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
January 11, 2009 at 11:39 am
By itself it is not a key indicator, but it can be an important clue if you are having performance issues.
😎 Kate The Great :w00t:
If you don't have time to do it right the first time, where will you find time to do it again?
January 12, 2009 at 1:56 am
I dont use it. I measure the time/ workload it takes to perform different tasks that is prio.
January 12, 2009 at 9:20 am
I've never bothered with it. Maybe I should, but I never have.
I've found tracking runtime on queries to be much more useful to me in performance tuning.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 9:51 am
Interesting you've never used it. I use this as a first glance when I get a complaint about a new server. It's a thumb-sketch to see if this is grossly out of whack.
Once I'm familiar with a server, I'd probably never look at it again.
January 12, 2009 at 10:02 am
I guess I was really just trying to challenge the inherent "high means all is well, low means trouble" assumption that accompanies the BCHR.
As Elisabeth points out, a high BCHR can hide some very inefficient queries – which is the overriding problem with these sorts of averaged values. Also, a drop (or rise) in BCHR could mean anything…so you'd need to look at other data, such as query response times, queries doing excessive physical or logical reads, queries causing blocking etc, to work out what was going on.
So why not forget the BCHR and, as GSquared says, just track the stuff that actually means something?
If you have performance issues you'll spot them in specific query response times long before you'll spot them in the value of the BCHR.
January 12, 2009 at 12:31 pm
Steve Jones - Editor (1/12/2009)
Interesting you've never used it. I use this as a first glance when I get a complaint about a new server. It's a thumb-sketch to see if this is grossly out of whack.Once I'm familiar with a server, I'd probably never look at it again.
Basically, I find that, if there's something slow about the server, I'm better off starting with either the specific complaint or with a trace that includes run-time.
If, for example, users are complaining that one of their pages is slowing down, I'd start with the queries on that page. If the whole system is slow, I'd start with a trace to find out what's eating all the resources.
Starting with "is BCHR low" just adds a step to either of those.
I mean, what do you do if a page is too slow, and you check and BCHR is high? What do you do if a page is slow and BCHR is low? In either case, you start looking at execution of the queries on the page. So why have the extra step in there.
What do you do if a server is slow and BCHR is low? What do you do if a server is slow and BCHR is high? You run a trace and find out which queries are slowing it all down, and handle them. Same handling in both cases. Why not skip the BCHR step?
Basically, I've not found a situation where it would help me narrow down my decision on what to do next. Maybe I'm missing something, but it hasn't come up for me yet.
How does it help you know what to do with a server? I may just be missing something.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 8:59 am
Checking the BCHR takes only a few seconds and it gives me a quick thumb view of "is there more than this complaint?" coming. Helps me to quickly size up the server as this is low, we might have all kinds of issues relating to memory and my phone might start ringing more. I would still need to investigate further, but it keeps it in my mind that I might have memory issues.
Granted in 2005 I might approach this differently as there are better ways to check memory pressure, but in 2000 and before, this is a good quick check.
If this is high, then I have to investigate, but also check with others to see if things are slow. I've found that many times when something is slow, that person's PC is running slow or they're just having a bad day and are impatient.
January 13, 2009 at 11:12 am
Okay. That makes sense then. Just not something I've thought of, and thus haven't used.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2009 at 11:12 pm
It's interesting seeing this in a SQL Server discussion. In every database I've worked with (DB2, Oracle, SQL Server), I've read articles on the importance of cache hit ratios. Once I started reading the book "Oracle Wait Interface: Practical to Performance and Diagnostic Tuning", I realized how hit ratios aren't the best indicators and many times can be miss leading.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply