SQL Server Buffer Cache

Was ist der SQL Server Buffer Cache und wie wirkt er sich auf die Leistung z.B. von Microsoft Dynamics ERP Systemen aus.

In SQL Servern ist der „Buffer Cache“ der Speicher, mit dem Anwendungen wie Dynamics 365 Business Central häufig aufgerufene Daten schnell abfragen kann. Wenn Daten in eine SQL Server-Datenbank geschrieben oder aus dieser gelesen werden, kopiert der Buffer Manager sie in den „Buffer Cache“ (auch als Pufferpool – Pufferspeicher eines Datenbankmanagementsystems bezeichnet). Wenn Pufferpool voll ist, werden ältere oder weniger häufig verwendete Datenseiten („Data Pages“) auf die Festplatte verschoben.

Warum sollten Sie den Buffer Cache überwachen?

Die Speichernutzung kann einen erheblichen Einfluss auf die Leistung haben. Wenn nicht genügend Speicher vorhanden ist, werden Datenseiten häufig aus dem Puffercache gelöscht. Dies verlangsamt Abfragen, da SQL Server auf die Festplatte gehen muss, um die Datenseite zu finden. Danach muss der Server die Datenseite im Puffercache wiederherstellen und dann die Seite lesen, bevor das Abfrageergebniss zurückgegeben werden kann.

Es gibt viele Gründe, warum Abfragen langsam ausgeführt werden. Wenn Sie jedoch Speicherprobleme ausschließen möchten, schauen Sie sich an, was im Puffercache vor sich geht. Ein Blick hinein zeigt, welche Datenbank, Tabelle oder welcher Index den Speicher belastet und Druck auf den Puffer ausübt.

Verwenden Sie die folgende SQL Abfrage, um festzustellen, welche Datenbank den meisten Speicher belegt (bei einem Dynamics 365 Business Central System sollte dies immer die „Live“ Datenbank sein):

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;

Führen Sie diese Abfrage in der Datenbank aus, die Sie untersuchen möchten, um die Tabelle oder den Index zu identifizieren, die bzw. der den meisten Speicher belegt.

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;

Verwalten Sie den Speicher mit Metriken

Obwohl es hilfreich ist, Datenbanken und Indizes auf Überbeanspruchung des Speichers zu überprüfen, ist die Verfolgung von Puffer-Cache-Metriken der beste Weg, um Leistungsprobleme zu identifizieren und zu beheben.

Hier sind die fünf wichtigsten zu überwachenden Metriken, um speicherbezogene Leistungsprobleme (SQL Server Buffer Cache) zu verbessern:

In V8 Search XE finden die eine Abfrage zur Messung des SQL Server Buffer Cache unter:

Ein fehlerfreier SQL Server Buffer Cache ist eine wichtige Komponente bei der Optimierung der SQL Server-Abfragegeschwindigkeit. Obwohl Speicherprobleme nur einer von mehreren Faktoren sind, die die Abfrageantworten verlangsamen können, sind sie relativ einfach zu identifizieren und zu beheben.
Das Verfolgen dieser fünf Schlüsselmetriken kann Ihnen dabei helfen, Datenseiten länger im Pufferpool zu halten, sodass SQL Server keine Zeit mit der Suche auf der Festplatte verschwenden muss, bevor Abfrageergebnisse zurückgegeben werden.

Gerne beantworten wir Ihnen persönlich weitergehende Fragen zu diesem Thema.
Ihr dynamicsproject.com Team