Wer mich kennt, weiß, dass ich ein großer Fan von HEAPS bin. HEAPS zeigen gegenüber gruppierten Indexen bei bestimmten Workloads bessere Leistungsverhalten. Neben den vielen Vorteilen haben HEAPS auch Nachteile. Ein deutlicher Nachteil ist, dass HEAPS allokierte Datenseiten bei Löschoperationen nicht unmittelbar freigeben. Daraus ergibt sich – manchmal – die Erfordernis, festzustellen, wie viele Datenseiten in einem HEAP nicht gefüllt sind und somit durch einen REBUILD wieder freigegeben werden können.

Löschoperationen in HEAP

In einem vorherigen Artikel habe ich Löschoperationen detailliert beschrieben, wenn es sich dabei um einen HEAP handelt. Weitere Informationen dazu findet man im Artikel “HEAPS in Verbindung mit DELETE-Operationen” vom 12.08.2016. Ein HEAP gibt leere Datenseiten nicht frei, wenn nicht sichergestellt werden kann, dass anderen Prozessen der Zugriff auf die IAM-Seite(n) verweigert werden kann. Möchte man Datenseiten während einer Löschoperation in einem HEAP unmittelbar freigeben, muss die Löschoperation mit Hilfe eines TABLOCK-Hinweises exklusiv abgesichert sein.

Ermittlung der vorhandenen Datenseiten

Um festzustellen, welche Datenseiten von einem Objekt (Tabelle, Index) allokiert sind, gibt es seit Microsoft SQL Server 2012 die Systemfunktion [sys].[dm_db_database_page_allocations]. Wer mit einer älteren Version von Microsoft SQL Server arbeitet, kann mit Hilfe von DBCC IND einen Blick in Struktur der allokierten Datenseiten werfen. Jedoch werden mit DBCC IND keine Informationen zur Belegung der Datenseiten ausgegeben! Nachfolgend wird eine Tabelle [dbo].[messages] erstellt und mit ca. 12.000 Datensätzen gefüllt.

SELECT *
INTO  dbo.messages
FROM  sys.messages
WHERE language_id = 1033;
GO

Die Tabelle besitzt keine Indexe und ist ein einfacher HEAP.

PFS-Seite

HEAPS haben gegenüber einem gruppierten Index eine Besonderheit, die ausschließlich bei HEAPS auftritt; der Füllgrad einer Datenseite wird in der PFS-Datenseite gespeichert! Wenn neue Datensätze in einem HEAP gespeichert werden, muss in der PFS-Datenseite nach einer Datenseite gesucht werden, die ausreichend Platz für die Speicherung der Daten in der Tabelle bereitstellt. Sobald ein neuer Datensatz in die Tabelle eingetragen wird, wird im Anschluss die PFS-Seite mit einem prozentualen Wert aktualisiert, der beschreibt, zu wieviel Prozent die Datenseite gefüllt ist. Hierbei können nur 5 mögliche Prozentwerte gespeichert werden:

Prozentwert Beschreibung
NULL / 0% Die allokierte Datenseite ist leer
50% Die allokierte Datenseite ist mit 4.030 Bytes gefüllt.
80% Die allokierte Datenseite ist mit 6.424 Bytes gefüllt.
95% Die allokierte Datenseite ist mit ~7.629 Bytes gefüllt
100% Die Datenseite ist voll

Werden neue Datensätze in einem HEAP gespeichert, findet eine Aktualisierung der PFS-Seite nur dann statt, wenn der neue Datensatz einen der obigen Schwellwerte überschreitet. Speichert man in einer Tabelle z. B. Datensätze mit einer Gesamtlänge von 1.000 Bytes, so wird beim Eintragen des ersten Datensatzes die PFS-Seite mit 50% aktualisiert. Wird ein zweiter Datensatz eingetragen, ist eine weitere Aktualisierung nicht notwendig, da die Gesamtmenge immer noch unter 50% liegt. Erst beim 5. Datensatz wird erneut die PFS-Seite aktualisiert und der Füllgrad wird mit 80% angegeben.

Ermittlung des Füllgrads

Der Füllgrad ist ein wesentlicher Bestandteil einer jeden Datenseite in einem HEAP. Mit Hilfe der oben genannten Systemview kann ermittelt werden, zu wieviel Prozent eine Datenseite gefüllt ist. Nachdem die Testtabelle erstmalig befüllt wurde, ist jede Datenseite vollständig gefüllt!

SELECT allocated_page_page_id,
       page_free_space_percent
FROM   sys.dm_db_database_page_allocations
       (
         DB_ID(),
         OBJECT_ID(N'dbo.messages', N'U'),
         0,
         NULL,
         N'DETAILED'
       )
WHERE  page_level = 0
       AND is_iam_page = 0;
GO

image

Sobald Datensätze aus der Tabelle gelöscht werden, werden die Einträge in der PFS-Seite aktualisiert!

DELETE dbo.messages
WHERE message_id %2 = 0;
GO

Da die Tabelle nicht exklusiv gesperrt ist, werden allokierte Datenseiten nicht wieder freigegeben. Nach dem Löschvorgang stellt sich die Datenverteilung prozentual wie folgt dar:

image

Es ist erkennbar, dass die PFS-Seite für alle betroffenen Datenseiten des HEAP aktualisiert wurde. Mit dem nächsten Löschbefehl werden alle Datensätze aus den ersten 2 Datenseiten gelöscht:

DELETE TOP (40) 
FROM dbo.messages;
GO

image

Wenn eine Datenseite vollständig leer ist, wird NULL als Eintrag gespeichert. Die Datenseite besitzt keine weiteren Daten mehr; verbleibt aber allokiert. Mit der folgenden Abfrage kann man feststellen, wie viele Datenseiten leer sind.

SELECT page_free_space_percent,
       COUNT_BIG(*) AS num_of_pages,
       SUM
       (
         CASE WHEN page_free_space_percent IS NULL
              THEN 0
              ELSE CAST(page_free_space_percent AS INT)
         END * 8060.0 / 100
       ) AS allocated_bytes
FROM   sys.dm_db_database_page_allocations
       (
         DB_ID(),
         OBJECT_ID(N'dbo.messages', N'U'),
         0,
         NULL,
         N'DETAILED'
       )
WHERE  page_level = 0
       AND is_iam_page = 0
GROUP BY
       page_free_space_percent;
GO

image

Die Auswertung ist nicht zu 100% akkurat, da sie – auf Grund der Speicherverfahren für HEAPS – einige Schwächen zeigt:

  • Die Anzahl der Pages mit dem Eintrag NULL kann bis zu 7 weitere LEERE Datenseiten anzeigen. Das hängt damit zusammen, dass Microsoft SQL Server ab der 9 allokierten Datenseite keine MIXED EXTENTS mehr verwendet, sondern UNIFORM EXTENTS. Daraus resultiert, dass bei Allokation von mindestens einer Datenseite in einem UNIFORM EXTENT 7 weitere Datenseiten allokiert werden, die jedoch noch nicht allokiert sind.
  • Die Prozentzahlen täuschen über den wahren Füllgrad hinweg. Ein Füllgrad von z. B. 50% besagt lediglich, dass eine Datenmenge von 1 Byte bis 4.030 Bytes auf der Datenseite gespeichert wird. Um die genaue Datenmenge zu evaluieren, muss JEDE Datenseite explizt untersucht werden. Das ist für eine kleine Tabelle noch vertretbar; handelt es sich jedoch um mehrere Millionen Datenseiten, dauert die Evaluierung zu lang.

Herzlichen Dank fürs Lesen!