Buffer Pool and Buffer Cache Hit Ratio | SQL Server

Most of us often get confused around the terms SQL Server Buffer Cache and Buffer Cache Hit Ratio. In this post we will see and learn more detailed about these

What is the SQL Server Buffer Cache?

The Buffer Cache (sometimes called the Data Cache) in SQL Server is the memory that allows you to query frequently accessed data quickly. When data is written to or read from a SQL Server database, the buffer manager copies it into the buffer cache (an area of the SQL Server Buffer Pool). When it’s full, older or less frequently used data pages are moved to the hard disk.

Read more How to find out what's in Buffer Cache, what's consuming the most memory | SQL Server

What is Buffer Cache Hit Ratio?

The performance measure buffer cache hit ratio expresses as a percentage, how often a request for a database data page, can be served from the Buffer Pool. The alternative action is for SQL Server to have to fetch the data page from disk.

So for example, if you have a 500MB database, a server with 4GB and SQL Server is configured to use all available memory, you will have a very good Buffer Cache Hit ratio of 99% or above because your entire database can easily fit into memory.

How to determine the Buffer Cache Hit Ratio of your server?

Use Windows Performance Monitor tool and follow below steps to determine the Buffer Cache Hit Ratio.

1. Just go to: Start > Programs > Administrative Tools > Performance

2. Right click the graph and choose: Add Counters.

3. Select Performance object: SQLServer:Buffer Manager

4. Add: Buffer Cache Hit Ratio.

Windows Performance Monitor – Buffer Cache Hit Ratio

How to get more detailed information about SQL Servers overall memory usage?

In order to get a detailed report of SQL Servers memory usage execute the DBCC statement: DBCC MEMORYSTATUS

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .