none
cache hit ratio < 90 % on SQL 2008 x64

    Question

  • Hi all,

    I have a SQL 2008 Ent x64 cluster on W2k8  each nodes have 64 Gb of RAM and MOM alert me about the cache hit ratio, after more research on the net I have settings the memory lock pages, setting the min and max memory restarted each node but the problem remain the same, I have remarked that the page file grow with the physical memory, for exemple If I have 22 Gb of RAM the page file is 22 GB.

    any idea about this pb ?

    Thanks,

    Mike
    MIKE
    Tuesday, March 16, 2010 3:46 PM

Answers

  • Thank you for your suggestion, but I have discovered the root cause, this cluster is monitored by MOM and I have discover that the alert is generated by the instance who is passive and that is normal , I have change the monitring rule, when I monitor with perfmon the active instance counters: buffer cache hit ratio, page expectancy life,read/s are excellent, just a mistake of my part.... :(

     


    MIKE
    • Marked as answer by Mike2670 Thursday, August 19, 2010 1:22 PM
    Thursday, August 19, 2010 1:21 PM

All replies

  • Perhaps you have so much data and/or so badly designed queries/indexes so that SQL Server just have to keep going against disk to get to the data you refer to from your query load?
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, March 16, 2010 6:31 PM
    Moderator
  • Can you run SELECT @@VERSION and report back with the results?  Don you just have the Database Engine installed on this instance, or do you also have other SQL Server components (such as Full-Text Search)?   What do you have MaxServer Memory set at?  What is your typical Page Life Expectancy?
    Glenn Berry
    http://glennberrysqlperformance.spaces.live.com/
    http://www.twitter.com/GlennAlanBerry
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, March 17, 2010 3:04 AM
    Moderator
  • For information this sql server is dedicated for MOSS 2007, no custom db...
    MIKE
    Wednesday, March 17, 2010 8:12 AM
  • Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

    MaxServer Memory is set to 65057

    What do you mean for the Page Life Expextancy? and How I can get it?

    Thank you,

    MIKE
    Wednesday, March 17, 2010 8:16 AM
  • Sorry I have forgetted: On this instance i have only SQL Engine with SQL full text filter launcher....

    Only one instance.
    MIKE
    Wednesday, March 17, 2010 8:18 AM
  • The Page Life Expectancy is around 800.

     

    Any Idea? I saw in many article that the file system must be formatted with block size at 64 K is not my case it is formatted in 4 K (default) it could be the root of the problem?

    Note: This instance is deciated only for MOSS 2700 and on this server there are only one instance and SRSS and others services are not installed..

     


    MIKE
    Thursday, August 19, 2010 11:16 AM
  • I have seen this behaviour more on servers dedicated to Sharepoint databases. Probably you are using Sharepoint as a store for documents (unstructured documents). As these documents are being requested by the user, these have to pass the data store cache too (can be changed using filestream feature in Sharepoint 2010). Not much that you can do about it...

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Thursday, August 19, 2010 12:35 PM
  • Thank you for your suggestion, but I have discovered the root cause, this cluster is monitored by MOM and I have discover that the alert is generated by the instance who is passive and that is normal , I have change the monitring rule, when I monitor with perfmon the active instance counters: buffer cache hit ratio, page expectancy life,read/s are excellent, just a mistake of my part.... :(

     


    MIKE
    • Marked as answer by Mike2670 Thursday, August 19, 2010 1:22 PM
    Thursday, August 19, 2010 1:21 PM