SQLServer:Buffer Manager vs SQL Server:CacheManag

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQLServer:Buffer Manager vs SQL Server:CacheManag

Author

Topic

vandelft
Starting Member

14 Posts

Posted - 2005-10-13 : 04:30:11

I have the following performance counter:

SQL Server:Buffer Manager

Buffer Cache hit ratio: 99,870 > 99%
Database pages: 34193
Procedure cache pages: 149188
Stolen pages:153826
Target pages: 188104
Total pages:188104

SQL Server: Cache Manager
cache hit ratio: 50,181 <80% this is not very good and tells me that I need extra RAM memory
cache object counts 67309
cache pages 170177

What can I conclude ?

Buffer Cache hit ratio: 99,870 > 99% this is a very good value and tells that I don't have a RAM memory problem.
cache hit ratio: 50,181 <80% this is not very good and tells me that I need extra RAM memory.
My question is what is the real conclusion?

thank u very much for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-13 : 14:02:32

Everything that I've read says to use the Buffer Cache Hit Ratio. I use that along with SQLServer:MemoryManager\Total Server Memory(KB) and SQLServer:MemoryManager\Target Server Memory(KB). Total and Target values should be equal. When a reboot occurs though, it'll take a little bit to catch up. Outside of reboots, Target should never be higher than Total. Target is how much SQL Server would like to use and Total is how much it currently has.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-13 : 16:40:34

the cache manager object is referrring to the procedure cache. everything I have read in the past says that you want that to be 85% or higher. What this means is that you are not referencing stored execution plans in the procedure cache buffer. You may have a bunch of adhoc queries, or you have stored procedures that compile each time they run, or... (there are tons of other possibilities).

bottom line is that you are only referencing 80% of the stored execution plans, so performance could be suffering somewhat.

-ec

vandelft
Starting Member

14 Posts

Posted - 2005-10-14 : 05:47:05

quote:
Originally posted by tduggan

Everything that I've read says to use the Buffer Cache Hit Ratio. I use that along with SQLServer:MemoryManager\Total Server Memory(KB) and SQLServer:MemoryManager\Target Server Memory(KB). Total and Target values should be equal. When a reboot occurs though, it'll take a little bit to catch up. Outside of reboots, Target should never be higher than Total. Target is how much SQL Server would like to use and Total is how much it currently has.

Tara

Hi Tara,
Thank u very much for your help.
The value of the following two object:
(SQLServer:MemoryManager\Target Server Memory(KB),
SQLServer:MemoryManager\Target Server Memory(KB).)
are constantely equal to each other. They reach a maximum value of 1509216 kilobytes is equal to 1.4GB.My Windows Server has 1.5 GB. is this een indication for using extra RAM memory?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-14 : 12:39:44

I don't see any indication that you need to add more memory at this time.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources