SQL Server Performance Forum – Threads Archive

Cache hit ratio is showing 60-67%

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.

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.

No I hvent reindex or updated the stats.Pages/sec is >30

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.

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.

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

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.

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
[email protected] When life gives you a lemon, fire the DBA.

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?

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.

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

Check
KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;224587
and articlehttp://www.sql-server-performance.com/articles_performance.asp for further information.
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.

Are you using stored procedures for your application? 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. If you are using stored procedures exclusively then you could start by using profiler to monitor the following procedure events: sp:recompile sp:cachehit sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tarting sp:completed sp:cachemiss 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. That’s a start anyway. Hope it helps. <blockquote id="quote">quote:<hr height="1" noshade id="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. <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

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

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

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.

Actually I was talking about the buffer cache hit ratio. [B)]

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).

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

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.

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.

Karl Grambow www.sqldbcontrol.com

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.

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.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mmarovic <blockquote 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"></blockquote id="quote">It will also cause a higher cpu usage. <hr height="1" noshade id="quote"></blockquote id="quote"> 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=’

‘ />]. Something else must be going wrong to cause such a high cpu usage[xx(] Karl Grambow www.sqldbcontrol.com

That, or you’ve just started the task manager – that usually raises CPU usage to 100% for a bit.

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.

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

]]>