Samstag, 8. Juni 2013

TRUNCATE TABLE <tablename> wird nicht protokolliert?

Diese Aussage habe ich heute während eines Kundentermins von einem DBA gehört. Solche “Mythen” werden immer wieder mal über die SQL Foren verbreitet und dann – leider – durch Mundpropaganda gerne in die Unternehmen getragen. Diese Aussage ist FALSCH! Selbstverständlich wird ein TRUNCATE TABLE protokolliert– und zwar genau so, wie ein DELETE dbo.relation. Dieser Artikel beschreibt und belegt detailliert, dass man Aussagen, wie den obigen möglichst skeptisch gegenüber stehen sollte und nicht immer alles glauben darf, was man so hört.

Bevor es in die Details geht, zunächst ein kleines Experiment; dieses Experiment belegt bereits, dass es sich bei TRUNCATE TABLE um eine protokollierte Transaktion handelt.

-- Erstellen einer einfachen Relation
CREATE TABLE dbo.mytable
(
    id
    int      NOT NULL IDENTITY(1, 1),
    col1 
char(20) NOT NULL,
    col2 
char(20) NOT NULL,

    CONSTRAINT pk_mytable_id PRIMARY KEY CLUSTERED (id)
);

-- Eintragen von 100.000 Datensätzen
DECLARE @i int = 1
WHILE @i <= 100000
BEGIN
    INSERT INTO dbo.mytable (col1, col2)
    SELECT 'Value: ' + CAST(@i AS varchar(2)),
           'Value: ' + CAST(@i % 10 AS varchar(2))

   SET    @i += 1
END

-- Ergebnis
SELECT * FROM dbo.mytable;

PreCheck_01

Nachdem die Relation mit ein paar Datensätzen angelegt wurde, kann das folgende Statement ausgeführt werden:

-- Löschen aller Daten aus der Relation mit TRUNCATE
BEGIN TRANSACTION
    TRUNCATE TABLE dbo.myTable;
    SELECT * FROM dbo.myTable;
ROLLBACK TRANSACTION

SELECT * FROM dbo.myTable;

Simpel: Innerhalb einer Transaktion wird der Inhalt der Relation mit TRUNCATE gelöscht und anschließend der Inhalt angezeigt. Im Anschluss wird die Transaktion abgebrochen und erneut der Inhalt überprüft.

Vor Transaktion In Transaktion Nach Transaktion
PreCheck_01 PreCheck_02 PreCheck_01

Wie man deutlich erkennen kann, wird durch ein ROLLBACK die Transaktion rückgängig gemacht. Warum also hält sich dieser Mythos so beständig. Vermutlich rührt diese Aussage auf der Beobachtung, dass TRUNCATE um ein Vielfaches schneller ausgeführt wird, als ein DELETE. Der Unterschied in der Ausführungsgeschwindigkeit beider Verfahren ist die Art und Weise, wie der “Löschvorgang” intern durchgeführt wird. Für das Verständnis reicht ein – erster – oberflächlicher Blick auf die physikalischen Indexstrukturen. Die nachfolgenden Skripte machen den Unterschied deutlich:

-- Löschen aller Daten aus der Relation mit TRUNCATE
BEGIN TRANSACTION
    TRUNCATE TABLE dbo.myTable;

    SELECT  i.name,
            ps.index_level,
            ps.page_count,
            ps.record_count,
            ps.ghost_record_count
    FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.mytable', 'U'), 1, DEFAULT, 'DETAILED') ps
            ON (
                 i.object_id = ps.object_id AND
                 i.index_id = ps.index_id
               )
ROLLBACK TRANSACTION

INDEX_PHYSICAL_STATS_TRUNCATE

Nach einem TRUNCATE sind KEINE Informationen über allokierten Speicher mehr vorhanden!

-- Löschen aller Daten aus der Relation mit DELETE
BEGIN TRANSACTION
    DELETE dbo.myTable;
    SELECT * FROM dbo.myTable;

    SELECT  i.name,
            ps.index_level,
            ps.page_count,
            ps.record_count,
            ps.ghost_record_count
    FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.mytable', 'U'), 1, DEFAULT, 'DETAILED') ps
           
ON (
                 i.object_id = ps.object_id AND
                 i.index_id = ps.index_id
               )
ROLLBACK TRANSACTION

INDEX_PHYSICAL_STATS_DELETE

Bei einer DELETE-Aktion bleiben die Schemadaten vollständig erhalten. Somit MUSS es einen Unterschied geben. Schaut man in die Online Hilfe von Microsoft SQL Server, mag es auf Grund der dortigen Aussage leicht zu Missverständnissen kommen:

“Die TRUNCATE TABLE-Anweisung ist ein schnelles, effizientes Verfahren zum Löschen aller Zeilen einer Tabelle. TRUNCATE TABLE ist der DELETE-Anweisung ohne eine WHERE-Klausel ähnlich. TRUNCATE TABLE ist jedoch schneller und verwendet weniger Systemressourcen und Ressourcen für die Transaktionsprotokollierung.”

Die Online Hilfe schreibt, dass beide Verfahren ähnlich sind; das ist aber NICHT der Fall. Zwischen beiden Verfahren liegt ein erheblicher Unterschied. Während DELETE eine DML-Aktion ist, ist TRUNCATE eine DDL-Aktion. Von DML-Aktionen spricht man, wenn Manipulationen auf Datenebene durchgeführt werden (SELECT, INSERT, UPDATE, DELETE). Von DDL-Aktionen spricht man, wenn es sich um Schema-Manipulation / Manipulation der Metadaten handelt. Und hier genau liegt der Unterschied, den man wunderbar durch einen Blick in das Transaktionsprotokoll und die Transaktionssperren belegen kann.

TRUNCATE intern

Zunächst einmal ein Blick auf das Transaktionsprotokoll und die Transaktionssperren bei einer TRUNCATE-Aktion.
(Da es sich um ein Testsystem handelt, ist nur eine Transaktion aktiv – von daher muss nicht auf eine LSN gefiltert werden!)

SELECT [Current LSN],
       Description,
       Operation,
       [Lock Information],
       [Rows Deleted],
       [Log Record]
FROM   fn_dblog(NULL, NULL)
ORDER BY
       [Current LSN];

FN_DBLOG_TRUNCATE_01

Das Transaktionsprotokoll ist wie ein “offenes Buch” zu lesen. Zunächst wird die Transaktion eingeleitet (Zeile 1) und augenblicklich wird das Objekt [dbo].[mytable] für Schema-Modifikationen gesperrt (Zeile 2). Was dann kommt, ist jedoch viel interessanter – es werden keine Datenzeilen aus den von [dbo].[mytable] allokierten Datenseiten gelöscht sondern es werden Datensätze manipuliert, die Metadaten zu Objekten speichern (PFS / IAM)! Als Beispiel soll – wie in Zeile 4 beschrieben – die Deallokierung der Page 106 dienen. Ein Blick auf den Pageheader zeigt, dass es sich um eine IAM-Seite (Index Allocation Map) und nicht um eine Datenseite handelt.

DBCC TRACEON (3604);
DBCC PAGE ('db_demo', 1, 106, 1)

DBCC_PAGE_01

Leitet man von dieser Information ab, dass die Relation [dbo].[mytable] einen Clustered Index hat und somit der Index die Relation selbst repräsentiert, ist verständlich, was hier gerade passiert; es werden nicht Datenseiten aus dem System entfernt sondern Datenzuordnungsseiten. Die Verbindungen zwischen Datenbankstruktur und Daten wird – quasi – gelöscht! Mal eben 42 Datensätze in einer IAM-Seiten zu ändern, ist natürlich etwas schneller, als 100.000 Datensätze aus den Datenseiten zu löschen!

DELETE intern

Statt eines TRUNCATE wird nun ein DELETE ohne WHERE-Klausel ausgeführt und erneut das Transaktionsprotokoll untersucht

FN_DBLOG_DELETE_01

Man kann sehr deutlich erkennen, dass es bei dieser Transaktion nicht mehr um Änderungen der Metadaten (Schemata) geht sondern vielmehr auf Datensatzebene Manipulationen vorgenommen werden. Der Context [LCK_MARK_AS_GHOST] beschreibt bereits die Aktion; Datensätze auf den durch den Clustered Index allokierten Datenseiten werden als “gelöscht” markiert und der allokierte Datenbereich wird für neue Datensätze freigegeben. Ein Blick auf die gesperrten Objekte belegt diesen Sachverhalt ebenfalls:

SELECT resource_type,
       OBJECT_NAME(resource_associated_entity_id) AS object_name,
       request_mode,
       request_type,
       request_status

FROM   sys.dm_tran_locks
WHERE  request_session_id  = XX; -- Eigene SPID eintragen, in der die Transaktion läuft!

TRAN_LOG_01

Während des Löschvorgangs wird eine exklusive Sperre auf die komplette Relation gesetzt – macht Sinn, da ja die Relation vollständig geleert werden soll.

Berechtigungen TRUNCATE vs. DELETE

Ein dritter – wichtiger – Hinweis auf deutliche Unterschiede zwischen TRUNCATE und DELETE beschreibt die erforderlichen Berechtigungen für die jeweiligen Transaktionen. Wie oben gezeigt, handelt es sich bei TRUNCATE um eine DDL-Aktion während DELETE eine DML-Aktion ist. Aus diesem Grund ist ableitbar, warum TRUNCATE die Berechtigung ALTER benötigt während für DELETE eine DELETE-Berechtigung für das betroffene Objekt ausreicht. Die Berechtigung ALTER hat weitreichende Konsequenzen für das Sicherheitskonzept, da mit dieser Gewährung ALLE Manipulationsmöglichkeiten auf Schemaebene für den Berechtigten einher gehen.

Fazit

Der Artikel zeigt, dass es Unterschiede zwischen TRUNCATE und DELETE gibt; Er belegt, dass die Aussage, “TRUNCATE wird nicht protokolliert” nicht wahr ist. TRUNCATE verfolgt zwar einen anderen Ansatz, ist aber eine VOLLSTÄNDIG protokollierte Transaktion. Auf Grund der unterschiedlichen Arbeitsweise ist das Transaktionsprotokoll deutlich geringer als beim Löschen mittels DELETE – und daher kommen auch die Zeitunterschiede. TRUNCATE bedeute eine Neuerstellung der Relation auf Grund der Löschung von allokierten Datenseiten aus den Verwaltungsseiten (PFS / IAM).

Das kann man sehr gut beobachten, wenn man mal folgende Aktion durchführt:

  1. Lassen Sie sich die Datenstrukturen der Relation ausgeben, wie im Artikel “Neue DMF für Struktur der Datenseiten (Pages)” beschrieben
  2. Löschen Sie alle Daten aus der Relation mittels TRUNCATE
  3. Lassen Sie die Abfrage wie in Schritt 1 beschrieben, erneut ausführen – es werden KEINE Datenseiten mehr vorhanden sein!
  4. Fügen Sie neue Datensätze in die zuvor mit TRUNCATE geleerte Relation ein
  5. Lassen Sie die Abfrage wie in Schritt 1 beschrieben, erneut ausführen – nun werden vollständig neue Datenseiten allokiert!

Merke: JEDE Datenmanipulation (Schemata oder Benutzerdaten) wird vollständig protokolliert. Microsoft SQL Server folgt für ALLE Aktionen IMMER dem ACID-Prinzip!

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Herzlichen Dank fürs Lesen!

Struktur von Pages (Paul Randal): http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
PFS / IAM / GAM / SGAM: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx
Technorati-Markierungen: ,,,

Kommentare :

  1. hallo Uwe,
    hmm, bevor ich Deinen Artikel las, habe ich auch die Truncate-Legenden verbreitet..
    Einfach cool!
    Ich bin hier gelandet, weil ich den interessanten Artikel über die Master-Prüfung gelesen habe - mein herzlicher Glückwunsch!
    Für einen frisch gebackenen MCSA (SQL 2012) ist der Weg zum Master noch sehr lang und steinig.. ;-)
    mit Gruß
    Andrzej

    AntwortenLöschen
  2. Hallo Andrzej,

    Es freut mich, dass Dir der Artikel gefallen hat.
    Bezüglich MCM hast Du ja bereits den Grundstein zum - neuen - MCSM gelegt.
    Viel Erfolg beim nächsten Schritt, den MCSE :)

    AntwortenLöschen
    Antworten
    1. Hallo Uwe,

      danke für die netten Wünsche.
      Ich glaube einen Tippfehler in Deinem Beispiel gefunden zu haben(die Schleife wird nur 1 mal lauen?):
      -- Eintragen von 100.000 Datensätzen
      ...
      DECLARE @i int = 100000
      WHILE @i <= 100000
      ...
      Es gibt hier viele coole Artikel von Dir, ich fange an zu lesen und lernen.. :-)
      MfG
      Andrzej

      Löschen
    2. Hallo Andrzej,

      tatsächlich - da hat der Fehlerteufel zugeschlagen.
      Ist korrigiert :)

      Löschen
  3. Toller Artikel.
    Übrigens gibt es zuweilen einen verräterischen Hinweis darauf, dass beim TRUNCATE Datensätze indirekt durch das Löschen von Schema-/Cataloginfos (DDL) gelöscht werden:
    Wenn ein ForeignKey die Tabelle referenziert, die wir "truncaten" wollen, gibt es einen Laufzeitfehler:
    "Cannot truncate table 'mytable' because it is being referenced by a FOREIGN KEY constraint.
    Man also muss zunächst die Referenzierung aufheben (DDL), damit man truncate durchführen kann.

    AntwortenLöschen