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.
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.
Just go to: Start > Programs > Administrative Tools > Performance
Right click the graph and choose: Add Counters.
Select Performance object: SQLServer:Buffer Manager
Add: 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 .
Recent Posts
See AllThe tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to...
Comments