John Sansom

SQL Server DBA Blog

The SQL Server Buffer Pool and Buffer Cache Hit Ratio

by 3 Comments

There seems to be some confusion surrounding Buffer Cache Hit Ratio and the SQL Server Buffer Cache. Having answered several queries concerning this over the last few weeks on a variety of forums I thought I would post here in order set the record straight.

What is the SQL Server Buffer Cache?

The Buffer Cache (sometimes called the Data Cache) is an area of the SQL Server Buffer Pool (which in turn is part of the overall SQL Server Process space) and in simple terms is the amount of your database data (data pages) that are stored in memory.

You can find out how many data pages are currently stored in memory for each of your databases by referring to my post How Much Memory is Each SQL Server Database Using?.

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 can I determine the Buffer Cache Hit Ratio of my server?

In order to determine you Buffer Cache Hit Ratio you can use the Windows Performance Monitor tool.

  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.
Buffer Cache Hit Ratio

Windows Performanc Monitor – Biffer Cache Hot Ratio

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

In order to get a detailed report of SQL Servers memory usage I like to use the DBCC statement:

DBCC MEMORYSTATUS

Useful SQL Server Memory References

Can’t Find the Answer To Your Question?

If you have a query about SQL Server that you can’t find the answer to you’re welcome to ask me.