Buffer cache: What is it and how does it impact database performance?

In SQL Server, the buffer cache 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 (aka the buffer pool). When it’s full, older or less frequently used data pages are moved to the hard disk.

Why do I need to monitor buffer cache?

Memory use can have a significant impact on performance. When there is insufficient memory, data pages get purged frequently from the buffer cache. This slows down queries because SQL Server has to go to the disk to find the data page, restore it to the buffer cache, and then read the page before it can return query results.

There are many reasons why queries start to run slowly. But if you want to rule out memory trouble, look at what is going on inside the buffer cache. A peek inside it will identify which database, table, or index is hogging memory and putting pressure on the buffer.

To see which database consumes the most memory, use the query:

Visibility into it all.

Monitor your databases and resolve performance issues quickly.

Try Foglight

SELECT
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name, COUNT(1)/128 AS megabytes_in_cache
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY megabytes_in_cache DESC;

To identify the table or index that consumes the most memory, run this query in the database you’d like to inspect:

SELECT COUNT(1)/128 AS megabytes_in_cache
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY megabytes_in_cache DESC;

Manage memory with metrics

Although it’s helpful to spot-check databases and indexes for overuse of memory, tracking buffer cache metrics is really the best way to identify and resolve performance issues caused by internal pressure on memory.

Here are the top five metrics to monitor to improve memory-related performance issues:

1. Buffer Cache Hit Ratio

• This metric shows how SQL Server utilizes buffer cache
• The hit ratio identifies the percent of page requests that were completed by data pages from the buffer cache versus all data page requests
• Pages that are not found in the buffer cache are read from the disk, which is much slower
• The ideal buffer cache ratio is 100 (i.e., SQL Server reads all pages from the buffer cache and none from the disk)
• The recommended buffer cache value is greater than 90

2. Page Life Expectancy (PLE)

• Page Life Expectancy measures how long (in seconds) a data page stays in the buffer cache
• The longer the PLE, the better the chance that SQL Server will read the pages from the buffer cache and not have to go to the disk
• If there isn’t enough memory, data pages are flushed from the buffer cache more often to free up the space for new pages
• Historically, when systems had far less memory than they do now, a “normal” PLE value was 300 seconds
• Today, a formula is used to determine “good” PLE: Page Life Expectancy = 300 seconds for every 4 GB of RAM on your server
• The PLE should remain stable if monitored over time
• Fast, frequent decreases indicate memory problems
• A drop of more than 50% should be investigated immediately

3. Page Reads/Sec (Server Level)

• This metric shows how many physical reads (i.e., reads from disk) occurred in one second across all databases on an instance
• Physical reads are expensive and slow
• Decrease physical reads by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design
• The recommended value is less than 90
• A value higher than 90 indicates insufficient memory and indexing issues

4. Page Writes/Sec

• This metric shows the number of times pages that were written to disk on the server level in one second
• The recommended value is less than 90

5. Pages Input/Sec and Pages Output/Sec (Memory Counters)

• Pages input/sec is the number of pages brought in from the disk every second
• Pages output/sec is the number of pages written to the disk every second to make room in the buffer cache
• Pages/sec is the sum of pages input/sec and pages output/sec
• If pages/sec value is consistently more than 50, additional investigation is needed

A healthy buffer cache is an important component in optimizing SQL Server query speed. Although memory issues are just one of several factors that can slow down query responses, they are fairly easy to identify and resolve. Tracking these five key metrics can help you keep data pages in the buffer pool longer so SQL Server doesn’t have to waste time searching the disk before returning query results.