Buffer cache hit ratio in SQL Server
Well, many times you might have come across the error or warning saying, that buffer cache hit ratio is low, less, below some percentage etc.
So what is Buffer Cache Hit Ratio?
Well, its pool of memory pages into which data pages are read. In other words, the data pages which are read from Disk are stored in Memory(Cache) to provide data faster. When a Data Read request is submitted, what SQL Engine does is it will go to disk retrieve the data from disk and place it in Memory(Cache) and return back the requested data. How this helps? When the request to same data pages is sent next time, there is no need to go to Disk again, it just reads from Cache and returns the data making data retrival faster and there by less IO Operations. So what are the impact when my buffer cache hit ratio is low, how does it impact performance.
Lets us take an example. Say that you see that the Buffer Cache hit ratio(query is below to find the buffer cache hit ratio) is around 95% when you request data. This mean 95 times out of 100 requests, SQL was able to provide data from Memory(cache) and only 5 times it had to go to Disk.
Buffer Cache Hit Ratio should be on 97%-100% level always. if its low for some moments that is fine. But if you see continously Buffer Cache Hit Ratio below 97-95% then definately this server needs more memory. Low buffer cache results in High IO operations and thus leading to low performance.
How to find out the Buffer Cache Hit Ratio? you can get it here: T-SQL query To Get Buffer Cache Hit Ratio in SQL Server