SQL Server Buffer Cache

What is the SQL Server Buffer Cache and how does it affect the performance of, for example, Microsoft Dynamics ERP systems.

In SQL Servers, the "Buffer Cache" is the memory used by applications such as Dynamics 365 Business Central to quickly retrieve frequently accessed data. When data is written to or read from a SQL Server database, the Buffer Manager copies it to the "Buffer Cache" (also referred to as Buffer Pool - buffer memory of a database management system). When buffer pool is full, older or less frequently used data pages ("data pages") are moved to disk.

Why should you monitor the buffer cache?

Memory usage can have a significant impact on performance. If there is not enough memory, data pages are often deleted from the buffer cache. This slows down queries because SQL Server must go to disk to find the data page. Then the server must restore the data page to the buffer cache and then read the page before returning the query result.

There are many reasons why queries execute slowly. But if you want to rule out memory problems, take a look at what's going on in the buffer cache. A look inside shows which database, table, or index is loading memory and putting pressure on the buffer.

Use the following SQL query to determine which database is using the most memory (for a Dynamics 365 Business Central system, this should always be the "live" database):

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;

Run this query on the database you want to examine to identify the table or index that occupies the most memory.

USE [your database]

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 is helpful to check databases and indexes for memory overuse, tracking buffer cache metrics is the best way to identify and troubleshoot performance issues.

Here are the top five metrics to monitor to improve memory-related performance issues (SQL Server Buffer Cache):

In V8 Search XE you can find a query to measure the SQL Server buffer cache at:

An error-free SQL Server buffer cache is an important component of optimizing SQL Server query speed. Although memory problems are just one of several factors that can slow down query responses, they are relatively easy to identify and troubleshoot.
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.

We will be happy to personally answer any further questions you may have on this topic.
Your dynamicsproject.com Team