Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Unanswered: Getting reliable information about cache hit ratio

    Maybe I am just a lot better at this than I thought, but I figure that somewhere there is a mathematical rule that is being overlooked. When I run dbcc sqlperf (lrustats) on some of my production machines, I sometimes end up with a cache hit ratio (which is defined as a percentage, mind you) that is slightly over the limit:

    Statistic Value
    -------------------------------- ------------------------
    Cache Hit Ratio 100.00898
    Cache Flushes 0.0
    Free Page Scan (Avg) 0.0
    Free Page Scan (Max) 0.0
    Min Free Buffers 331.0
    Cache Size 4362.0
    Free Buffers 9434.0


    I suspect some counter somewhere is getting wrapped around its 4 byte limit. Is there any reliable source for getting statisics about SQL Server performance? Users tend be unreliable and say everything is slow.

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool hit ratio

    I'd say use the SQL Anaylser and use the Estimate Execution Plan area to start. Then, there alway the current activity under Management to look at. Then there the Profiler. I also suggest checking out a software called BMC. Maybe get a test copy.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Those are good suggestions for tuning individual queries, but I am looking more for server level statistics. Suppose I have one query that fills my buffer cache with tables A through G. Then after that query, there is another query that brings in tables J through Q. Unless the buffer is big enough to hold them all in RAM, then some or all of tables A though G are going to be paged out. This is what I am looking for. The interaction of multiple queries as reflected by a cache hit ratio (and other waittime statistics as I find them). BMC would also have to depend on the statistics gathered from SQL Server, so I should be able to get those statistics that BMC is reading. Clear as mud?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •