the Myth that DROP and TRUNCATE TABLE are Non-Logged

Er is een hardnekkige mythe in de SQL Server wereld dat zowel deDROP TABLE enTRUNCATE TABLE commando ‘ s niet-gelogd zijn.

dat zijn ze niet. Ze zijn beide volledig gelogd, maar efficiënt gelogd.

u kunt dit gemakkelijk aan uzelf bewijzen., Voer de volgende code om het opzetten van een test database en tabel, en tonen we hebben 10.000 rijen in onze tabel:

Uitslag:

En dan de volgende code, die kapt de tabel in een transactie en controles op de rij-count:

BEGIN TRAN;GOTRUNCATE TABLE ;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO

de Resultaten:

de tabel is leeg., Maar ik kan terugdraaien van de transactie en alle gegevens weer terug:

ROLLBACK TRAN;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO

de Resultaten:

Duidelijk de TRUNCATE bewerking die moet worden aangemeld, anders is de roll-back ‘ operatie niet zou werken.

dus waar komt de misvatting vandaan?

Het komt voort uit het gedrag van DROP en TRUNCATE operaties op grote tabellen., Ze zullen bijna onmiddellijk worden voltooid, en als u in het transactielogboek kijkt met fn_dblog direct daarna, ziet u slechts een klein aantal logrecords gegenereerd door de operatie. Dat kleine getal correleert niet met de grootte van de tabel die wordt afgekapt of weggelaten, dus het lijkt alsof DROP en TRUNCATE operaties niet gelogd zijn.

maar ze zijn volledig gelogd, zoals ik hierboven heb aangetoond. Waar zijn de logboeken van de operaties?,

het antwoord is dat de logrecords worden aangemaakt, alleen niet onmiddellijk, door een mechanisme genaamd ‘deferred drop’, dat werd toegevoegd in SQL Server 2000 SP3.

wanneer een tabel wordt geschrapt of afgekapt, moeten alle pagina ‘ s met gegevensbestanden die voor de tabel zijn toegewezen, worden gedeallocated.,uire een exclusieve toewijzing slot op de omvang
de Sonde de pagina te vergrendelen voor elke pagina in de mate (het verwerven van de sluis in de exclusieve modus, en onmiddellijk laat vallen en zorg ervoor dat niemand anders heeft de pagina geblokkeerd.
laat de mate van het slot, het garanderen dat niemand anders die kan gebruiken die omvang
Verplaats naar de volgende omvang
Einde

Als de mate sloten werden aangehouden tot het einde van de operatie en elk slot neemt een kleine hoeveelheid van het geheugen, was het mogelijk voor de lock manager voor het uitvoeren van het geheugen als een DROP of TRUNCATE van een zeer grote tabel opgetreden., Sommige SQL Server klanten begonnen te ontdekken dat ze liep in out-of-memory voorwaarden op SQL Server 2000, als tabellen groeide zeer groot en enorm overtrof de groei in het systeemgeheugen.

het deferred-drop mechanisme simuleert de operatie DROP of TRUNCATE onmiddellijk voltooien, door de toewijzingen voor de tabel los te koppelen en ze in de ‘deferred-drop queue’ te plaatsen, voor latere verwerking door een achtergrondtaak. Deze unhook-and-transfer operatie genereert slechts een handvol log records., Dit is de bewerking die wordt gedaan en teruggedraaid in mijn code voorbeeld hierboven.
De ‘deferred-drop background task’ draait om de paar seconden en dealloceert alle pagina ‘ s en breidt zich uit op de deferred-drop wachtrij in kleine batches, waardoor wordt gegarandeerd dat de operatie geen gebrek aan geheugen zal hebben. Deze deallocaties zijn allemaal volledig gelogd, maar onthoud dat deallocaties van een pagina vol met gegevens of indexrecords geen individuele verwijderingen van die records registreren; in plaats daarvan wordt de hele pagina gewoon gemarkeerd als deallocated in de relevante PFS (Page Free Space) allocation byte-map.,

vanaf SQL Server 2000 SP3, wanneer u een DROP of TRUNCATE van een tabel uitvoert, ziet u slechts een paar logrecords worden gegenereerd. Als u een minuut of zo wacht en vervolgens opnieuw in het transactielogboek kijkt, ziet u dat duizenden logboekrecords zijn gegenereerd door de uitgestelde-drop-operatie, waarbij elke deallocating een pagina of omvang heeft. De werking wordt volledig en efficiënt geregistreerd.,

Hier is een voorbeeld, met behulp van het bovenstaande scenario:

CHECKPOINT;GOTRUNCATE TABLE ;GOSELECT COUNT (*) AS N'LogRecCount'FROM fn_dblog (NULL, NULL);GO

resultaten:

zoals u kunt zien, zijn er duidelijk geen logrecords die de 10.000 pagina ‘ s, plus 1.250 extents in de testtabel weergeven.

als ik een paar seconden wacht, en dan de fn_dblog code opnieuw uitvoert, krijg ik:

u vraagt zich misschien af waarom er niet minstens 10.000 logrecords zijn – één voor elke pagina die wordt gedealloceerd., Dat komt omdat de paginadeallocaties zelfs efficiënt worden geregistreerd-met één logrecord dat wijzigingen in de PFS-paginaallocatie weergeeft voor 8 opeenvolgende pagina ‘ s met gegevensbestanden, in plaats van één logrecord voor elke pagina met gegevensbestanden die de toewijzingsstatus weergeeft die op de PFS-pagina wordt gewijzigd.

SQL Server probeert altijd zo weinig mogelijk transactielogboek te produceren, terwijl ze zich toch houdt aan de regels over volledige of minimale logboekregistratie gebaseerd op het huidige herstelmodel., Als u wilt kijken naar de werkelijke log records gegenereerd door de unhook-and-transfer en deferred-drop mechanismen, vervang gewoon * voor COUNT (*) in de fn_dblog code hierboven en zoek naar een transactie met de Transactienaam ingesteld op DeferredAllocUnitDrop::Process.

in toekomstige posts zal ik de internals bespreken die andere hardnekkige mythes ondersteunen rond prestatieaspecten van de SQL Server Storage Engine.