SQL Server Performance

Cache hit ratio is showing 60-67%

Discussion in 'General DBA Questions' started by jumbo, Nov 17, 2005.

  1. jumbo New Member

    My performance counter is giving the Cache hit ratio as 60-67% for the last 2 days which is below the normal 85-90%.No other application is installed on the server,Neither there were any query change.What is wrong with my server.
  2. Luis Martin Moderator

    Did you reindex and/or update statistics?
    What about pages/sec?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. jumbo New Member

    No I hvent reindex or updated the stats.Pages/sec is >30
  4. Luis Martin Moderator

    Pages/sec > 30 means you, may be, have less memory than neccesary.

    I suggest to reindex and update statistics (in windows time) as part of maintenance plan.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. satya Moderator

    How about memory settings on SQL Server?
    Any other application sharing the resources of server?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. jumbo New Member

    I have 2GB of RAM of which sql server almost using 1 GB.I have a .net apps which takes around 200MB of memory
  7. Luis Martin Moderator

    Then Reindex and Update Statistics.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  8. derrickleggett New Member

    Reindex and Update Statistics.

    And run Profiler to see if you have anything taking up a lot of resources or running badly. Have you checked your procedure cache to see how fast things are flushing out of cache?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  9. mmarovic Active Member

    quote:Originally posted by jumbo

    I have 2GB of RAM of which sql server almost using 1 GB.I have a .net apps which takes around 200MB of memory
    Your .net app run on the same machine as mssql server? Maybe .net apps takes now more memory?
  10. satya Moderator

    YOu must run PERFMON and capture counters for CPU, MEMORY, PHysical disk alongwith PROFILER and seperate any applications from SQL Server in order to keep it as a dedicated one for optimum performnace. If you perform update stats and reindex any number of times and due to the memory shortage sometime it is hard to get real time performance on server.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. tchaiyad New Member

    dear all,
    i have the same problem with Cache hit ratio it show 60-70 % which normal is 85-90 % so it very slow when use application and it use memory only 1.7 gb ,i try to reindex and update statistics but it's still the same
    i have detail about my server for every and configuration
    - windows 2003 advance server
    - sql server 2000 sp3 standard edition
    - ram 4 GB
    - cpu intel xeon 2.8 Ghz

    Rgds,

    My sql configuration
    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    affinity mask -2147483648 2147483647 0 0
    allow updates 0 1 0 0
    awe enabled 0 1 0 0
    c2 audit mode 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    Cross DB Ownership Chaining 0 1 0 0
    cursor threshold -1 2147483647 -1 -1
    default full-text language 0 2147483647 1033 1033
    default language 0 9999 32 32
    fill factor (%) 0 100 0 0
    index create memory (KB) 704 2147483647 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2147483647 0 0
    max degree of parallelism 0 32 0 0
    max server memory (MB) 4 2147483647 2625 2625
    max text repl size (B) 0 2147483647 65536 65536
    max worker threads 32 32767 255 255
    media retention 0 365 0 0
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 0 0
    nested triggers 0 1 1 1
    network packet size (B) 512 65536 4096 4096
    open objects 0 2147483647 0 0
    priority boost 0 1 0 0
    query governor cost limit 0 2147483647 0 0
    query wait (s) -1 2147483647 -1 -1
    recovery interval (min) 0 32767 0 0
    remote access 0 1 1 1
    remote login timeout (s) 0 2147483647 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2147483647 600 600
    scan for startup procs 0 1 0 0
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    two digit year cutoff 1753 9999 2049 2049
    user connections 0 32767 0 0
    user options 0 32767 0 0


    Performance captureObject: MSSQL$THCUSTOMS:Buffer Manager


    AWE lookup maps/sec0.000
    AWE stolen maps/sec0.000
    AWE unmap pages/sec0.000
    AWE write maps/sec0.000
    Lazy writes/sec0.000
    Page reads/sec0.000
    Page writes/sec0.000
    Total pages208208.000

    Object: MSSQL$THCUSTOMS:Cache Manager

    _Total
    Cache Hit Ratio62.830
    Cache Use Counts/sec18.997

    Object: MSSQL$THCUSTOMS:General Statistics


    Logins/sec0.000
    Logouts/sec0.000
    User Connections62.000

    Object: MSSQL$THCUSTOMS:Memory Manager


    Connection Memory (KB)2464.000
    Granted Workspace Memory (KB)5040.000
    Maximum Workspace Memory (KB)1205832.000
    Memory Grants Pending0.000
    SQL Cache Memory (KB)1320.000
    Total Server Memory (KB)1678856.000

    Object: Network Interface

    HP NC7760 Gigabit Server Adapter
    Bytes Total/sec17321.038
    Current Bandwidth100000000.000

    Object: PhysicalDisk

    _Total
    % Disk Time10.780
    Current Disk Queue Length0.000
    Disk Read Bytes/sec32775.642
    Disk Reads/sec4.001
    Disk Transfers/sec13.003

    Object: Processor

    _Total
    % Processor Time100.000
    % User Time87.500



  12. satya Moderator

  13. SQLDBcontrol New Member

    Are you using stored procedures for your application?<br /><br />When you have enough system memory and your cache-hit ratio is low it often is as a result of not using stored procedures or the stored procedures not being properly cached or being recompiled.<br /><br />If you are using stored procedures exclusively then you could start by using profiler to monitor the following procedure events:<br /><br />sp:recompile<br />sp:cachehit<br />sp<img src='/community/emoticons/emotion-7.gif' alt=':s' />tarting<br />sp:completed<br />sp:cachemiss<br /><br />Of particular note will be sp:cachemiss and sp:recompile. If you're getting a lot of these then that will give you an idea of what area to look at.<br /><br />That's a start anyway. Hope it helps.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by jumbo</i><br /><br />My performance counter is giving the Cache hit ratio as 60-67% for the last 2 days which is below the normal 85-90%.No other application is installed on the server,Neither there were any query change.What is wrong with my server.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  14. mmarovic Active Member

    Cash hit ratio is about hitting data cache not sp cache.
  15. SQLDBcontrol New Member

    Hmmm. The "Buffer Cache Hit Ratio" is about hitting the data cache surely. I thought we were talking about the "Cache Hit Ratio" not the "Buffer Cache Hit Ratio". I've been know it be wrong before though[B)]

    Have a look at the perfmon counter "Cache Hit Ratio" in the "SQL Server: Cache Manager" object.

    You'll notice that the default instance is _Total but there are several instances that you can monitor independantly, including "Procedure Plans" as well as several others.

    Note that there is a counter called "Buffer Cache Hit Ratio" in the "SQL Server: Buffer Manager" object.

    It seems we need some clarification on which cache hit ratio we're talking about.


    quote:Originally posted by mmarovic

    Cash hit ratio is about hitting data cache not sp cache.

    Karl Grambow

    www.sqldbcontrol.com
  16. Adriaan New Member

    Karl, you're right: there is just one cache for both data and execution plans. Not sure where the information is buried in BOL, but this was discussed here not too long ago. Also that if you have multiple instances running on the same computer, each instance will have its own cache.

    Pages/sec is on the high side, so indeed if there is not enough RAM available then the cache size will also be reduced, and the cache hit ratio will inevitably go down too.
  17. mmarovic Active Member

    Actually I was talking about the buffer cache hit ratio. [B)]
  18. Adriaan New Member

    quote:Originally posted by mmarovic

    Actually I was talking about the buffer cache hit ratio. [B)]
    Not sure there's a difference between "cache hit ratio" and "buffer cache hit ratio" - but of course I could be horribly wrong (foot-in-mouth disease all over again).
  19. SQLDBcontrol New Member

    Jumbo, I would suggest that you collect perfmon data on the specific instances for the Cache Hit Ratio - like the "Procedure Plans", "Prepared SQL Plans" and so on. One of them is bound to be low and that might provide some indicator as to where the problem lies.

    By the way, was that a 100% processor usage that I spotted in your perfmon data?[:0]


    quote:Originally posted by jumbo

    My performance counter is giving the Cache hit ratio as 60-67% for the last 2 days which is below the normal 85-90%.No other application is installed on the server,Neither there were any query change.What is wrong with my server.

    Karl Grambow

    www.sqldbcontrol.com
  20. mmarovic Active Member

    quote:Originally posted by SQLDBcontrol
    By the way, was that a 100% processor usage that I spotted in your perfmon data?[:0]
    Actually high processor usage may be impacted by frequent sp recompilations.
  21. SQLDBcontrol New Member

    Another thing to look out for Jumbo is whether you are fully-qualifying your stored procedure calls.

    Say for example that you have a procedure called dbo.MySproc.

    If a user (say MyUser) runs "exec MySproc", this will generate a cache miss because SQL Server will first look for an object in the cache that is called MyUser.MySproc. When it can't find it (there's your cache miss) it will then look for dbo.MySproc (here's a cache hit).

    Not fully-qualifying procedure names when executing them will completely mess up with your cache hit ratio.



    quote:Originally posted by jumbo

    My performance counter is giving the Cache hit ratio as 60-67% for the last 2 days which is below the normal 85-90%.No other application is installed on the server,Neither there were any query change.What is wrong with my server.

    Karl Grambow

    www.sqldbcontrol.com
  22. mmarovic Active Member

    quote:Not fully-qualifying procedure names when executing them will completely mess up with your cache hit ratio.
    It will also cause a higher cpu usage.
  23. Adriaan New Member

    Also, if you have stored procedures in your database with a name starting with "sp_", SQL starts by looking for them in the master database - yet another miss in the cache hit ratio - before looking in your database.
  24. SQLDBcontrol New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Not fully-qualifying procedure names when executing them will completely mess up with your cache hit ratio.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">It will also cause a higher cpu usage.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I didn't know that. If it caused a cpu usage of 100% though I'd be impressed [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]. Something else must be going wrong to cause such a high cpu usage[xx(]<br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  25. Adriaan New Member

    That, or you've just started the task manager - that usually raises CPU usage to 100% for a bit.
  26. mmarovic Active Member

    It depends on how much do you use stored procedure and on the traffic. I worked in the company where all sql code was inside stored procedure and we had extremely high traffic on our site and fully qualifying sp names really solved our cpu problems.
  27. mmarovic Active Member

    There are also other possible reasons for sp recompilation.
    High cpu usage may be caused by cursors too.

Share This Page