SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Look at the size of my BCHR


Look at the size of my BCHR

Author
Message
Tony Davis
Tony Davis
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: Administrators
Points: 1397 Visits: 1162

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.

GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56879 Visits: 9730
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: Administrators
Points: 144630 Visits: 19424
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56879 Visits: 9730
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
joness59
joness59
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 11
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search