det finns en ihållande myt i SQL Server-världen att både DROP TABLE och TRUNCATE TABLE kommandon inte loggas.
det är de inte. De är båda helt inloggade, men loggas effektivt.
Du kan enkelt bevisa detta för dig själv., Kör följande kod för att ställa in en testdatabas och tabell och visa att vi har 10 000 rader i tabellen:
resultat:
—-
10000
och sedan följande kod, som trunkerar tabellen i en transaktion och kontrollerar radräkningen:
BEGIN TRAN;GOTRUNCATE TABLE ;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO
BEGIN TRAN;GOTRUNCATE TABLE ;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO
resultat:
—-
0
Nu är tabellen tom., Men jag kan rulla tillbaka transaktionen och lägga tillbaka Alla data igen:
ROLLBACK TRAN;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO
resultat:
—-
10000
tydligt TRUNCATE operation måste loggas annars roll back operation skulle inte fungera.
så var kommer missuppfattningen från?
det kommer från beteendet hosDROP ochTRUNCATE operationer på stora tabeller., De kommer att slutföras nästan omedelbart, och om du tittar i transaktionsloggen med fn_dblog direkt efteråt ser du bara ett litet antal loggposter som genereras från operationen. Det lilla numret korrelerar inte med storleken på tabellen som trunkeras eller tappas, så det verkar som om DROP och TRUNCATE-operationer inte loggas.
men de är helt inloggade, som jag visade ovan. Så var är loggposterna för verksamheten?,
svaret är att loggposterna kommer att skapas, bara inte omedelbart, av en mekanism som heter ”uppskjuten droppe”, som lades till i SQL Server 2000 SP3.
När en tabell släpps eller trunkeras måste alla datafilsidor som allokeras för tabellen avallokeras.,uire en exklusiv fördelning lås på mån
Sond page lock för varje sida i den utsträckning (skaffa lås i exklusivt läge, och omedelbart släppa det, att se till att ingen annan har sidan låst)
INTE släppa den utsträckning lock, vilket garanterar att ingen annan kan använda den mån
Flytta till nästa mån
Slut
Som alla utsträckning lås hölls fram till slutet av operationen, och varje lås tar en liten mängd minne, var det möjligt för lås-manager för att köra slut på minne när en DROP eller TRUNCATE av en stor tabell inträffat., Vissa SQL Server-kunder började hitta att de sprang in i minnesförhållanden på SQL Server 2000, eftersom tabellerna växte mycket stora och överträffade kraftigt tillväxten i systemminnet.
den uppskjutna släppmekanismen simulerarDROP ellerTRUNCATE åtgärden Slutför omedelbart, genom att avmarkera tilldelningarna för tabellen och lägga dem i ”uppskjuten-drop kö”, för senare bearbetning av en bakgrundsuppgift. Denna unhook-and-transfer operation genererar bara en handfull loggposter., Det här är den operation som görs och rullas tillbaka i mitt kodexempel ovan.
den ”uppskjutna-släpp bakgrundsuppgiften” snurrar upp några sekunder och avallokerar alla sidor och extents på den uppskjutna-släpp-kön i små satser, vilket garanterar att operationen inte kommer att gå ut ur minnet. Dessa avallokeringar är alla helt loggade, men kom ihåg att avallokering av en sida full av data-eller indexposter inte loggar enskilda raderingar av dessa poster; istället är hela sidan bara markerad som avallokerad i relevant PFS (Page Free Space) allokering byte-karta.,
från SQL Server 2000 SP3 och framåt, när du utför en DROP eller TRUNCATE I en tabell, visas bara några loggposter som genereras. Om du väntar en minut eller så, och sedan tittar i transaktionsloggen igen, ser du tusentals loggposter har genererats av uppskjuten-släpp-operation, varje avallokera en sida eller omfattning. Operationen är helt och effektivt inloggad.,
här är ett exempel med hjälp av scenariot vi skapade ovan:
CHECKPOINT;GOTRUNCATE TABLE ;GOSELECT COUNT (*) AS N'LogRecCount'FROM fn_dblog (NULL, NULL);GO
resultat:
—-
25
som du kan det finns uppenbarligen inte loggposter som avallokerar 10 000 sidor, plus 1 250 förlängningar i testtable-tabellen.
om jag väntar några sekunder och sedan kör fn_dblog-koden igen Får jag:
— –
3811
Du kanske undrar varför det inte finns minst 10 000 loggposter-en för varje sida som avallokeras., Det beror på att sidans avallokeringar loggas effektivt-med en loggpost som återspeglar PFS-sidans allokeringsändringar för 8 på varandra följande datafilsidor, istället för en loggpost för varje datafilsida som återspeglar dess allokeringsstatus som ändras på PFS-sidan.
SQL Server försöker alltid att producera så lite transaktionslogg som möjligt, samtidigt som man följer reglerna om fullständig eller minimal loggning baserat på den aktuella återställningsmodellen., Om du vill titta på de faktiska loggposterna som genereras av mekanismerna unhook-and-transfer och uppskjuten-drop, ersätt bara * för COUNT ( * ) i fn_dblog-koden ovan och leta efter en transaktion med Transaktionsnamnet inställt på DeferredAllocUnitDrop::Process.
i framtida inlägg diskuterar jag de interna som ligger till grund för andra ihållande myter kring prestandaaspekter av SQL Server-lagringsmotorn.
Lämna ett svar