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:

1. Buffer Cache Hit Ratio (Puffer-Cache-Trefferquote)


  • Diese Metrik zeigt, wie SQL Server den Puffercache verwendet
  • Die Trefferquote gibt den Prozentsatz der Seitenanforderungen an, die von Datenseiten aus dem Puffercache ausgeführt wurden, im Vergleich zu allen Datenseitenanforderungen
  • Seiten, die nicht im Puffercache gefunden werden, werden von der Festplatte gelesen, was viel langsamer ist
  • Das ideale Puffer-Cache-Verhältnis beträgt 100 (d.h. der SQL Server liest alle Seiten aus dem Puffer-Cache und keine von der Festplatte)
  • Der empfohlene Puffer-Cache-Wert ist größer als 90

2. Page Life Expectancy (PLE – Lebenserwartung der Seite)


  • Die Seitenlebenserwartung misst, wie lange (in Sekunden) eine Datenseite im Puffercache verbleibt
  • Je länger die PLE ist, desto besser ist die Chance, dass SQL Server die Seiten aus dem Puffercache liest und nicht auf die Festplatte gehen muss
  • Wenn nicht genügend Speicher vorhanden ist, werden Datenseiten häufiger aus dem Puffercache gelöscht, um Speicherplatz für neue Seiten freizugeben
  • In der Vergangenheit betrug ein „normaler“ PLE-Wert 300 Sekunden, wenn Systeme viel weniger Speicher hatten als Heutzutage
  • Für neuere SQL Server wird folgende Formel verwendet, um “gute” PLE zu bestimmen: Seitenlebensdauer = 300 Sekunden für jeweils 4 GB RAM auf Ihrem Server
  • Die PLE sollte stabil bleiben, wenn er über die Zeit überwacht wird
  • Schnelle, häufige Abnahmen weisen auf Speicherprobleme hin
  • Ein Abfall von mehr als 50% sollte sofort untersucht werden

3. Page reads / Sec. (Serverebene)


  • Diese Metrik zeigt an, wie viele physische Lesevorgänge (d.h. Lesevorgänge von der Festplatte) in einer Sekunde in allen Datenbanken einer Instanz aufgetreten sind
  • Physische Lesevorgänge sind teuer und langsam
  • Verringern Sie physische Lesevorgänge, indem Sie einen größeren Datencache, intelligente Indizes und effizientere Abfragen verwenden oder das Datenbankdesign ändern
  • Der empfohlene Wert liegt unter 90
  • Ein Wert über 90 weist auf unzureichende Speicher- und Indizierungsprobleme hin

4. Page Writes/Sec


  • Diese Metrik gibt an, wie oft Seiten in einer Sekunde auf Serverebene auf die Festplatte geschrieben wurden
  • Der empfohlene Wert liegt unter 90

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


  • Seiteneingabe / Sek. Ist die Anzahl der Seiten, die pro Sekunde von der Festplatte eingelegt werden
  • Die Seitenausgabe / Sek. Ist die Anzahl der Seiten, die pro Sekunde auf die Festplatte geschrieben werden, um Platz im Puffercache zu schaffen
  • Seiten / Sek. Ist die Summe aus Seiteneingabe / Sek. Und Seitenausgabe / Sek
  • Wenn der Wert für Seiten / Sek. durchweg mehr als 50 beträgt, sind zusätzliche Untersuchungen erforderlich

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

V8 Search SQL Server Buffer Cache Navi

V8 Search SQL Server Buffer Cache

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