“Luft ablassen!”: Buffer Pool Fragmentierung beim SQL Server erkennen

Bei Datenbanksystemen hat die Größe des Hauptspeichers einen signifikanten Einfluss auf die Performance. Das Ziel ist es, möglichst viele relevante Daten im Speicher zu halten. Aufgrund der Struktur des Speichers kann es vorkommen, dass ein Teil des Speichers quasi „Luft“ enthält, die wesentlich sinnvoller genutzt werden könnte.

Der Microsoft SQL Server bietet zum Glück eine gute Quelle, um dieser Platzverschwendung auf die Spur zu kommen und entsprechend reagieren zu können. Aber bisher wird sie viel zu selten genutzt. Das möchte ich ändern.

Zum Hintergrund: Was ist denn eigentlich Fragmentierung?

Zu Beginn müssen wir ein paar Begriffe klären und vor allem mit alten Mythen aufräumen. Den Begriff Fragmentierung haben sicher viele schon mal gehört. Auf vielen Datenbanksystemen laufen regelmäßig Prozesse, um dieser Fragmentierung entgegenzuwirken: die sogenannte Defragmentierung.

Ich werde im Folgenden vor allem über Indizes sprechen, das Gleiche gilt aber fast analog für Tabellen. Vor allem beim SQL Server, wo die meisten Tabellen letztendlich in Form eines gruppierten Index gespeichert werden.

Wird ein Index neu erstellt, so liegen alle Seiten zum einen nah beieinander und zum anderen in der „richtigen“ Reihenfolge vor. Zudem sind die Seiten (Pages) fast alle komplett gefüllt. Der Speicherplatz wird optimal ausgenutzt. Es gibt keine „Luft“ in den Seiten. Im Laufe der Zeit werden jedoch durch das Hinzufügen, Ändern und Löschen von Datensätzen neue Seiten für den Index benötigt und diese liegen dann in aller Regel nicht mehr in räumlicher Nähe zu den bisherigen Seiten. Zudem sind die Seiten nicht mehr alle komplett gefüllt. Der Index beginnt zu fragmentieren.

An dieser Stelle müssen wir zwei Arten der Fragmentierung unterschieden: Externe und interne Fragmentierung. Externe Fragmentierung bezieht sich auf die Lage der Seiten, die interne Fragmentierung auf den Füllgrad der Seiten.

Externe Fragmentierung ist nicht relevant

Externe Fragmentierung entsteht fast ausschließlich durch Seitenteilungen (Page Splits). Diese sind notwendig, wenn neue Einträge zwischen die bisherigen Einträge eines Index geschrieben werden müssen, auf der entsprechenden Seite aber nicht mehr genug Platz ist. Die neuen Seiten liegen dann nicht mehr in der Nähe der bisherigen Seiten. Beim sequenziellen Lesen des Index entstehen Sprünge, die als externe Fragmentierung bezeichnet werden.

Der Fokus auf die externe Fragmentierung stammt aus einer Zeit, als Daten auf sich drehenden Scheiben gespeichert wurden und ein sich hin und her bewegender Arm einige Zeit brauchte, um an die passende Stelle zu fahren. Damals war es wichtig und richtig, Daten möglichst in räumlicher Nähe zueinander zu speichern, um möglichst schnell darauf zugreifen zu können.

Mit modernen Speichersystemen wirkt sich externe Fragmentierung nur in sehr seltenen Fällen in einem relevanten Maß auf die Performance aus. Leider bezieht sich der Wert der Spalte „avg_fragmentation_in_percent“ in der häufig zur Analyse genutzten Systemfunktion „sys.dm_db_index_physical_stats“ nur auf die externe Fragmentierung. Defragmentierungen auf Basis dieses Wertes sind also in vielen Fällen nicht notwendig und erzeugen Aufwand, ohne relevanten Nutzen zu generieren. Da die meisten zur Defragmentierung eingesetzten Tools ausschließlich diesen Wert betrachten, ist deren Einsatz aus meiner Sicht unbedingt kritisch zu hinterfragen. 

Interne Fragmentierung ist relevant – die „Luft" muss raus

Interne Fragmentierung betrachtet nur die einzelnen Seiten und die darauf gespeicherten Daten, unabhängig von der Lage der Seiten zueinander. Der Begriff „hohe interne Fragmentierung“ ist somit nur ein Synonym für „niedriger Füllgrad der Seiten“.

Auch diese Art der Fragmentierung entsteht bei den schon angesprochenen Seitenteilungen, denn in den meisten Fällen sind die so entstehenden Seiten nur zu etwa der Hälfte gefüllt. Allerdings entsteht interne Fragmentierung auch zum Beispiel beim Löschen von Datensätzen. Gerade wenn aus Tabellen regelmäßig historische Daten entfernt werden, können die Indizes von interner Fragmentierung betroffen sein.

Der Fokus sollte daher vor allem auf der internen Fragmentierung liegen. Auch dieser Wert wird in der angesprochenen Systemfunktion ausgegeben. Der Name der Spalte lautet „avg_page_space_used_in_percent“. Allerdings muss dazu der Modus „SAMPLED“ oder „DETAILED“ verwendet werden. Dieser führt leider dazu, dass mehr Seiten zur Analyse in den Hauptspeicher geladen werden müssen als dies im Modus „LIMITED“ notwendig ist. Vor allem werden Seiten nur zur Analyse geladen, die von der Applikation gerade nicht benötigt werden. Somit werden die wirklich benötigten Seiten über diesen Prozess aus dem Hauptspeicher verdrängt. Gerade für eine regelmäßige Analyse ist das aus meiner Sicht nicht geeignet.

Als Alternative empfehle ich die Systemsicht „sys.dm_os_buffer_descriptors“, die für jede Seite im Hauptspeicher eine Zeile mit Informationen aus dem Header dieser Seite liefert. Unter anderem wird dort in der Spalte „free_space_in_bytes“ der für die Analyse benötigte freie Platz angegeben. Wir greifen also nur auf den Hauptspeicher zu und betrachten zudem nur diejenigen Seiten, die aktuell für die Applikation relevant sind oder dies zumindest vor einiger Zeit waren. Wir schauen also nicht auf die „Luft“ auf der Platte, sondern auf die bereits im Hauptspeicher befindliche „Luft“.

Analyse der gesamten Instanz

Die angesprochene Systemsicht lässt sich auf zwei Arten nutzen: global für die ganze Instanz oder speziell für eine Datenbank. Die Sicht über alle Datenbanken hinweg bietet einen ersten guten Überblick, wie sich der Hauptspeicher auf die einzelnen Datenbanken aufteilt. Eine Analyse auf Basis einzelner Indizes ist allerdings nur pro Datenbank möglich, da mehrere datenbankspezifische Systemsichten mit berücksichtigt werden müssen, um alle benötigten Informationen darstellen zu können.

Die folgenden Abfragen sollen Ihnen einige Möglichkeiten zur Analyse aufzeigen. Filterungen und Sortierungen der Abfragen müssen je nach konkreter Fragestellung und Situation angepasst werden.

Die erste Abfrage listet für alle Benutzerdatenbanken, die Anzahl der Seiten, den im Hauptspeicher belegten Platz sowie den darin enthaltenen freien Platz („Luft“) auf. 

SELECT database_id
     , DB_NAME(database_id) AS database_name
     , COUNT(*) AS pages
     , COUNT(*)*8/1024 AS mb_total
     , SUM(CAST(free_space_in_bytes as bigint))/1024/1024 AS mb_free
  FROM sys.dm_os_buffer_descriptors
 WHERE database_id BETWEEN 5 and 32760
 GROUP BY database_id 
 ORDER BY SUM(CAST(free_space_in_bytes as bigint)) DESC;
 

Die zweite Abfrage geht einen Schritt weiter und unterteilt nicht nur nach Datenbanken, sondern auch nach dem Typ der Seite. Dabei wird der Fokus auf die für das Thema Fragmentierung relevanten Typen gelegt: Datenseiten und Indexseiten. Bei den Indexseiten wird zudem nach der vor allem relevanten untersten Ebene sowie den Seiten des darüber liegenden Baums unterschieden. Zudem werden nur Gruppen mit mehr als 100 MB an freiem Platz angezeigt. Das sorgt gerade bei Instanzen mit vielen Datenbanken für eine bessere Übersicht.

WITH data AS (
  SELECT database_id
       , DB_NAME(database_id) AS database_name
       , CASE
           WHEN page_type = 'DATA_PAGE'
           THEN 'DATA_PAGE'
           WHEN page_type = 'INDEX_PAGE'
            AND page_level = 0
           THEN 'INDEX_PAGE_leaf'
           ELSE 'INDEX_PAGE_non_leaf'
         END AS page_type_and_level
       , CAST(free_space_in_bytes AS bigint) AS free_space_in_bytes
    FROM sys.dm_os_buffer_descriptors
   WHERE database_id BETWEEN 5 and 32760
     AND page_type IN ('DATA_PAGE', 'INDEX_PAGE')
)
SELECT database_id
     , database_name
     , page_type_and_level
     , COUNT(*) AS pages
     , COUNT(*)*8/1024 AS mb_total
     , SUM(free_space_in_bytes)/1024/1024 AS mb_free
  FROM data
 GROUP BY database_id
        , database_name
        , page_type_and_level
HAVING SUM(free_space_in_bytes)/1024/1024 > 100
 ORDER BY SUM(free_space_in_bytes) DESC;
 

So können wir uns im nächsten Schritt auf diejenigen Datenbanken fokussieren, in denen ein nennenswerter Effekt erzielt werden kann. 

Analyse einer einzelnen Datenbank

Für eine einzelne Datenbank können jetzt die Namen der betroffenen Tabellen und Indizes mit hinzugefügt werden. Die Abfrage muss dabei im Kontext der Datenbank ausgeführt werden.

Auch hier schränkt die folgende Abfrage die Rückgabe auf die relevanten Aspekte ein. Es werden nur Datenseiten sowie Indexseiten der untersten Ebene betrachtet. Analog zur entsprechenden Spalte in der Systemfunktion „sys.dm_db_index_physical_stats“ berechne ich den durchschnittlichen Füllfaktor der Seiten. Als Vergleichswert wird zudem der im Index konfigurierte Füllfaktor mit angegeben.

SELECT DB_ID() AS database_id
     , DB_NAME() AS database_name
     , o.name AS table_name
     , i.index_id
     , i.name AS index_name
     , i.type_desc AS index_type
     , CASE
         WHEN bd.page_type = 'DATA_PAGE'
         THEN 'DATA_PAGE'
         ELSE 'INDEX_PAGE_leaf'
       END AS page_type_and_level
     , COUNT(*) AS pages
     , COUNT(*)*8/1024 AS mb_total
     , SUM(CAST(bd.free_space_in_bytes AS bigint))/1024/1024 AS mb_free
     , AVG(CAST(bd.free_space_in_bytes AS bigint)) AS avg_free_space_in_bytes
     , (8096-AVG(CAST(bd.free_space_in_bytes AS bigint)))*100/8096 AS avg_page_space_used_in_percent
     , CASE 
         WHEN i.fill_factor = 0
         THEN 100 
         ELSE i.fill_factor
       END AS index_fill_factor
  FROM sys.dm_os_buffer_descriptors AS bd
  JOIN sys.allocation_units AS au ON bd.allocation_unit_id = au.allocation_unit_id
  JOIN sys.partitions AS p ON au.container_id = p.hobt_id
  JOIN sys.objects AS o ON p.object_id = o.object_id
  JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
 WHERE bd.database_id = DB_ID()
   AND bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE')
   AND bd.page_level = 0
   AND au.type_desc = 'IN_ROW_DATA'
   AND o.is_ms_shipped = 0
 GROUP BY o.name
        , i.index_id
        , i.name
        , i.type_desc
        , i.fill_factor
        , bd.page_type
 ORDER BY SUM(CAST(bd.free_space_in_bytes AS bigint)) DESC;
 

Der erste Blick sollte hier auf die Spalte „mb_free“ fallen. Eine Reorganisation oder ein Neuaufbau eines Index sollte immer zu einer signifikanten Reduzierung der „Luft“ führen. Dabei ist jedoch stets auch das Verhältnis von aktuellem Füllfaktor zu dem im Index konfigurierten Füllfaktor zu beachten, denn bei einer nur sehr geringen Abweichung würde sich auch nur ein geringer Effekt ergeben.

Wenn Sie Unterstützung bei der Interpretation der Werte benötigen, stehen wir Ihnen mit Rat und Tat zur Seite. Sprechen Sie uns gerne an.

Weitere Abfragen finden Sie hier in meinem Repository auf GitHub.  

Fazit

Durch die Wahl der richtigen Systemsicht lassen sich ressourcenschonend Informationen über die für die Performance relevante interne Fragmentierung sammeln. Darauf aufbauend lassen sich maßgeschneiderte Prozesse zur Reorganisation von Tabellen und Indizes entwickeln, die auch für sehr große Datenbanken funktionieren. Unnötige Reorganisationen werden so vermieden. 

Seminarempfehlung