SQL Server Performance

Buffer cache Hit ratio

Discussion in 'Performance Tuning for DBAs' started by ananda, May 7, 2009.

  1. ananda New Member

    Hi friends
    i have fired the following query for find out buffer hit ratio one of the production server, but it is display nothing
    select [object_name], [counter_name], [cntr_value]
    from master.dbo.sysperfinfo
    where counter_name = 'Buffer cache hit ratio'
    or counter_name = 'Buffer cache hit ratio base'
    (0) rows return
    please can you tell me any one, waht is worng this server?
  2. satya Moderator

  3. ananda New Member

    Thanks satya for your reply..
    os: windows server 2003 enterprise edtion
    database : SQL server 2000 enterprise edtion
    SP: SP3 for sql server. product version 8.00.760(sp3)
    Every three days we are facing problem in this server, apache server also installed on this server and database connected to WEB application
    So user after 3 days called me application running very slow, that we are restarting SQL SERVER services after that it will be clear, than user will get same problem after 3 days.
    I had checked locking every day, so many SPID has been locked and we are getting most of select, update and delete command through trace porfiler tools.
    please tell me , how to encountered this problem?
  4. satya Moderator

    How about the resources such as memory & CPU?
    FIrst things first, you have to patch the server to the latest service pack on SQL 2000 which is SP4, as there are many fixes to such problems in terms of query optimizaiton & memory. So I would recommend to test and apply the SP4, also see this blog http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx that talks about how to monitor and start the performance tuning.
  5. ananda New Member

    CPU usage 38%
    PF usage 2.09 GB
    sqlservr.exe momory using - 1.680.50
    Total RAM - 3.25 GB, 4 CPU
    1. SQL Agent Alert - using more cpu, Can i delete all Demo: agent alert on production server?, if i deletean agent alert what impact on database?
    suggestion please alreday i have configured for database maintenance as follows, .
    1.Rebuild index every week sunday on 8:00 PM.
    2. Update_statistices daily on 2:00 AM (precentage of database sampling 10 %)
    3.Check Integrity every week sunday, Tuesday, friday on 12:45 AM.
    4. Full Backup daily on 9:00 PM . 5. TLog backup daily on every 1 hours.
    we able to getting all counter another server, on this server SP is RTM.
    could you tell me, why sp 3 missing all counter in sql server.?
  6. satya Moderator

    I believe you are missing the main point of testing and apply the latest service pack (SP4) for SQL 2000, the problem you are getting missing for counters is a part of the issue too of missing or unreported buffer cache hit ratio.
  7. Luis Martin Moderator

    Agree with Satya. What you wrote works with sp4.
    Start installing last sp.

Share This Page