In der SQL Server-Welt gibt es einen persistenten Mythos, dass sowohl die Befehle DROP TABLE als auch TRUNCATE TABLE nicht protokolliert sind.
Das sind sie nicht. Sie sind beide vollständig protokolliert, aber effizient protokolliert.
Sie können dies leicht selbst beweisen., Führen Sie den folgenden Code aus, um eine Testdatenbank und-tabelle einzurichten, und zeigen Sie, dass wir 10.000 Zeilen in unserer Tabelle haben:
Ergebnisse:
—-
10000
Und dann den folgenden Code, der die Tabelle in einer Transaktion schneidet und die Zeilenanzahl überprüft:
BEGIN TRAN;GOTRUNCATE TABLE ;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO
Ergebnisse:
— –
0
Nun ist die Tabelle leer., Aber ich kann die Transaktion rückgängig machen und alle Daten wieder einfügen:
ROLLBACK TRAN;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO
Ergebnisse:
—-
10000
Eindeutig die TRUNCATE Der Vorgang muss protokolliert werden, da sonst der Rollback-Vorgang nicht funktioniert.
Woher kommt das Missverständnis?
Es kommt aus dem Verhalten vonDROP undTRUNCATE Operationen an großen Tabellen., Sie werden fast augenblicklich abgeschlossen, und wenn Sie im Transaktionsprotokoll mit fn_dblog direkt danach suchen, sehen Sie nur eine kleine Anzahl von Protokolldatensätzen, die aus der Operation generiert wurden. Diese kleine Zahl korreliert nicht mit der Größe der Tabelle, die abgeschnitten oder gelöscht wird, so dass es scheint, als ob DROP und TRUNCATE Operationen sind nicht protokolliert.
Aber Sie sind vollständig protokolliert, wie ich oben gezeigt. Wo sind also die Protokolldatensätze für die Operationen?,
Die Antwort lautet, dass die Protokolldatensätze nicht sofort durch einen Mechanismus namens „deferred drop“ erstellt werden, der in SQL Server 2000 SP3 hinzugefügt wurde.
Wenn eine Tabelle gelöscht oder abgeschnitten wird, müssen alle für die Tabelle zugewiesenen Datendateiseiten freigegeben werden.,uire eine exklusive Zuordnungssperre für den Umfang
Testen Sie die Seitensperre für jede Seite im Umfang (erwerben Sie die Sperre im exklusiven Modus und legen Sie sie sofort ab, um sicherzustellen, dass niemand sonst die Seite gesperrt hat)
Lassen Sie die Ausdehnungssperre NICHT los, um sicherzustellen, dass niemand diesen Umfang verwenden kann
Bewegen Sie sich zum nächsten Umfang
Ende
Da alle Ausdehnungssperren bis zum Ende des Vorgangs gehalten wurden und jede Sperre eine kleine Menge Speicher benötigt, war es möglich, dass dem Sperrmanager der Speicher ausging, wenn eine DROP oder TRUNCATE einer sehr großen Tabelle aufgetreten., Einige SQL Server-Kunden begannen zu finden, sie liefen in Out-of-Memory-Bedingungen auf SQL Server 2000, wie Tabellen wuchs sehr groß und übertrifft das Wachstum im Systemspeicher.
Der Deferred-Drop-Mechanismus simuliert dieDROP oderTRUNCATE-Operation, die sofort abgeschlossen wird, indem die Zuordnungen für die Tabelle aufgehoben und zur späteren Verarbeitung durch eine Hintergrundaufgabe in die ‚Deferred-Drop-Warteschlange‘ gestellt werden. Diese Unhook-and-Transfer-Operation erzeugt nur eine Handvoll von Protokolldatensätzen., Dies ist die Operation, die in meinem obigen Codebeispiel ausgeführt und zurückgesetzt wird.
Die ‚Deferred-Drop-Hintergrund-Task‘ dreht sich alle paar Sekunden und deallocates alle Seiten und Extents auf der Deferred-Drop-Warteschlange in kleinen Stapeln, um sicherzustellen, dass der Betrieb nicht aus dem Speicher laufen. Diese Deallokationen sind alle vollständig protokolliert, aber denken Sie daran, dass die Deallokation einer Seite voller Daten oder Indexdatensätze keine einzelnen Löschvorgänge dieser Datensätze protokolliert.Stattdessen wird die gesamte Seite nur in der entsprechenden PFS-Zuweisungsbyte-Map (Page Free Space) als Deallocated markiert.,
Wenn Sie ab SQL Server 2000 SP3 eine DROP oder einer Tabelle ausführen, werden nur einige Protokolldatensätze generiert. Wenn Sie etwa eine Minute warten und dann erneut im Transaktionsprotokoll nachsehen, werden Tausende von Protokolldatensätzen durch den Vorgang „Verzögertes Ablegen“ generiert, bei denen jeweils eine Seite oder ein Umfang freigegeben wird. Der Vorgang wird vollständig und effizient protokolliert.,
Hier ist ein Beispiel für das oben erstellte Szenario:
Ergebnisse:
—-
25
Wie Sie können sehen Sie, es gibt eindeutig keine Protokolldatensätze, die die Zuweisung der 10.000 Seiten plus 1.250 Extents in der TestTable-Tabelle aufheben.
Wenn ich einige Sekunden warte und dann den fn_dblog-Code erneut ausführe, erhalte ich:
— –
3811
Sie fragen sich vielleicht, warum es nicht mindestens 10.000 Protokolldatensätze gibt-einen für jede Seite, die freigegeben wird., Dies liegt daran, dass die Seitendeallokationen sogar effizient protokolliert werden – wobei ein Protokolldatensatz die Änderungen der PFS-Seitenzuweisung für 8 aufeinanderfolgende Datendateiseiten widerspiegelt, anstelle eines Protokolldatensatzes für jede Datendateiseite, der den Zuordnungsstatus widerspiegelt, der sich auf der PFS-Seite ändert.
SQL Server versucht immer, so wenig Transaktionsprotokolle wie möglich zu erstellen, wobei die Regeln für die vollständige oder minimale Protokollierung basierend auf dem aktuellen Wiederherstellungsmodell eingehalten werden., Wenn Sie sich die tatsächlichen Protokolldatensätze ansehen möchten, die von den Mechanismen zum Abhaken und Übertragen und Aufschieben generiert wurden, ersetzen Sie einfach * durch COUNT ( * ) im obigen fn_dblog-Code und suchen Sie nach einer Transaktion, deren Transaktionsname auf DeferredAllocUnitDrop::Process.
In zukünftigen Beiträgen werde ich die Interna diskutieren, die andere persistente Mythen um Leistungsaspekte der SQL Server Storage Engine untermauern.
Schreibe einen Kommentar