Il mito secondo cui la TABELLA DROP e TRUNCATE non sono registrate

Esiste un mito persistente nel mondo SQL Server secondo cui entrambi i comandi DROP TABLEe TRUNCATE TABLE non sono registrati.

Non lo sono. Sono entrambi completamente registrati, ma registrati in modo efficiente.

Puoi facilmente dimostrarlo a te stesso., Eseguire il codice riportato di seguito per impostare un database di test e tavolo, e mostra abbiamo 10.000 righe nella nostra tabella:

i Risultati:

E poi il codice seguente, che tronca la tabella in una transazione e controlla il numero di riga:

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

i Risultati:

Ora la tabella è vuota., Ma posso eseguire il rollback della transazione e di mettere tutti i dati di nuovo:

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

i Risultati:

Chiaramente il TRUNCATE operazione deve essere registrato in caso contrario il roll back operazione non avrebbe funzionato.

Quindi da dove viene l’idea sbagliata?

Deriva dal comportamento delle operazioniDROP eTRUNCATE su tabelle di grandi dimensioni., Verranno completati quasi istantaneamente e, se si guarda nel log delle transazioni utilizzando fn_dblog subito dopo, verrà visualizzato solo un piccolo numero di record di registro generati dall’operazione. Quel piccolo numero non è correlato con la dimensione della tabella che viene troncata o eliminata, quindi sembra che le operazioni DROP e TRUNCATE non siano registrate.

Ma sono completamente registrati, come ho dimostrato sopra. Allora, dove sono i record di registro per le operazioni?,

La risposta è che i record di registro verranno creati, solo non immediatamente, da un meccanismo chiamato “goccia differita”, che è stato aggiunto in SQL Server 2000 SP3.

Quando una tabella viene eliminata o troncata, tutte le pagine del file di dati allocate per la tabella devono essere deallocate.,uire un esclusivo allocazione del blocco su misura
Sonda il blocco di pagina per ogni pagina a misura (di acquisire il blocco in modalità esclusiva, e subito caduta, assicurandosi che nessun altro ha la pagina bloccata)
NON rilasciare la misura di blocco, garantendo che nessun altro può utilizzare tale misura
passare alla successiva misura
Fine

Come tutte le entità blocchi sono stati mantenuti fino alla fine dell’operazione, e ogni blocco richiede una piccola quantità di memoria, è stato possibile per il gestore di blocco di memoria insufficiente quando un DROP o TRUNCATE di una tabella molto grande si è verificato., Alcuni clienti di SQL Server hanno iniziato a scoprire che si sono imbattuti in condizioni di memoria esaurita su SQL Server 2000, poiché le tabelle sono cresciute molto e hanno superato di gran lunga la crescita della memoria di sistema.

Il meccanismo deferred-drop simula l’operazione DROP o TRUNCATE completando immediatamente, sganciando le allocazioni per la tabella e mettendole nella ‘coda deferred-drop’, per una successiva elaborazione da parte di un’attività in background. Questa operazione di sganciamento e trasferimento genera solo una manciata di record di registro., Questa è l’operazione che viene eseguita e ripristinata nel mio esempio di codice sopra.
Il’ deferred-drop background task ‘ gira verso l’alto ogni pochi secondi e dealloca tutte le pagine e le estensioni sulla coda deferred-drop in piccoli lotti, garantendo che l’operazione non esaurirà la memoria. Queste deallocazioni sono tutte completamente registrate, ma ricorda che la deallocazione di una pagina piena di dati o record di indice non registra le singole eliminazioni di tali record; invece l’intera pagina viene semplicemente contrassegnata come deallocata nella relativa mappa di byte di allocazione PFS (Page Free Space).,

Da SQL Server 2000 SP3 in poi, quando si esegue un DROPo TRUNCATE di una tabella, verranno generati solo alcuni record di registro. Se attendi un minuto o giù di lì e poi guardi di nuovo nel log delle transazioni, vedrai che migliaia di record di log sono stati generati dall’operazione di rilascio differito, ciascuno deallocando una pagina o un’estensione. L’operazione viene registrata in modo completo ed efficiente.,

Ecco un esempio, utilizzando lo scenario che abbiamo creato in precedenza:

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

i Risultati:

Come potete vedere, ci sono, chiaramente, non i record di log di deallocazione di 10.000 pagine, più di 1.250 extent in Tabellaprova tabella.

Se aspetto qualche secondo e poi eseguo di nuovo il codice fn_dblog, ottengo:

Potresti chiederti perché non ci sono almeno 10.000 record di log – uno per ogni pagina che viene deallocata., Questo perché le deallocazioni delle pagine vengono anche registrate in modo efficiente, con un record di registro che riflette le modifiche di allocazione delle pagine PFS per 8 pagine di file di dati consecutive, invece di un record di registro per ogni pagina di file di dati che riflette il suo stato di allocazione che cambia nella pagina PFS.

SQL Server cerca sempre di produrre il minor numero possibile di log delle transazioni, pur rispettando le regole sulla registrazione completa o minima in base al modello di ripristino corrente., Se si desidera esaminare i record di registro effettivi generati dai meccanismi unhook-and-transfer e deferred-drop, è sufficiente sostituire * per COUNT ( * ) nel codice fn_dblog sopra e cercare una transazione con il nome della transazione impostato su DeferredAllocUnitDrop::Process.

Nei post futuri discuterò gli interni che sono alla base di altri miti persistenti sugli aspetti delle prestazioni del motore di archiviazione di SQL Server.