Q: How to understand Cache hit ratio?

Answer:

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.