Q: How to understand Cache hit ratio?
Many a times I've seen people asking if a Buffer cache
hit ratio of 95 or 97% a normal behaviour. Well, what you are seeing is normal
behavior. As more users access SQL Server, SQL Server will increase the buffer
cache as necessary to ensure a high hit ratio. And this behaviour is by design
in SQL Sevrer 2000. If SQL Server does not need the RAM, then it will
give it up to the operating system. But that would happen only when the OS
askes for the same. The OS reseves the memory till someone asks for that.
This of course assumes you are using the default SQL
Server 2000 memory setting of "Dynamically configure SQL Server memory"
. Then this would be the behaviour. If your buffer cache ratio is over 90%, you
can be fairly sure that SQL Server has all the RAM it needs to run as
efficiently as it can. On the contrary if you were to allocate a staic memory
value then this is going to be allocated statically and even when the OS needs
some it is not released ... Hence kepping a Dynamic configuration can be
benificial most of the cases.
Moreover it is also recommended that you reserve the
Servers activity to SQL Server use only, i.e. try to use the SQL Server as a
dedicated server rather than just using the same for multiple activities.