In einem Projekt wurde den Entwicklern gesagt, dass man grundsätzlich mit Heaps arbeiten solle, da durch die Verwendung eines Clustered Index viele Deadlocks verursacht worden sein. Daraufhin hat man für fast alle Tabellen in der Datenbank die geclusterten Tabellen wieder zu Heaps konvertiert. Die Deadlocks sind laut Aussage vollkommen verschwunden – jedoch hat man ein paar Dinge nicht beachtet, die sich nun nachteilig auf die Performance auswirken; und es sind nicht fehlende Indexe gemeint!
Inhaltsverzeichnis
Einleitung
Ich persönlich favorisiere Heaps sofern es möglich ist; insbesondere DWH-Szenarien sind prädestiniert für Heaps. Meine Meinung über Heaps habe ich grundsätzlich überdacht, nachdem ich die Artikel “Unsinnige Defaults: Primärschlüssel als Clusterschlüssel” von Markus Wienand und Thomas Kejser “Clustered Index vs. Heap” gelesen habe. Grundsätzlich bieten Clustered Indexe in SQL Server sehr viele Vorteile (insbesondere in der Maintenance); jedoch sollte man nicht immer den “pauschalen” Empfehlungen folgen. Von daher empfehle ich meinen Kunden immer wieder mal, auch über die Alternative HEAP nachzudenken. Die Alternative muss aber – wie im vorliegenden Fall – gründlich überlegt sein und immer von drei Seiten betrachtet werden:
- Analyse der Workloads
- Analyse der SELECT-Statements
- Analyse der Maintenance (Indexfragmentierung, Forwarded Records, DELETE und UPDATE-Verhalten)
Wenn alle Bedingungen “passen”, steht der Verwendung von Heaps nichts mehr im Wege. Im Vorliegenden Fall hat man leider nur die Option 2 im Blick gehabt und dabei vollkommen ausgeblendet, dass in der betroffenen Tabelle sehr viele Aktualisierungs- und Löschvorgänge stattfinden.
Testumgebung
Für die aus dem obigen Fall entstandenen Probleme wird eine Tabelle verwendet, die pro Datenseite einen Datensatz speichert. Diese Tabelle wird mit 20.000 Datensätzen gefüllt.
-- Create a HEAP for the demo
CREATE TABLE dbo.demo_table
(
Id INT NOT NULL IDENTITY (1, 1),
C1 CHAR(8000) NOT NULL DEFAULT ('Das ist nur ein Test')
);
GO
-- Now we fill the table with 20,000 records
SET NOCOUNT ON;
GO
INSERT INTO dbo.demo_table WITH (TABLOCK) (C1)
SELECT TOP 20000
text
FROM sys.messages;
Sobald die Tabelle befüllt ist, sind insgesamt (inklusive IAM-Seite) 20.001 Datenseiten im Buffer Pool des SQL Servers.
-- what resource of the table dbo.demo_table are in the buffer pool now!
;WITH db_pages
AS
(
SELECT DDDPA.page_type,
DDDPA.allocated_page_file_id,
DDDPA.allocated_page_page_id,
DDDPA.page_level,
DDDPA.page_free_space_percent,
DDDPA.is_allocated
sys.dm_db_database_page_allocations
(
DB_ID(),
OBJECT_ID(N'dbo.demo_table', N'U'),
NULL,
NULL,
'DETAILED'
) AS DDDPA
)
SELECT DOBD.file_id,
DOBD.page_id,
DOBD.page_level,
DOBD.page_type,
DOBD.row_count,
DOBD.free_space_in_bytes,
DP.page_free_space_percent,
DP.is_allocated
FROM sys.dm_os_buffer_descriptors AS DOBD
INNER JOIN db_pages AS DP ON
(
DOBD.file_id = DP.allocated_page_file_id
AND DOBD.page_id = DP.allocated_page_page_id
AND DOBD.page_level = DP.page_level
)
WHERE DOBD.database_id = DB_ID()
ORDER BY
DP.page_type DESC,
DP.page_level DESC,
DOBD.page_id,
DOBD.file_id;
Jede Datenseite des Heaps ist nahezu vollständig gefüllt. Nun werden 1.000 Datensätze aus der Tabelle mittels DELETE gelöscht.
-- Now we delete half of the records SET ROWCOUNT 2000; DELETE dbo.demo_table WHERE Id % 2 = 0;
Die Analyse des Bufferpools zeigt das Dilemma beim Löschen von Datenseiten aus einem Heap…
Entgegen der Erwartung, dass leere Datenseiten automatisch wieder an die Datenbank zurückgegeben (deallokiert) werden, stellt sich die Situation so dar, dass die leeren Datenseiten weiterhin von der Tabelle allokiert sind und im Buffer Pool vorhanden sind. Jede zweite Seite aus dem gezeigten Beispiel ist leer und beim Laden der Daten aus der Tabelle werden diese leeren Datenseiten mit in den Buffer Pool geladen. bei 1.000 Datensätzen macht das immerhin 8 MByte aus.
Lesen von Datenseiten im Heap
Wird ein Heap verwendet, so können Daten nur mit einem Table Scan ermittelt werden. Ein Table Scan bedeutet, dass immer die vollständige Tabelle gelesen werden muss und anschließend die gewünschten Datensätze aus der Ergebnismenge gefiltert werden müssen.
SELECT * FROM dbo.demo_table WHERE Id = 10 OPTION (QUERYTRACEON 9130);
Der Beispielcode generiert folgenden Ausführungsplan. Das TF 9130 wird verwendet, um den FILTER-Operator im Ausführungsplan sichtbar zu machen.
Faktisch ist ein Heap auf Grund seiner Definition nicht mit einem Ordnungskriterium versehen. Aus diesem Grund verhält sich der Heap wie ein Puzzle und jeder einzelne Stein muss verglichen werden, bis die gewünschten Steine gefunden werden. Ein Heap hat eine weitere Besonderheit, die es in einem Index nicht gibt; die einzelnen Datenseiten haben keine unmittelbare Verbindung zueinander.
Eine Datenseite in einem Heap ist isoliert und nur durch die IAM-Seite (Index Allocation Map) werden die vielen autarken Datenseiten miteinander in Verbindung gebracht. Da Microsoft SQL Server beim Lesen von Seite 110 z. B. nicht weiß, welche Seiten außerdem zur Tabelle gehören, muss – im Vorfeld – die IAM Datenseite gelesen werden. Auf dieser Seite stehen alle Datenseiten (max. 64.000), die zur Tabelle gehören. Mit dieser Information kann Microsoft SQL Server anschließend die einzelnen Datenseiten lesen. Diese Technik nennt sich Allocation Scan.
Löschen von Datensätzen in Heap
Wenn man versteht, dass ein Heap kein Ordnungskriterium besitzt, kann man sich vorstellen, was passieren würde, wenn während eines Löschvorgang von Transaktion 1 ein weiterer Vorgang Daten der Tabelle lesen möchte.
In der Abbildung laufen zwei Transaktionen zeitversetzt. Transaktion 1 (T1) führt einen Löschvorgang aus während Transaktion 2 (T2) mittels SELECT die Daten für einen anderen Prozess liest. Auf Grund der Strukturen eines Heap müssen beide Prozesse zunächst die IAM Seite lesen. Sobald die IAM Seite gelesen wurde, können beide Prozesse mit dem sequentiellen Durchlaufen der Tabelle beginnen. Würde T1 die Daten und die Datenseite 36 löschen, würde T2 in einen Fehler laufen. Da T2 bereits die IAM Seite gelesen hat, weiß der Prozess, dass er die Datenseite 36 lesen muss. Die wäre aber bei einer Deallokation nicht mehr vorhanden! Aus diesem Grund belässt Microsoft SQL Server die Datenseite als allokierte (aber leere) Datenseite in der Struktur der Tabelle. Unabhängig davon, ob ein weiterer Prozess auf die Tabelle zugreift, ist dieses Verhalten das Standardverhalten von Microsoft SQL Server.
Deallokieren von leeren Datenseiten
Um leere Datenseiten wieder an die Datenbank zurückzugeben, gibt es vier Möglichkeiten:
- Verwendung von Clustered Index statt Heap
- Neuaufbau des HEAP mit ALTER TABLE
- Löschen von Datensätzen mit einer exklusiven Tabellensperre
- Lock Escalation beim Löschen von großen Datensatzmengen
Während Option 1 und 2 selbsterklärend sind, zeigen die nachfolgenden Beispiele, wie es sich mit exklusiven Sperren verhält:
Löschen von Datensätzen mit exklusiver Tabellensperre
Die einfachste Methode, Datensätze aus einem Heap zu löschen und den allokierten Platz wieder freizugeben, besteht darin, die Tabelle während des Löschvorgangs exklusiv zu sperren. Der Nachteil dieses Verfahrens liegt jedoch darin, dass ein solches System nicht mehr skalieren kann. Solange ein Löschvorgang durchgeführt wird, können anderen Prozesse nicht auf die Tabelle zugreifen (Lesend und/oder Schreibend). Einzig mit Hilfe der optimistischen Isolationsstufe “READ COMMITTED SNAPSHOT ISOLATION” lässt sich der Lesevorgang unter intensiver Verwendung von Systemressourcen bewerkstelligen; dieses Verfahren soll jedoch in diesem Artikel nicht näher beleuchtet werden.
-- Now we delete 2000 records
BEGIN TRANSACTION;
GO
DELETE dbo.demo_table WITH (TABLOCK)
WHERE Id <= 2000;
Sobald der Code durchgelaufen ist, sind von den ursprünglichen 20.000 Datenseiten nur noch 18.000 Datenseiten vorhanden.
-- what pages have been allocated by the table
SELECT DDIPS.index_id,
DDIPS.index_type_desc,
DDIPS.page_count,
DDIPS.record_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID(N'dbo.demo_table', N'U'),
0,
NULL,
N'DETAILED'
) AS DDIPS
Microsoft SQL Server kann mit Hilfe einer exklusiven Sperre auf der Tabelle gefahrlos die Datenseiten deallokieren, da während des Löschvorgangs kein Prozess auf die IAM-Seite zugreifen kann.
Transaktion 1 (T1) beginnt mit dem Löschvorgang und setzt zunächst eine exklusive Sperre auf die Tabelle (in diesem Fall auf die IAM-Datenseite). Solange der Löschvorgang andauert, wird diese exklusive Sperre auf der Tabelle belassen. Während dieser Zeit muss Transaktion 2 (T2) warten. Sobald der Löschvorgang beendet ist, wird die Sperre auf der Tabelle aufgehoben und T2 kann (die nun aktualisierte IAM-Seite) lesen. Das Sperren der kompletten Tabelle hat zur Folge, dass Prozesse, in die die Tabelle involviert ist, nicht mehr skalieren können; die Prozesse müssen seriell bearbeitet werden.
-- output of aquired / released locks DBCC TRACEON (3604, 1200, -1); GO -- delete 1,000 records SET ROWCOUNT 2000; DELETE dbo.demo_table WITH (TABLOCK) WHERE Id % 2 = 0; GO -- deactivate the output of locks DBCC TRACEOFF (3604, 1200, -1); GO
Das Codebeispiel macht die Sperren für die Transaktion sichtbar. Die nachfolgende Abbildung zeigt die gesetzten Sperren.
Der Löschvorgang fordert zunächst einen X-Lock auf die Tabelle (OBJECT: 8:245575913). Sobald die Tabellen erfolgreich gesperrt wurde, können Datenseiten und Datensätze gesperrt werden, um sie zu löschen. Durch die exklusive Sperre auf die Tabelle können keine weiteren Prozesse auf das Objekt zugreifen; die IAM ist “gesichert” und kann ohne Gefahren modifiziert werden.
Lock Escalation beim Löschen von großen Datensatzmengen
Das obige Beispiel hat gezeigt, dass man gezielt steuern kann, ob Datenseiten in einem Heap bei einem Löschvorgang wieder freigegeben werden sollen. Jedoch werden Datenseiten auch dann freigegeben, wenn die Tabelle durch den Prozess exklusiv blockiert wird. Als Lock Escalation wird der Vorgang beschrieben, bei dem viele Einzelsperren zu einer Gesamtsperre konvertiert werden. Diese Technik verwendet Microsoft SQL Server, um Ressourcen zu sparen. Der – grobe – Schwellwert für eine Sperrausweitung liegt bei 5.000 Einzelsperren. Wenn z. B. 10.000 Datensätze gelöscht werden sollen, muss Microsoft SQL Server 10.000 exklusive Sperren während der Transaktion halten. Jede Sperre konsumiert 96 Bytes. Bei 10.000 Datensätzen würden das 960.000 Bytes (~1 MB) an RAM während der Transaktion belegt werden. Aus diesem Grund wird ab einem bestimmten Schwellwert die EInzelsperre zu einer Komplettsperre (Partition oder Tabelle) eskaliert.
Das folgende Beispiel zeigt, wie sich Lock Escalation auf das Löschen von großen Datenmengen in einem HEAP auswirkt. Gegeben ist wieder die obige Tabelle mit 20.000 Datensätzen. Aus dieser Tabelle sollen – ohne weitere Tabellenhinweise – 10.000 Datensätze gelöscht werden.
Da ein Datensatz 8 KBytes konsumiert, hat die Tabelle 20.000 Datenseiten allokiert. Um die Besonderheiten im Transaktionsprotokoll besser lokalisieren zu können, wird eine benannte Transaktion verwendet.
-- Now we delete half of the records BEGIN TRANSACTION demo; GO DELETE dbo.demo_table WHERE Id % 2 = 0; GO COMMIT TRANSACTION demo; GO
Nachdem 10.000 Datensätze gelöscht worden sind (OHNE Tabellenhinweise), stellt sich das Ergebnis der verbliebenen Datenseiten wie folgt dar.
Die Zahl der verbliebenen Datenseiten ergibt – auf dem ersten Blick – überhaupt keinen Sinn. Die Erwartungshaltung bei diesem Löschvorgang wäre entweder alle 20.000 Datenseiten verbleiben im allokierten Zustand oder aber nur noch 10.000 Datenseiten. Die während des Löschvorgangs gesetzten Sperren stellen sich wie folgt dar:
20.000 allokierte Datenseiten – 6.876 gesperrte Datenseiten = verbliebene 13.124 Datenseiten. Die Frage, die sich in diesem Zusammenhang stellt: Warum werden 6.876 Datenseiten exklusiv gesperrt und nicht alle 10.000 Datenseiten. Ein Blick in das Transaktionsprotokoll liefert die Antworten zu diesem Verhalten.
Die erste Abbildung zeigt den Inhalt des Transaktionsprotokolls zu Beginn der Transaktion. Es ist gut zu erkennen, dass einzelne Datensätze gelöscht werden (AQUIRE_LOCK_X_RID). Da zu diesem Zeitpunkt keine exklusive Sperre auf der Tabelle liegt, verbleiben die Datenseiten weiterhin in der Tabelle.
Ab Zeile 3.126 im Transaktionsprotokoll wird es interessant. Insgesamt wurden – bis zu diesem Punkt – 3.124 Datensätze gelöscht, OHNE die Datenseiten wieder zu deallokieren! Ab Datensatz 3.125 findet eine Lock Escalation statt (Zeile 3126). Nun wird nicht mehr jede einzelne Datenzeile (RID) blockiert sondern die vollständige Tabelle (OBJECT). Wird bei gesperrter Tabelle ein Datensatz gelöscht, ist die Aktion “aufwändiger”:
- Der Datensatz wird gelöscht (LOP_DELETE_ROWS)
- Aktualisierung des Headers der betroffenen Datenseite (LOP_MODIFY_HEADER)
- Freigabe der Datenseite in PFS (LOP_MODIFY_ROW | LCK_PFS)
- Löschen der Zuordnung aus IAM (LOB_HOBT_DELTA)
Insgesamt werden die ersten Datensätze – bis zur Lock Escalation – aus den Datenseiten gelöscht ohne die Datenseiten zu deallokieren. Bei einer Lock Escalation (Tabelle wird gesperrt) werden ab diesem Moment die Datenseiten im dem Heap deallokiert.
Zusammenfassung
Heaps bieten viele Vor- und Nachteile in einem Datenbanksystem; man sollte vor der Implementierung von Heaps berücksichtigen, dass sie “pflegebedürftiger” sind als Clustered Indexe. Ein Heap reagiert in DML-Operationen komplett anders als ein Clustered Index. Ein Heap sollte daher nur verwendet werden, wenn:
- Die Tabelle hauptsächlich Daten aufnimmt (INSERT)
z. B. Protokoll-Tabellen - Die Tabelle autark ist und keine Referenz zu anderen Tabellen besitzt
- Die Attribute des Heaps ausschließlich “Fixed Length” Attribute sind
(Forwarded Records)
Wenn Daten aus einem Heap gelöscht werden müssen, dann muss man berücksichtigen, dass Microsoft SQL Server Datenseiten in einem Heap nicht automatisch wieder freigibt. Datenseiten werden nur deallokiert, wenn sichergestellt ist, dass die Tabelle nicht durch andere Prozesse gelesen werden kann; die Tabelle muss aslo exklusiv gesperrt sein! Durch die Bearbeitung von Tabellen mit exklusiven Sperren wird ein großer Vorteil von Microsoft SQL Server – Granularität und Concurrency – gewaltsam blockiert. Diese Besonderheiten gilt es bei der Arbeit mit Heaps zu beachten.
Herzlichen Dank fürs Lesen!
