Eine Anfrage im MSDN-Forum von Microsoft mit dem Titel “Issue in shrinking data file” ist der Grund für diesen Artikel. In der Anfrage ging es darum, dass der Fragesteller aus einer sehr großen Datenbank Unmengen von Datensätzen aus diversen Tabellen gelöscht hatte. Anschließend wollte er die Datenbankdatei verkleinern um so mehr Platz auf dem Storage zu schaffen. Jedoch ergaben Überprüfungen des konsumierten / allokierten Speichers, dass trotz des Löschens mehrerer Millionen Datensätze der Speicher nicht als “frei” gekennzeichnet wurde. Beim genaueren Lesen der Fragestellung kam einem Satz besondere Aufmerksamkeit zu Teil: “Also, I noticed that there is three huge tables in the db and these are non-clustered index.”. Damit war eigentlich schon klar, was das Problem des Fragestellers war. Dieser Artikel beschreibt die technischen Hintergründe, warum ein DELETE-Befehl nicht automatisch den allokierten Speicher freigibt.
Inhaltsverzeichnis
Was ist ein HEAP?
Als HEAP wird eine Tabelle bezeichnet, die nicht nach Ordnungskriterien sortiert wird. Das bedeutet, dass ein HEAP die Datensätze immer da abspeichert, wo ausreichend Platz in einer Datenseite ist. Ein HEAP genießt den großen Vorteil, dass er – anders als ein Clustered Index oder Nonclustered Index – keine B-Tree-Struktur zur Verwaltung benötigt. Ein HEAP wird ausschließlich durch Datenseiten (Leafs) repräsentiert, die durch eine oder mehrere IAM-Datenseiten (Index Allocation Map) verwaltet werden.
Die obige Abbildung zeigt die Organisationsstruktur eines HEAPS. Die Datenseiten (110 – 152) haben keinen direkten Bezug zueinander und die übergeordnete IAM-Datenseite verwaltet die der Tabelle zugehörigen Datenseiten. Eine IAM-Datenseite kann immer nur EIN Datenbankobjekt (Tabelle, Indexed View) verwalten!
Testumgebung
Um die Fragestellung / Problemstellung des Autors der obigen Anfrage zu reproduzieren, wird in einer Testdatenbank eine Tabelle [dbo].[demo_table] angelegt. Diese Tabelle kann pro Datenseite maximal einen Datensatz speichern, da die Datensatzlänge 8.004 Bytes beträgt. Anschließend werden 10.000 Datensätze in die zuvor angelegte Tabelle eingetragen um die zugewiesene Speicherzuordnung auszuwerten.
-- Create the demo table
CREATE TABLE dbo.demo_table
(
Id INT NOT NULL IDENTITY (1, 1),
C1 CHAR(8000) NOT NULL DEFAULT ('only a filler')
);
GO
-- and insert 10.000 records into this table
SET NOCOUNT ON;
GO
INSERT INTO dbo.demo_table DEFAULT VALUES;
GO 10000
-- show the number of allocated data pages after the insert
SELECT DDPS.index_id,
DDPS.in_row_data_page_count,
DDPS.in_row_used_page_count,
DDPS.in_row_reserved_page_count,
DDPS.row_count
FROM sys.dm_db_partition_stats AS DDPS
WHERE DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO
Wie die Abbildung zeigt, wurden 10.000 Datensätze ([row_count]) in die Tabelle eingetragen. Diese 10.000 Datensätze belegen insgesamt 10.000 Datenseiten ([in_row_data_page_count]).
Löschen von Datensätzen
Nachdem die Datensätze eingetragen wurden, werden im nächsten Schritt 1.000 Datensätze aus der Tabelle gelöscht und erneut der belegte Speicher überprüft. Zum Löschen von Datensätzen wird der DELETE-Befehl im “klassischen” Stil ohne weitere Hints verwendet
-- Delete the last 1,000 records...
DELETE dbo.demo_table WHERE Id >= 9001;
GO
-- and check the allocated space again!
SELECT DDPS.index_id,
DDPS.in_row_data_page_count,
DDPS.in_row_used_page_count,
DDPS.in_row_reserved_page_count,
DDPS.row_count
FROM sys.dm_db_partition_stats AS DDPS
WHERE DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO
Das Ergebnis ist “überraschend”; obwohl 1.000 Datensätze gelöscht wurden, ist der allokierte Speicher nicht freigegeben worden.
SELECT in HEAP
Die Ursache für dieses Verhalten liegt in der Art und Weise, wie Microsoft SQL Server beim Lesen von Daten aus einem Heap vorgeht. Da ein HEAP kein Ordnungskriterium besitzt, wird auch mit eingesetztem Prädikat immer ein Table Scan ausgeführt; es muss also immer die komplette Tabelle gelesen werden.
SELECT * FROM dbo.demo_table WHERE Id = 10 OPTION (QUERYTRACEON 9130);
Der Lesevorgang in einem HEAP liest zunächst die IAM-Datenseite des betroffenen Objekts. Die IAM-Datenseite muss gelesen werden, da ansonsten Microsoft SQL Server nicht weiß, welche Datenseiten zum Objekt gehören. In einem Clustered Index / Nonclustered Index ist das nicht notwendig, da die Datenseiten Verknüpfungen zu den nachfolgenden / vorherigen Datenseiten besitzen! Hat Microsoft SQL Server die IAM-Datenseite gelesen, kann mit dem Einlesen der allgemeinen Datenseiten begonnen werden. Genau hier liegt aber der “Fehler des Designs”; da der DELETE-Vorgang nicht mit einer Tabellensperre einhergeht, muss Microsoft SQL Server alle Datenseiten auch weiterhin bereitstellen, da ansonsten ein zweiter Vorgang, der einen SELECT auf die Tabelle durchführt, die IAM-Datenseite bereits gelesen haben könnte und somit die zu lesenden Datenseiten bereits kennt. Würde Microsoft SQL Server nun bei einem DELETE-Vorgang diese Datenseiten aus der Zuordnung entfernen, würde der zweite – Lese – Vorgang eine Datenseite anfordern, die nicht mehr existiert.
Dieses „Problem“ ist auch offiziell bei Microsoft bekannt und kann hier nachgelesen werden: https://support.microsoft.com/en-us/kb/913399. Entgegen der Auffassung von Microsoft, dass es sich um einen „bekannten Bug“ handelt, stellt sich dieses Verhalten eher als „Feature“ dar!
Lösung
Um das Problem der Freigabe von allokiertem Datenspeicher zu lösen, gibt es zwei Alternativen:
Neuaufbau der Tabelle
Die erste Möglichkeit besteht darin, die Tabelle selbst mittels REBUILD neu aufzubauen. Hierzu benötigt Microsoft SQL Server – kurzfristig – eine exklusive Sperre auf die Tabelle, um die “alte” Tabelle durch die neu aufgebaute Tabelle zu ersetzen. Zuvor werden die Datenbestände in die “neue” Tabelle transferiert und somit eine neue Struktur geschaffen.
-- Rebuild the table
ALTER TABLE dbo.demo_table REBUILD;
GO
-- and check the allocated space again!
SELECT DDPS.index_id,
DDPS.in_row_data_page_count,
DDPS.in_row_used_page_count,
DDPS.in_row_reserved_page_count,
DDPS.row_count
FROM sys.dm_db_partition_stats AS DDPS
WHERE DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO
Nach dem REBUILD sind die “leeren” Datenseiten gelöscht worden und nur noch Datenseiten mit Datensätzen sind vorhanden.
Löschen von Datensätzen mit TABLOCK-Hinweis
Eine andere – elegantere – Möglichkeit besteht darin, den Löschvorgang mit einer Tabellensperre zu verbinden. Da ein möglicher SELECT-Vorgang die IAM-Datenseite lesen muss, kann durch das Sperren der Tabelle dieser Zugriff ausgeschlossen werden. Ein möglicher SELECT-Befehl kann keine IS-Sperre (Intent Shared) auf das Tabellenobjekt legen, da während des Löschvorgangs eine X-Sperre (Exklusiv) auf dem Tabellenobjekt liegt. Somit wird der SELECT-Befehl solange gesperrt, bis der Löschvorgang abgeschlossen ist. Da in dieser Situation sichergestellt ist, dass niemand lesend auf die Tabelle zugreift, kann Microsoft SQL Server gefahrlos die leeren Datenseiten entfernen!
-- Delete 2,000 records with a TABLOCK hint
DELETE dbo.demo_table WITH (TABLOCK) WHERE Id >= 7001;
GO
-- and check the allocated space again!
SELECT DDPS.index_id,
DDPS.in_row_data_page_count,
DDPS.in_row_used_page_count,
DDPS.in_row_reserved_page_count,
DDPS.row_count
FROM sys.dm_db_partition_stats AS DDPS
WHERE DDPS.object_id = OBJECT_ID(N'dbo.demo_table', N'U');
GO
Zusammenfassung
HEAPS haben eine andere interne Struktur, die viele Vor- aber auch Nachteile besitzt. Viele Aktionen, die als “selbstverständlich” gelten, bergen Tücken. Wenn man aber die Konzepte hinter den Objekten versteht, tappt man nicht in diese Fallen.
Herzlichen Dank fürs Lesen und allen Lesern ein frohes neues Jahr!
