Dieser Artikel beschreibt Strategien, wie große Datenmengen aus Relationen gelöscht werden können und dabei möglichst effizient und schnell zu arbeiten. Ziel einer solchen Operation ist die Vermeidung großer Datenmengen im Transaktionsprotokoll. Diese Aufgabenstellung zu dokumentieren, rührt von den immer wieder auftretenden Fragen in den Microsoft Foren bezüglich effizienter Löschstrategien:

Need to build a job that purges old data and rebuilds indexes

Removing large number of records with truncate?

Problembeschreibung

Häufig kommt es in großen Datenbanksystemen zu Aufgabenstellungen, große Teilmengen der Daten und Altbestände oder sonstige Daten aus der Datenbank zu entfernen. Beim Löschen großer Datenmengen muss der Datenbankadministrator mehrere Aspekte berücksichtigen, die seine Entscheidung maßgeblich beeinflussen:

• Ist die Datenbank Bestandteil einer Hochverfügbarkeitslösung, die einen Wechsel des Wiederherstellungsmodells erschwert oder sogar unmöglich macht?
• Wie viele Indexe sind von der Löschaktion in einer Relation betroffen?
• Ist die betroffene Relation von anderen Objekten abhängig, die eine bevorzugte Löschoperation verhindern? - FOREIGN KEY – Einschränkung - Schemagebundene Views / Funktionen / ...
• Wie fragmentiert sind nach dem Löschen die Indexe, die für die Relation erstellt wurden? Muss eventuell nach dem Löschvorgang ein ALTER INDEX REBUILD durchgeführt werden?

Alle obigen Fragen müssen vom Datenbankadministrator berücksichtigt werden, um die richtige Strategie festzulegen. Die nachfolgenden Beispiele zeigen Möglichkeiten auf und zeigen auch die entsprechenden Einschränkungen, die mit der Lösung einhergehen!

Testumgebung

Als Testumgebung wird eine Datenbank angelegt, in der sich eine Testtabelle mit 100.000 Datensätzen befindet. Diese Relation besitzt keine weiteren Einschränkungen und soll nur das generierte Transaktionsvolumen messen, dass bei den jeweiligen Lösungsstrategien generiert wird.

 1: -- Erstellen der Demonstrations-Datenbank

 2: IF db_id('DeleteRecord') IS NOT NULL

 3: BEGIN

 4:     ALTER DATABASE DeleteRecord SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

 5:     DROP DATABASE DeleteRecord;

 6: END

 7:  

 8: CREATE DATABASE DeleteRecord

 9: ON PRIMARY 

 10: ( 

 11:     NAME = N'DelRecord', 

 12:     FILENAME = N'S:\Backup\DelRecord.mdf',

 13:     SIZE = 100MB,

 14:     MAXSIZE = 1000MB,

 15:     FILEGROWTH = 100MB

 16: )

 17: LOG ON 

 18: (

 19:     NAME = N'DelRecord_log',

 20:     FILENAME = N'S:\Backup\DelRecord.ldf',

 21:     SIZE = 100MB,

 22:     MAXSIZE = 1GB,

 23:     FILEGROWTH = 100MB

 24: );

 25: GO

 26:  

 27: -- Das Wiederherstellungsmodell wird initial auf SIMPLE gestellt

 28: ALTER DATABASE DeleteRecord SET RECOVERY SIMPLE;

 29: GO

 30:  

 31: -- Erstellen der Relation für die Testdaten

 32: USE DeleteRecord;

 33: GO

 34:  

 35: CREATE TABLE dbo.bigtable

 36: (

 37:     Id    int             NOT NULL    IDENTITY (1, 1),

 38:     c1    char(100)       NOT NULL    DEFAULT ('only stupid stuff'),

 39:     c2    varchar(100)    NOT NULL    DEFAULT ('more stupid stuff'),

 40:     c3    date            NOT NULL    DEFAULT (getdate()),

 41:  

 42:     CONSTRAINT pk_bigTable PRIMARY KEY CLUSTERED (Id)

 43: );

 44: GO

 45:  

 46: -- Eintragen von 100.000 Datensätzen

 47: SET NOCOUNT ON

 48: GO

 49:  

 50: INSERT INTO dbo.bigtable DEFAULT VALUES

 51: GO 100000

Die Relation [dbo].[bigtable] besitzt 100.000 Datensätze, von denen in den nachfolgenden Szenarien jeweils die Hälfte der Datensätze gelöscht werden sollen.

Löschen von Datensätzen mit DELETE FROM <table>

Die wohl bekannteste Möglichkeit zum Löschen von Datensätzen ist die DELETE-Operation. DELETE ist eine DML-Operation und hat entsprechende Einschränkungen. Eine DML-Operation ist immer eine vollständig protokollierte Operation – das heißt also, dass beim Löschen von großen Datenmengen JEDE zu löschende Datenzeile protokolliert wird.

Befindet sich die Datenbank in einem Hochverfügbarkeitsumfeld, kann ein solcher Löschvorgang für den Spiegel (Mirroring), Replica (AlwaysOn) oder das Backup (Log Shipping) problematisch werden. Eine vollständig protokollierte Transaktion dauert entsprechend lang, da jede Einzelaktion in das Protokoll geschrieben werden muss. Neben dem erzeugten Datenvolumen muss ebenfalls berücksichtigt werden, dass während des Löschvorgangs Sperren auf die Ressourcen gesetzt werden. Im Falle einer “Lock-Eskalation” kann dies (bei großer Datenmenge) zu einer vollständigen Tabellensperre (Table-Lock) führen (http://technet.microsoft.com/de-de/library/ms184286(v=sql.105).aspx).

Dem Datenbankadministrator muss also daran gelegen sein, neben einem schnellen Löschvorgang auch das generierte Transaktionsprotokoll so gering wie möglich zu halten. Sehr häufig wird daher in den Foren von zwei Strategien gesprochen, die sowohl das Transaktionsvolumen gering halten sollen als auch die Sperren auf die Ressourcen verringern.

Ändern des Wiederherstellungsmodells auf SIMPLE oder BULK_LOGGED

Dieser – sehr häufig in den Foren zu lesende – Vorschlag ist nicht praktikabel, da er auf DELETE als DML-Operation nicht anwendbar ist. DELETE ist eine DML-Operation, die eine vollständige Protokollierung unabhängig vom Wiederherstellungsmodell erzwingt. Das Beispiel zeigt das generierte Transaktionsvolumen, das bei unterschiedlichen Wiederherstellungsmodellen generiert wird. Dazu wird das folgende Script zum Löschen aller Datensätzen mit einer geraden Id angewendet:

 1: USE DeleteRecord;

 2: GO

 3:  

 4: -- Bei Messung das entsprechende Wiederherstellungsmodell wählen

 5: ALTER DATABASE DeleteRecord SET RECOVERY SIMPLE -- BULK_LOGGED, FULL;

 6: GO

 7:  

 8: -- Tabelle für die Speicherung des Transaktionsvolumens

 9: DECLARE    @ResultTable TABLE

 10: (

 11:     Id            int            NOT NULL IDENTITY (1, 1),

 12:     Operation    varchar(20)    NOT NULL,

 13:     bytes        bigint        NOT NULL

 14: );

 15:  

 16: -- Beginn der Transaktion

 17: BEGIN TRANSACTION

 18:     -- Löschen der Hälfte des Datenvolumens

 19:     DELETE    dbo.bigTable WHERE Id % 2 = 0;

 20:  

 21:     INSERT INTO @ResultTable (Operation, Bytes)

 22:     SELECT    'DELETE', database_transaction_log_bytes_used

 23:     FROM    sys.dm_tran_database_transactions

 24:     WHERE    database_id = db_id();

 25:  

 26:     -- Neuerstellung aller (1) Index(e) wegen Fragmentierung

 27:     ALTER INDEX ALL ON dbo.bigTable REBUILD;

 28:  

 29:     INSERT INTO @ResultTable (Operation, Bytes)

 30:     SELECT    'REBUILD', database_transaction_log_bytes_used

 31:     FROM    sys.dm_tran_database_transactions

 32:     WHERE    database_id = db_id();

 33:  

 34:     - Ausgabe des gemessenen Transaktionsvolumens

 35:     SELECT  Id,

 36:             Operation,

 37:             Bytes - LAG(Bytes, 1, 0) OVER (ORDER BY Id) AS TransactionVolume

 38:     FROM    @ResultTable;

 39: ROLLBACK TRANSACTION

Das Script stellt zunächst das gewünschte Wiederherstellungsmodell ein um anschließend den Löschvorgang durchzuführen. Dazu werden die generierten Transaktionsvolumina in einer Tabellenvariablen zwischengespeichert um sie später auswerten zu können. Für jedes Wiederherstellungsmodell wurde das Transaktionsvolumen gemessen – folgende Ergebnisse wurden dabei protokolliert:

Wie der Vergleich zeigt, ist das generierte Transaktionsvolumen für den eigentlichen Löschvorgang (DELETE) in allen Wiederherstellungsmodellen identisch – eben weil DELETE ein vollständig protokollierter DML-Befehl ist. Interessant ist jedoch, wie sich das Transaktionsvolumen bei der Neuerstellung des Index verändert. Dieser Unterschied rührt daher, dass die Protokollierung der Indexänderungen vom Wiederherstellungsmodell abhängt. ALTER INDEX REBUILD ist in den Wiederherstellungsmodellen SIMPLE und BULK_LOGGED nur “minimal logged” (http://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx).

Problematisch ist bei der zu löschenden Datenmenge, welche Sperren Microsoft SQL Server verwendet, um den Löschvorgang abzuschließen. Da es sich um 50% der gesamten Datenmenge handelt, wendet Microsoft SQL Server eine Tabellensperre an – somit ist während des Löschvorgangs ein Zugriff auf die Relation nicht möglich!

Zwischenergebnis

Das Ändern des Wiederherstellungsmodells bringt keine Vorteile beim Löschen von Datensätzen mittels DELETE. Da DELETE eine vollständig protokollierte Operation ist, ändert sich das generierte Datenvolumen nicht. Wird zusätzlich ein INDEX REBUILD durchgeführt, verändert sich das Transaktionsvolumen im Wiederherstellungsmodus FULL signifikant, da das INDEX REBUILD in diesem Wiederherstellungsmodus die Erstellung vollständig protokolliert. Andere Wiederherstellungsmodelle verwenden “minimal logging” bei dieser Aktion.

Löschen von Datensätzen mit TRUNCATE

TRUNCATE ist – im Gegensatz zu DELETE – keine DML-Operation sondern eine DDL-Operation. Bei TRUNCATE werden nicht die Daten selbst geändert sondern das zu Grunde liegende Schema (es wird den Daten quasi “der Teppich unter den Füssen” entfernt!). Die detaillierte Funktionsweise mit allen Vor- und Nachteilen habe ich im Artikel “TRUNCATE <tablename> wird nicht protokolliert?” beschrieben.

Bei diesem Vorgehen werden Daten, die nicht gelöscht werden, zunächst in einer Stagingtabelle gespeichert. Anschließend wird die Relation mit TRUNCATE komplett neu aufgebaut und die Daten werden aus der Stagingtabelle in die neu erstellte Relation überführt. Auf Grund der Beschränkungen von TRUNCATE ist diese Operation nur möglich, wenn die Quelle keine Fremdschlüsselbeziehungen hat und/oder abhängig von schemagebundenen Objekten ist.

Da das besondere Augenmerk dieses Artikels auf dem generierten Transaktionsvolumen liegt, kann diese Einschränkung für das Beispiel außer acht gelassen werden. Das Script für das Löschen der Datensätze sieht wie folgt aus:

 1: ALTER DATABASE DeleteRecord SET RECOVERY SIMPLE -- BULK_LOGGED, FULL;

 2: GO

 3:  

 4: DECLARE @ResultTable TABLE

 5: (

 6:     Id           int           NOT NULL IDENTITY (1, 1),

 7:     Operation    varchar(20)   NOT NULL,

 8:     bytes        bigint        NOT NULL

 9: );

 10:  

 11: BEGIN TRANSACTION

 12:     -- Bulk logged

 13:     SELECT    *

 14:     INTO    dbo.bigtable_intermediate

 15:     FROM    dbo.bigtable

 16:     WHERE    Id % 2 = 0;

 17:  

 18:     INSERT INTO @ResultTable (Operation, Bytes)

 19:     SELECT    'MOVE', database_transaction_log_bytes_used

 20:     FROM    sys.dm_tran_database_transactions

 21:     WHERE    database_id = db_id();

 22:  

 23:     -- minimally logged because DDL-Operation

 24:     TRUNCATE TABLE dbo.bigtable;

 25:  

 26:     INSERT INTO @ResultTable (Operation, Bytes)

 27:     SELECT    'TRUNCATE', database_transaction_log_bytes_used

 28:     FROM    sys.dm_tran_database_transactions

 29:     WHERE    database_id = db_id();

 30:  

 31:     SET IDENTITY_INSERT dbo.bigTable ON;

 32:     INSERT INTO dbo.bigtable (Id, c1, c2, c3) 

 33:     SELECT Id, c1, c2, c3 FROM dbo.bigtable_intermediate ORDER BY Id;

 34:     SET IDENTITY_INSERT dbo.bigtable OFF;

 35:  

 36:     INSERT INTO @ResultTable (Operation, Bytes)

 37:     SELECT    'TRANSFER BACK', database_transaction_log_bytes_used

 38:     FROM    sys.dm_tran_database_transactions

 39:     WHERE    database_id = db_id();

 40:  

 41:     SELECT  Id,

 42:             Operation,

 43:             Bytes - LAG(Bytes, 1, 0) OVER (ORDER BY Id) AS TransactionVolume

 44:     FROM    @ResultTable;

 45: ROLLBACK TRANSACTION

Das obige Script überträgt zunächst die NICHT zu löschenden Daten in eine Zwischentabelle [dbo].[bigtable_intermediate]. Anschließend wird die Quelle mittels TRUNCATE komplett neu aufgebaut (es werden ausschließlich Metadaten geändert!). Nachdem die Relation neu aufgebaut wurde, können die zwischengespeicherten Daten wieder in die Quelltabelle übertragen werden.

Interessant bei diesem Verfahren ist, dass die Operation “SELECT ... INTO” bulk logged unterstützt. Das bedeutet für die Transaktion, dass die Operation relativ wenig Transaktionsvolumen generiert und der Prozessteil relativ schnell abgearbeitet ist.

Bei TRUNCATE handelt es sich um eine DDL-Operation – es werden also keine Daten als solche gelöscht sondern ausschließlich die den Daten zugrunde liegende Struktur. Dieser Teil der Transaktion sollte also auch kein großes Transaktionsvolumen generieren.

Die Operation “INSERT INTO ... SELECT ... FROM” besitzt ebenfalls die Fähigkeit, als bulk logged Operation verwendet zu werden (http://technet.microsoft.com/en-us/library/ms174335(v=sql.105).aspx). Das Ergebnis der Messungen für alle drei Wiederherstellungsmodelle sieht wie folgt aus:

Die Zahlen zeigen das für jeden Transaktionsschritt generierte Transaktionsvolumen. Auffällig ist erneut, dass im Wiederherstellungsmodell FULL für den Transfer der Daten in die Stagingtabelle fast 20x mehr Protokollvolumen generiert wird, als in den anderen beiden Wiederherstellungsmodellen.

Wie weiter oben beschrieben sollte auch “INSERT INTO” die Möglichkeit besitzen von den Vorteilen des BULK LOGGED zu partizipieren. Betrachtet man sich jedoch die Ergebnisse, wird schnell erkennbar, dass Microsoft SQL Server diese Option für den Transfer nicht verwendet hat.

Um die Operation INSERT INTO als BULK LOGGED Operation zu verwenden, müssen einige Voraussetzungen erfüllt sein:

• Das Wiederherstellungsmodell muss SIMPLE oder BULK_LOGGED sein
• Das Ziel muss leer sein oder aber es handelt sich um einen HEAP
• Das Ziel darf nicht Bestandteil einer Replikation sein
• Das Ziel muss exklusiv mit dem Hinweis TABLOCK gesperrt werden

Die drei ersten Optionen können vernachlässigt werden, da die Datenbank sich einem der geforderten Wiederherstellungsmodelle befunden hat. Auch war die Relation nach der Ausführung von TRUNCATE vollständig geleert und die Relation ist nicht Bestandteil einer Replikation. Das Zielobjekt wurde aber nicht explizit gesperrt. Ändert man das Script für den Bereich des Rücktransfers wie folgt ab, ist das Objekt gesperrt:

 1: SET IDENTITY_INSERT dbo.bigTable ON;

 2: INSERT INTO dbo.bigtable WITH (TABLOCK) (Id, c1, c2, c3) 

 3: SELECT Id, c1, c2, c3 FROM dbo.bigtable_intermediate ORDER BY Id;

 4: SET IDENTITY_INSERT dbo.bigtable OFF;

Die neuen Messungen zeigen den Unterschied deutlich – auf eine Kommentierung verzichte ich:

Zwischenergebnis

Die Verwendung von TRUNCATE als Löschmechanismus ist die effektivste Lösung. Alle für eine solche Lösung verwendeten DML / DDL – Operationen werden minimal protokolliert oder weisen auf Grund ihrer Arbeitsweise kein hohes Transaktionsvolumen auf.

Zusammenfassung

Zwei Lösungen mit unterschiedlichem Ausgang. Jede Lösung hat ihre Berechtigung. Während DELETE zwar das schlechteste Verhalten in Bezug auf das Transaktionsvolumen besitzt, ist es die einzige Möglichkeit in Hochverfügbarkeitslösungen wie Spiegelung, AlwaysOn (Für Log Shipping wäre das BULK_LOGGED Wiederherstellungsmodell möglich!). Auch gilt es, zwischen “Performance” und RPO (Recovery Point Objectives) abzuwägen.

Wird das Wiederherstellungsmodell von FULL auf SIMPLE geändert, werden Transaktionen, die während des Löschvorgangs von anderen Benutzern ausgeführt werden, nicht mehr im Transaktionsprotokoll gespeichert und Änderungen können nicht mehr rückgängig gemacht werden.

Sind die Voraussetzungen für die Verwendung von TRUNCATE gegeben, sollte man diese Lösung wählen, wenn es “schnell” gehen soll. Insbesondere bietet sich diese Möglichkeit an, wenn ein geplantes Wartungsfenster zur Verfügung steht, in dem niemand Daten bearbeitet.  Nachteilig sind die Einschränkungen, die dieser Lösung vorausgehen.

Herzlichen Dank fürs Lesen!