Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Look at the size of my BCHR Expand / Collapse
Author
Message
Posted Monday, January 12, 2009 10:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Wednesday, April 16, 2014 9:53 AM
Points: 569, Visits: 985

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.

Post #634820
Posted Monday, January 12, 2009 12:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 15,442, Visits: 9,590
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
Post #634943
Posted Tuesday, January 13, 2009 8:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:29 PM
Points: 32,834, Visits: 14,975
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
Post #635535
Posted Tuesday, January 13, 2009 11:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 15,442, Visits: 9,590
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
Post #635674
Posted Saturday, January 24, 2009 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 12, 2013 11:03 AM
Points: 1, 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.
Post #643109
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse