Delete, truncate e data pages

Delete o truncate ?
Quando abbiamo la necessità di rimuovere il contenuto della nostra tabella quale istruzione è più indicata ?
Ovviamente se abbiamo la necessità di rimuovere solo un insieme ristretto di righe è d'obbligo usare l'istruzione Delete,
mentre se è l'intero set di righe che vogliamo eliminare allora la truncate potrebbe fare al caso nostro....
Ma se elimino l'intero set di righe vengono eliminate anche le pagine che le contengono ?
La risposta è...... :-) ( se ve lo dico non andate avanti a leggere il post quindi vi tengo ancora un po sulle spine... ;-) )

Questo script sarà il punto 0 per ogni operazione successiva.

create Database MyDBReadWriteTest
Go

Use MyDBReadWriteTest
Go

if (OBJECT_ID('TabToDelete') is not null)
 Drop table TabToDelete
 
Create table TabToDelete
(
 id char(900),-- primary key clustered,
 valore char(7000)
)


Set nocount On
Declare @i int=1
While @i <= 1000
begin
 Insert into TabToDelete(id,valore) 
  values(CAST(@i as varchar(6)),'Valore che inserisco ' + CAST(@i as varchar(6)))
 
 Set @i += 1
end

Checkpoint
Dbcc dropcleanbuffers()

CASO 1: Delete su Clustered table

--Creiamo l'indice clustered
Create Clustered Index [Idx_TabToDelete] ON TabToDelete(Id)

Checkpoint
Dbcc dropcleanbuffers()

Select 'PRE DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')
Delete from TabToDelete
Select 'POST DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')

Come vedete il numero dei livelli dell'indice ed il numero delle pagine che compongono ogni livello è rimasto inalterato nonostante l'operazione di delete. Eseguiamo nuovamente questa query

Select 'POST DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')

Cosa è successo ? Com'è che adesso il numero delle pagine che compongono ogni livello è cambiato ? E' successo che SQL Server attraverso un processo asincrono chiamato Ghost Cleanup ha eliminato le pagine vuote ma non i livelli......
La domanda che ci siamo posti all'inizio del post è :
Ma se elimino l'intero set di righe vengono eliminate anche le pagine che le contengono ?
In questo caso la risposta è :
Si, vengono eliminate anche le pagine che le contengono ma in modo asincrono ed inoltre non viene intaccato il numero dei livelli del clustered index.
Rieseguiamo ora lo script punto 0 per ripreparare il nostro DB.

CASO 2: Truncate su Clustered table

--Creiamo l'indice clustered
Create Clustered Index [Idx_TabToDelete] ON TabToDelete(Id)

Checkpoint
Dbcc dropcleanbuffers()

Select 'PRE TRUNCATE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')
truncate table TabToDelete
Select 'POST TRUNCATE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')

Come potete vedere la struttura fisica della nostra tabella è cambiata radicalmente. Questo perchè la truncate dealloca le pagine che compongono la tabella.
La domanda che ci siamo posti all'inizio del post è :
Ma se elimino l'intero set di righe vengono eliminate anche le pagine che le contengono ?
In questo caso la risposta è :
Si, vengono eliminate anche le pagine che le contengono in modo sincrono.
1 a zero per la truncate !!!!

Rieseguiamo ora lo script punto 0 per ripreparare il nostro DB.

CASO 3: Delete su tabella Heap

Select 'PRE DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')
Delete from TabToDelete
Select 'POST DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')

Come vedete il numero delle pagine che compongono la tabella è rimasto inalterato nonostante l'operazione di delete. Eseguiamo nuovamente questa query

Select 'POST DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')

Non è cambiato nulla ! E il processo asincrono Ghost cleanup ? Non è intervenuto..... Quindi le nostre pagine restano allocate !!!
La domanda che ci siamo posti all'inizio del post è :
Ma se elimino l'intero set di righe vengono eliminate anche le pagine che le contengono ?
In questo caso la risposta è :
No,non vengono eliminate le pagine.
2 a zero per la truncate !!!!

Rieseguiamo ora lo script punto 0 per ripreparare il nostro DB.

CASO 4: Delete con query Hint Tablock su tabella Heap

Select 'PRE DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')
Delete from TabToDelete with(Tablock)
Select 'POST DELETE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')

Come potete vedere in questo caso le pagine sono state deallocate. Questo perchè SQL Server al posto di lock di tipo row o page impone, grazie all'hint Tablock un unico lock shared a livello di tabella, rendendo di fatto possibile la deallocazione delle pagine in modalità sincrona, esattamente come la truncate.
La domanda che ci siamo posti all'inizio del post è :
Ma se elimino l'intero set di righe vengono eliminate anche le pagine che le contengono ?
In questo caso la risposta è :
No,non vengono eliminate le pagine a meno che non si usi il query hint Tablock. Per questo motivo do un punto alla delete ;-)
2 a 1 ancora per la truncate !!!!
Rieseguiamo ora lo script punto 0 per ripreparare il nostro DB.

CASO 5: Truncate su tabella Heap

Select 'PRE TRUNCATE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')
Truncate table TabToDelete
Select 'POST TRUNCATE'
Select index_type_desc,index_level,page_count,record_count from sys.dm_db_index_physical_stats(db_id(),OBJECT_ID('TabToDelete'),null,null,'detailed')

La truncate anche sull'heap dealloca totalmente le pagine.....

La domanda che ci siamo posti all'inizio del post è :
Ma se elimino l'intero set di righe vengono eliminate anche le pagine che le contengono ?
In questo caso la risposta è :
Ancora assolutamente si.
3 a 1 ancora per la truncate !!!!

Ok, quindi 3 a 1 per la truncate....
Vero, peccato che la truncate non posso usarla su tabelle referenziate da foreign key.
Non posso eseguirla su tabelle coinvolte in viste indicizzate o pubblicate con repliche Merge o Transazionali.
Posso eseguirla solo se faccio parte del ruolo db_owner, db_ddladmin, sysadmin oppure se ho l'autorizzazione di Alter sulla tabella in questione.
La Delete invece posso eseguirla se faccio parte del ruolo DB_Datawriter e cosa molto importante posso usarla per filtrare i dati da eliminare.
Se poi uso il query hint Tablock dealloco le pagine totalmente vuote come la truncate e questo comportamento lo posso combinare anche con le condizioni di ricerca (Where).

Valutiamo quindi di volta in volta come procedere.

Ciao

Luca