Truncate versus Delete – Uma explicação mais detalhada

Boa Noite Pessoal,

Na semana passada ministrei o curso 2778 (Writing Queries Using Microsoft SQL Server 2008 Transact-SQL). É um curso bem tranquilo e destinado a iniciantes em consultas no SQL Server. Essa semana iniciei uma outra turma desse curso e me parece que as expectativas são enormes (vou levar o máximo de material complementar que conseguir). Aproveitando a experiência da turma passada e anteriores, notei que em um dos capítulos havia um comparativo entre o DELETE e o TRUNCATE. A comparação era muito superficial que fiquei motivado a falar um pouco sobre eles com alguns exemplos práticos de forma a complementar o curso já que não há tempo para falar tudo o que eu gostaria em apenas seis dias. Claro que não vou simplesmente dizer que o truncate é mais rápido, mas que o DELETE aceita cláusula WHERE (isso já está no MOC e no Books OnLine). Vejamos um pouco mais de perto seus comportamentos e efeitos colaterais bem como alguns mitos que merecem ser esclarescidos.

Operações minimamente logadas

O log de transações do SQL Server loga as operações de forma detalhada, pois, necessita desses detalhes para recuperar-se de uma falha, efetuar os processos de REDO exigidos em uma operação de restauração, bem como permitir o funcionamento de uma série de outras features como replicação, chance tracking, change data capture, Database Snapshot, etc. A necessidade do log prover detalhes para que o SQL Server possa trabalhar é inquestionável, mas qual será o nível ideal ? Algumas operações não podem deixar de ser completamente logadas, mas outras talvez pudessem ter seu nível de detalhe reduzido ao mínimo possível para garantir a recuperação quando necessária. Quando uma operação pode ter seu nível de detalhe reduzido ela é conhecida como uma operação minimamente logada (Operations That Can Be Minimally Logged). Alguns exemplos de operações minimamente logadas incluem:

  • Importações de dados utilizando o comando BULK INSERT ou o utilitário BCP
  • Criação de tabelas com o uso do comando SELECT INTO
  • Criação de índices
  • Atualizações com os comandos WRITETEXT e UPDATETEXT

Quando uma dessas operações é executada, o SQL Server efetua todo o trabalho necessário, mas ao invés de logar cada linha alterada no log de transações, ele apenas faz uma anotação dizendo que a operação foi realizada. Isso pode soar a primeira vista uma perda de controle e uma possibilidade dele não conseguir recuperar-se uma vez que apenas fez uma anotação sem gravar a riqueza de detalhes que normalmente faz (linhas, partições, objetos afetados, etc). De fato, se somente uma anotação fosse feita haveria esse risco, mas além da simples anotação de que um índice foi criado ou que uma tabela foi carregada, o controle das extents alteradas também é mantido através de estruturas do tipo Bulk Change Map (BCM). A construção de um índice em um modo full logged incorreria em gravar todas as entradas de índice no log enquanto que a mesma operação em um modo minimamente logado incorreria apenas em anotar que um índice foi criado e que as extensões X, Y, Z foram afetadas. Naturalmente que a segunda operação é bem mais rápida de maneira proporcional à quantidade de registros afetados. Para que operações minimamente logadas sejam de fato minimamente logadas é necessário que o RECOVERY MODEL do banco de dados esteja BULK_LOGGED. Se essa propriedade estiver marcada como FULL ou SIMPLE todas as operações serão FULL LOGGED, ou seja, com o nível de detalhe máximo mesmo que nem todas as informações sejam indispensável para o processo de recuperação. Isso pode induzir que o BULK LOGGED é sempre a melhor escolha, mas certamente existem alguns efeitos colaterais (pretendo discorrer sobre eles em algum artigo futuro).

Operações de exclusão de dados

O comportamento de exclusão de dados tem muito em comum com as características de operações minimamente logadas no sentido de tentar incorrer no mínimo possível de informações no log de transações de forma a garantir a recuperação e consistência de um banco de dados SQL Server. Afinal, se um objeto está sendo excluído como um todo qual seria o objetivo de logar os registros que foram excluídos e não participam mais do banco de dados ? Isso não traria benefícios à recuperação do banco de dados já que os registros foram completamente excluídos. Logar de forma completa uma operação de exclusão de um objeto iria apenas aumentar drasticamente o tamanho usado pelo log de transações além de retardar a operação como um todo.

Uma operação como DROP TABLE poderia “logar” todas as linhas de uma tabela juntamente com os metadados da tabela, mas isso seria um grande desperdício. Se a tabela foi excluída, qual o sentido de logar seus metadados e os registros excluídos ? Ela foi eliminada e não é mais relevante para o banco de dados sendo assim, logar cada registro da tabela em um log de transações só representaria um enorme desperdício de espaço no log de transações bem como um grande lentidão para tabelas muito volumosas. Uma operação de DROP TABLE eficiente não irá “logar” o que é desnecessário. Ela irá simplesmente anotar no log de transações que uma determinada tabela foi excluída e adicionalmente irá incluir a lista de páginas e extensões afetadas para que posteriormente a tabela seja de fato excluída no arquivo MDF. Essas informações são suficientes para garantir a recuperação do banco de dados, pois, caso alguma operação de REDO seja necessária, ela irá simplesmente garantir a exclusão das extensões já mapeadas.

O comando TRUNCATE TABLE e o comando DROP TABLE tem os mesmos princípios, ou seja, irão eliminar completamente todos os registros da tabela. A única diferença é que o TRUNCATE TABLE irá ficar restrito aos dados, ou seja, ele irá eliminar todos os registros, mas irá deixar a tabela vazia enquanto o DROP TABLE irá eliminar a tabela como objeto, ou seja, seus metadados. Em ambos os comandos, não haverá registro linha a linha dos dados no log de transações. Ambos irão apenas se preocupar em mapear as páginas e extensões utilizadas e irá se encarregar de eliminá-las completamente no banco de dados o que comumente é uma operação bem mais rápida. É por isso que comandos de TRUNCATE e DROP TABLE levam segundos para serem executados mesmo contra tabelas muito grandes. Isso é verdadeiro independente do RECOVERY MODEL utilizado.

O comando DELETE pode ser utilizado para eliminar todas as linhas de uma tabela de forma semelhante ao TRUNCATE TABLE, mas ao contrário do TRUNCATE, ele tem necessidade de “logar” cada linha excluída sendo portanto uma operação bem mais lenta e normalmente impõe mais bloqueios podendo prejudicar atividades concorrentes. Se o TRUNCATE é mais eficiente que o DELETE justamente por registrar suas atividades no log de transações de forma mínima, por que o DELETE tem de ser tão lento ? A razão chama-se cláusula WHERE. O comando DELETE suporta o uso da cláusula WHERE para selecionar os registros que devem ser excluídos enquanto que o TRUNCATE TABLE não contempla essa possibilidade. Quando se deseja excluir alguns registros específicos não é possível efetuar uma exclusão de forma mínima, pois, uma operação de DELETE pode afetar vários registros de vários blocos, mas não necessariamente irá afetar todos os registros de todos os blocos e por isso é necessário realmente logar linha a linha de forma detalhada, o que irá incorrer em mais uso do log de transações e por consequência maior lentidão.

Um exemplo prático

Para evidenciar o comportamento do TRUNCATE, elaborei um rápido script para verificar que o TRUNCATE realmente é uma operação que faz as gravações no log de forma mínima enquanto o DELETE realmente tem a necessidade de registrar cada linha excluída no log de transações.

— Cria um banco de dados
CREATE DATABASE BDTD

— Muda o RECOVERY MODEL
ALTER DATABASE BDTD SET RECOVERY BULK_LOGGED

— Faz um Backup Full para "forçar" o comportamento FULL Logged após a criação do banco
BACKUP DATABASE BDTD TO DISK = ‘D:\SQL Server\Backups\BDTD.BAK’

— Muda o contexto de banco de dados
USE BDTD;

— Verifica o tamanho do arquivo de Log
SELECT name, physical_name, size * 8 as sizekb
FROM sys.database_files

O comando acima apenas captura o tamanho dos arquivos do banco BDTD. Irei utilizar essas referências para comparações futuras:

name physical_name sizekb
BDTD D:\SQL Server\DBs\BDTD.MDF 2304
BDTD_log D:\SQL Server\DBs\BDTD_log.LDF 576

— Cria uma tabela com 100 mil registros
CREATE TABLE T1 (Num INT, Nome CHAR(2000))

DECLARE @i INT = 1

WHILE @i <= 100000
BEGIN
    INSERT INTO
T1 VALUES (@i,REPLICATE(CHAR(@i),2000))
    SET @i += 1
END

— Verifica o tamanho do arquivo de Log
SELECT name, physical_name, size * 8 as sizekb
FROM sys.database_files

name physical_name sizekb
BDTD D:\SQL Server\DBs\BDTD.MDF 268544
BDTD_log D:\SQL Server\DBs\BDTD_log.LDF 297024

Podemos perceber um significativo aumento nos arquivos de dados e de log do banco BDTD. O aumento do arquivo de dados é óbvio já que a tabela T1 foi populada com 100.000 registros. O aumento do log ocorreu porque foi necessário logar esses registros.

— Cria uma réplica de T1
SELECT Num, Nome INTO T2 FROM T1

— Verifica o tamanho do arquivo de Log
SELECT name, physical_name, size * 8 as sizekb
FROM sys.database_files

name physical_name sizekb
BDTD D:\SQL Server\DBs\BDTD.MDF 469248
BDTD_log D:\SQL Server\DBs\BDTD_log.LDF 297024

Como podemos ver, o fato do RECOVERY MODEL estar como Bulk_Logged realmente permitiu a criação de uma tabela idêntica a T1, porém sem o overhead do aumento do tamanho do log de transações que continuou com o mesmo tamanho (297MB aproximadamente). O mesmo não seria verdade se o Recovery Model utilizado fosse o FULL. Nesse caso, certamente o tamanho do log seria aumentado significativamente.

— Declara duas variáveis
DECLARE @InicioDelete DATETIME2, @FimDelete DATETIME2
SET @InicioDelete = SYSDATETIME()

— Exclui todos os registros de T1 com DELETE
DELETE FROM T1

SET @FimDelete = SYSDATETIME()

SELECT DATEDIFF(MS,@InicioDelete,@FimDelete) As Tempo

— Verifica o tamanho do arquivo de Log
SELECT name, physical_name, size * 8 as sizekb
FROM sys.database_files

name physical_name sizekb
BDTD D:\SQL Server\DBs\BDTD.MDF 469248
BDTD_log D:\SQL Server\DBs\BDTD_log.LDF 770752

O comando de DELETE completou em aproximadamente 15 segundos e o log cresceu bastante, pois, foi necessário logar completamente todas as linhas excluídas. Como o DELETE não pode ser configurado como uma operação minimamente logada, mesmo o recovery model estando configurado como Bulk_Logged não pode reduzir o overhead gerado pelo comando.

— Altera o Recovery Model para FULL
ALTER DATABASE BDTD SET RECOVERY FULL

— Declara duas variáveis
DECLARE @InicioTruncate DATETIME2, @FimTruncate DATETIME2
SET @InicioTruncate = SYSDATETIME()

— Exclui todos os registros de T2 com TRUNCATE
TRUNCATE TABLE T2

SET @FimTruncate = SYSDATETIME()

SELECT DATEDIFF(MS,@InicioTruncate,@FimTruncate) As Tempo

— Verifica o tamanho do arquivo de Log
SELECT name, physical_name, size * 8 as sizekb
FROM sys.database_files

name physical_name sizekb
BDTD D:\SQL Server\DBs\BDTD.MDF 469248
BDTD_log D:\SQL Server\DBs\BDTD_log.LDF 770752

O comando de TRUNCATE rodou em 3 milissegundos e mesmo o recovery model configurado como FULL não fez com que as operações efetuadas pelo truncate aumentassem o log de transações. Isso já era esperado, pois, o truncate grava operações de forma mínima no log de transações sempre independente do recovery model. Podemos ver que o tamanho do log de transações permanecesse inalterado. Como podemos ver o TRUNCATE realmente é muito mais eficiente, pois, desalocar páginas e extensões é um processo muito rápido principalmente quando não necessita de logar todas as linhas excluídas como é o caso do DELETE.

Truncate e Transações

Um mito comum em relação ao comando TRUNCATE é achar que ele não pode fazer parte de uma transação. De fato há vários comandos "drásticos" que não podem ser encapsulados em transações. De acordo com a lista na documentação da Microsoft (Transact-SQL Statements Allowed in Transactions), o comando de TRUNCATE é permitido em transações e funciona perfeitamente.

— Muda o contexto de banco de dados
USE BDTD

— Cria uma tabela T3
CREATE TABLE T3 (Num INT, Nome CHAR(2000))

— Insere três registros
INSERT INTO T3 (Num, Nome) VALUES (1,REPLICATE(‘1’,2000))
INSERT INTO T3 (Num, Nome) VALUES (2,REPLICATE(‘2’,2000))
INSERT INTO T3 (Num, Nome) VALUES (3,REPLICATE(‘3’,2000))

— Declara duas variáveis
DECLARE @qtdAnterior INT, @qtdPosterior INT

— Inicia uma transação
BEGIN TRAN

    — Conta o total de registros antes do TRUNCATE
    SELECT @qtdAnterior = COUNT(*) FROM T3

    — Trunca a tabela T3
    TRUNCATE TABLE T3

    — Conta o total de registros após o TRUNCATE
    SELECT @qtdPosterior = COUNT(*) FROM T3

ROLLBACK

— Exibe os resultados
SELECT @qtdAnterior As Antes, @qtdPosterior As Depois, COUNT(*) As Atual FROM T3

Como podemos ver o uso do TRUNCATE em transações não representa nenhum problema. Esse comando pode ser transacionado normalmente. Não é de se admirar, pois, há outros comandos mais "drásticos" que também podem ser transacionados.

— Muda o contexto de banco de dados
USE BDTD

— Inicia uma transação
BEGIN TRAN

    — Exclui a tabela T3
    DROP TABLE T3

ROLLBACK

— Seleciona os registros
SELECT Num, Nome FROM T3

É possível transacionar usando o TRUNCATE e outras instruções, mas é preciso estar ciente que o uso do TRUNCATE (por ser considerada uma instrução DDL) exigirá um bloqueio exclusivo do objeto e mesmo a utilização de HINTs ou mudança nos níveis de isolamento não é capaz de acessar o objeto alvo do comando de TRUNCATE enquanto a transação não for finalizada. Vejamos isso na prática.

— Muda o contexto de banco de dados
USE BDTD

— Cria uma tabela T4
CREATE TABLE T4 (Num INT, Nome CHAR(2000))

— Insere três registros
INSERT INTO T4 (Num, Nome) VALUES (1,REPLICATE(‘1’,2000))
INSERT INTO T4 (Num, Nome) VALUES (2,REPLICATE(‘2’,2000))
INSERT INTO T4 (Num, Nome) VALUES (3,REPLICATE(‘3’,2000))

Para demonstrar o uso do TRUNCATE irei utilizar duas sessões de forma concorrente, respectivamente sessão 1 e sessão 2. É recomendável colar o código nas respectivas sessões antes de executá-los.

— Sessão 1
— Muda o contexto de banco de dados

USE BDTD

— Abre uma transação
BEGIN TRAN

    — Trunca a tabela T3
    TRUNCATE TABLE T3

    — Atualiza a tabela T4
    UPDATE T4 SET Num = CHECKSUM(Nome)

    — Força uma espera de 10 segundos
    WAITFOR DELAY ’00:00:10′

— Desfaz a transação
ROLLBACK

— Sessão 2
— Muda o contexto de banco de dados

USE BDTD

— Acessa a tabela T4 com leitura suja
SELECT ‘T4’ As Tabela, Num, Nome FROM T4 WITH (NOLOCK)

— Acessa tabela T3 com leitura suja
SELECT ‘T3’ As Tabela, Num, Nome FROM T3 WITH (NOLOCK)

— Aguarda dez segundos e executa os SELECT novamente
WAITFOR DELAY ’00:00:10′

SELECT ‘T4’ As Tabela, Num, Nome FROM T4
SELECT ‘T3’ As Tabela, Num, Nome FROM T3

A execução das consultas de forma concorrente tem alguns resultados curiosos. Quando a sessão 2 se inicia podemos ver que a leitura suja da tabela T4 realmente foi feita com sucesso (o update com CHECKSUM foi lido mesmo que não comitado).

Tabela Num Nome
T4 84215045 11111111111111111111111111111111
T4 117901063 22222222222222222222222222222222
T4 -752037076 33333333333333333333333333333333

Embora a leitura do TRUNCATE seja feita de forma suja, ou seja, com o uso do NOLOCK, o comando não é retornado e fica bloqueado. Não é à toa que a sessão 2 demora mais de dez segundos para ser finalizada. É necessário que a sessão 1 finalize (10 segundos) para que a sessão 2 possa prosseguir. Após o término da sessão 1, os resultados da sessão 2 são exibidos:

Tabela Num Nome
T3 1 11111111111111111111111111111111
T3 2 22222222222222222222222222222222
T3 3 33333333333333333333333333333333

Tabela Num Nome
T4 1 11111111111111111111111111111111
T4 2 22222222222222222222222222222222
T4 3 33333333333333333333333333333333

Tabela Num Nome
T3 1 11111111111111111111111111111111
T3 2 22222222222222222222222222222222
T3 3 33333333333333333333333333333333

Podemos ver que a leitura suja em T3 não foi possível. Os retornos referentes à T3 são idênticos e contemplam todos os três registros, ou seja, o SELECT não foi capaz de ler a tabela após os efeitos do TRUNCATE (mesmo antes do ROLLBACK). Isso porque o TRUNCATE bloqueia requisições de SELECT mesmo com o uso do HINT NOLOCK. Se usássemos o DELETE isso não iria acontecer.

— Sessão 1
— Muda o contexto de banco de dados

USE BDTD

— Abre uma transação
BEGIN TRAN

    — Trunca a tabela T3
    DELETE FROM T3

    — Atualiza a tabela T4
    UPDATE T4 SET Num = CHECKSUM(Nome)

    — Força uma espera de 10 segundos
    WAITFOR DELAY ’00:00:10′

— Desfaz a transação
ROLLBACK

— Sessão 2
— Muda o contexto de banco de dados

USE BDTD

— Acessa a tabela T4 com leitura suja
SELECT ‘T4’ As Tabela, Num, Nome FROM T4 WITH (NOLOCK)

— Acessa tabela T3 com leitura suja
SELECT ‘T3’ As Tabela, Num, Nome FROM T3 WITH (NOLOCK)

— Aguarda dez segundos e executa os SELECT novamente
WAITFOR DELAY ’00:00:10′

SELECT ‘T4’ As Tabela, Num, Nome FROM T4
SELECT ‘T3’ As Tabela, Num, Nome FROM T3

Tabela Num Nome
T4 84215045 11111111111111111111111111111111
T4 117901063 22222222222222222222222222222222
T4 -752037076 33333333333333333333333333333333

Tabela Num Nome

Tabela Num Nome
T4 1 11111111111111111111111111111111
T4 2 22222222222222222222222222222222
T4 3 33333333333333333333333333333333

Tabela Num Nome
T3 1 11111111111111111111111111111111
T3 2 22222222222222222222222222222222
T3 3 33333333333333333333333333333333

Nesse caso, quando a sessão 1 iniciou ela atualizou a tabela T4 e excluiu todos os registros de T3. A sessão 2 leu essas alterações mesmo antes delas terem sido comitadas já que o uso do NOLOCK foi utilizado. Após dez segundos a sessão 1 finalizou e quando a sessão 2 leu os dados as transações já estavam desfeitas. Isso evidencia que o TRUNCATE pode provocar bloqueios que o DELETE não provocaria, mas não podemos dizer que ele é prejudicial à concorrência, pois, o uso do TRUNCATE normalmente é executado de forma muito rápida. Ainda assim, por ser uma operação caracterizada como DDL é desejável evitar utilizá-la em transações frequentes principalmente se essas transações demorarem para finalizar.

Olhando um pouco mais de perto

Para entender um pouco mais sobre os efeitos do DELETE e do UPDATE vejamos como os recursos bloqueados se comportam em relação a esses dois comandos. O primeiro passo é repopular as tabelas T1 e T2 excluídas nos passos anteriores. Farei com uma quantidade menor de registros já que o objetivo é entender os bloqueios e não mais medir desempenho e efeitos no log de transações.

— Muda o RECOVERY MODEL
ALTER DATABASE BDTD SET RECOVERY SIMPLE

— Popula a tabela T1 e T2 com 10 registros
DECLARE @i INT = 1

WHILE @i <= 10
BEGIN
    INSERT INTO T1 VALUES (@i,REPLICATE(CHAR(@i),2000))
    INSERT INTO T2 VALUES (@i,REPLICATE(CHAR(@i),2000))
    SET @i += 1
END

As consultas a seguir serão dividas em duas sessões para que os efeitos possam ser melhor analisados.

— Sessão 1
— Muda o contexto de banco de dados

USE BDTD

— Abre uma transação
BEGIN TRAN

    — Exclui todos os registros de T1 via DELETE
    DELETE FROM T1

    — Aguarda 10 segundos
    WAITFOR DELAY ’00:00:10′

— Desfaz a transação
ROLLBACK

— Sessão 2
— Verifica os recursos bloqueados

SELECT
    resource_type As Recurso, resource_description As Descricao,
    resource_associated_entity_id As Entidade, request_mode As Modo
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID(‘BDTD’) AND resource_type != ‘DATABASE’

O resultado dos recursos bloqueados pode ser descrito na tabela abaixo (é possível que eles possam variar):

Recurso Descrição Entidade Modo
RID 1:89:0 72057594038779904 X
PAGE 1:89 72057594038779904 IX
RID 1:127:0 72057594038779904 X
PAGE 1:127 72057594038779904 IX
PAGE 1:151 72057594038779904 IX
RID 1:151:0 72057594038779904 X
RID 1:174:0 72057594038779904 X
PAGE 1:174 72057594038779904 IX
RID 1:89:1 72057594038779904 X
RID 1:127:1 72057594038779904 X
RID 1:151:1 72057594038779904 X
RID 1:89:2 72057594038779904 X
RID 1:127:2 72057594038779904 X
RID 1:151:2 72057594038779904 X
OBJECT   2105058535 IX

Podemos visualizar que o comando de DELETE realmente atua apenas sobre dados. No caso, as dez linhas estão bloqueadas exclusivamente e suas respectivas páginas estão com um bloqueio de intensão de exclusividade juntamente com o objeto. Algumas consultas confirmam que realmente se trata da tabela T1.

— Habilita a leitura de página
DBCC TRACEON(3604)

— Verifica o DB_ID da base
SELECT DB_ID(‘BDTD’) As ID

— Lê a primeira página (substitua o 5 pelo ID na consulta anterior)
DBCC PAGE(5,1,89,3)

PAGE HEADER:

Page @0x0000000081A6E000

m_pageId = (1:89)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 38     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594040418304                                
Metadata: PartitionId = 72057594038779904                                 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 2008                       m_slotCnt = 3                        m_freeCnt = 2057
m_freeData = 6129                    m_reservedCnt = 0                    m_lsn = (1023:704:21)
m_xactReserved = 0                   m_xdesId = (0:146625)                m_ghostRecCnt = 0
m_tornBits = 0
 

— Verifica a unidade de alocação correspondente
SELECT container_id FROM sys.allocation_units WHERE allocation_unit_id = 72057594040418304

Container_ID
72057594038779904

— Verifica o HOBT_ID correspondente
SELECT object_id FROM sys.partitions WHERE hobt_id = 72057594038779904

Object_ID
2105058535

— Verifica o objeto bloqueado
SELECT Name FROM sys.objects WHERE object_id = 2105058535

Name
T1

Vejamos agora que tipo de transações são impostas pelo comando TRUNCATE. Irei utilizar duas sessões da mesma forma que foi feito com o DELETE.

— Sessão 1
— Muda o contexto de banco de dados

USE BDTD

— Abre uma transação
BEGIN TRAN

    — Exclui todos os registros de T1 via DELETE
    TRUNCATE TABLE T2

    — Aguarda 10 segundos
    WAITFOR DELAY ’00:00:10′

— Desfaz a transação
ROLLBACK

— Sessão 2
— Verifica os recursos bloqueados

SELECT
    resource_type As Recurso, resource_description As Descricao,
    resource_associated_entity_id As Entidade, request_mode As Modo
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID(‘BDTD’) AND resource_type != ‘DATABASE’

O resultado dos recursos bloqueados pode ser descrito na tabela abaixo (é possível que eles possam variar):

Recurso Descrição Entidade Modo
OBJECT   5 IX
OBJECT   7 IX
RID 1:126:0 6488064 X
PAGE 1:126 72057594039107584 X
PAGE 1:121 72057594039107584 X
PAGE 1:148 72057594039107584 X
PAGE 1:175 72057594039107584 X
PAGE 1:173 72057594039107584 X
METADATA principal_id = 1 0 Sch-S
RID 1:126:1 6488064 X
RID 1:126:2 6488064 X
RID 1:126:3 6488064 X
KEY (5057bcc7bd17) 458752 X
OBJECT   37575172 Sch-M
KEY (013f116f5b88) 327680 IX

Podemos visualizar que os dois primeiros bloqueios são de objeto e agem respectivamente sobre as tabelas de sistema sys.sysrowsets e a sys.sysallocunits. Possivelmente essas tabelas guardam a relação de páginas e extensões utilizadas pela tabela T2 (e por todas as outras) e uma vez que as páginas e extensões tenham de ser fisicamente excluídas, as tabelas de sistema devam ser atualizadas. Isso também ocorre com o DELETE, mas como o processo de limpeza é assíncrono e realizado pelo Ghost Cleanup Task não pudemos visualizar as alterações diretamente em tabelas de sistema como ocorre com o TRUNCATE. Podemos ver também que as páginas e as linhas são bloqueadas exclusivamente em oposição ao DELETE que bloqueou exclusivamente apenas as linhas. Alguns resultados são curiosos quando o TRUNCATE é utilizado. Segundo o Books OnLine, a coluna resource_associated_entity_id normalmente representa o ID de um objeto, um Hobt_Id ou uma unidade de alocação. No caso do ID 3755172 é fácil identificar que se trata da tabela T2, mas o ID 6488064 não tem correspondente em nenhuma das visões de catálogo (sys.objects, sys.partitions ou sys.allocation_units). Acredito ser uma allocation unit ou um hobt_id de uma tabela de sistema.

Fica evidente que ao contrário de uma operação de DELETE comum, o uso do TRUNCATE faz mais do que somente excluir os dados completamente. É necessário também bloquear o esquema do objeto com um bloqueio bem rígido como o Sch-M (Schema Modification). Esse é o mesmo tipo de lock lançado contra um objeto em operações DDL e é incompatível com com qualquer tipo de bloqueios de acordo com a matriz de compatibilidade de bloqueios superando até mesmo um bloqueio exclusivo em uma operação de DELETE. Felizmente operações de TRUNCATE são muito rápidas e dificilmente provocam contenção. Não seria absurdo dizer que os comandos DROP TABLE e TRUNCATE tem o mesmo comportamento a exceção que o TRUNCATE deixa a tabela enquanto o DROP TABLE se encarrega de eliminá-la completamente. Ambos porém atualizam os metadados enquanto o DELETE incorre apenas em alterações nos dados. Os exemplos citados aplicam-se também a tabelas clusterizadas. Para confirmar bastaria rodar os mesmo exemplo com um índice cluster em cada tabela, mas ao invés de demonstrar deixo para os mais fuçadores.

Uma última observação deve-se a quantidade de registros. No caso do DELETE, quanto mais registros houver, mais bloqueios irão aparecer. Inicialmente ele irá impor um bloqueio linha a linha e isso pode aumentar significativamente a quantidade de bloqueios. Esses podem ser diminuídos posteriormente já que o SQL Server pode optar por escalá-los para tabela. Isso não ocorre com o TRUNCATE.

De volta ao básico

Agora que o comportamento do TRUNCATE e do DELETE estão bem explicados vejamos as considerações básicas sobre exclusão de registros com DELETE e com TRUNCATE típicas de documentações e materiais de curso. Os tópicos foram retirados do Books OnLine, mas as explicações são minhas:

Menor log de transações: Uma vez que o comando de TRUNCATE desaloque diretamente as páginas e extensões de forma direta como uma operação minimamente logada a quantidade de log naturalmente é menor. O DELETE fatalmente irá gerar mais entradas no log de transação em virtude da necessidade de logar cada linha no log de transações

Menos bloqueios são impostos: Os bloqueios impostos pelo DELETE iniciam no nível de linha e à medida que o SQL Server vai bloqueando mais linhas ele pode optar por elevar o nível de bloqueio para tabela (o SQL Server jamais irá escalar um bloqueio de linha para o nível de página). Até que isso ocorra a quantidade de bloqueios pode gastar uma quantidade significativa de memória impondo contenção. Os bloqueios impostos por uma operação de TRUNCATE são mais rígidos que o DELETE, mas são em menor quantidade. Como operações de TRUNCATE normalmente são executadas muito rapidamente, esses bloqueios costumam passar despercebidos.

Uso da cláusula WHERE: Como a instrução TRUNCATE TABLE varrre todos os blocos e faz a exclusão logo em seguida não é possível selecionar quais registros devem ser excluídos. Os registros que obedecem uma cláusula WHERE podem estar em qualquer bloco e não há garantia nenhuma de que todos estejam em um bloco em particular não sendo portanto possível utilizar uma cláusula WHERE em conjunto com o TRUNCATE.

Uso em tabelas referenciadas por Foreign Keys: Uma vez que o TRUNCATE simplesmente exclui todas as páginas e extensões de uma tabela, não seria possível validar se algum desses registros é referenciado por alguma tabela filha. O DELETE loga linha a linha e caso não haja violação de integridade referencial é possível utilizá-lo mesmo em tabelas referenciadas. A opção CASCADE é capaz de propagar as atualizações para o DELETE, mas não para o TRUNCATE uma vez que esse comando não mantém a relação de linhas afetadas e não é portanto capaz de propagar seus efeitos.

Uso em conjunto com triggers: Como o comando TRUNCATE efetua uma gravação mínima no log de transações ele não dispara triggers de DELETE. Ainda que fosse possível, seria bem desastroso um comando de TRUNCATE gerar uma DELETE com todos os dados da tabela. Como o TRUNCATE normalmente é usado para tabelas muito volumosas os recursos de memória, disco e TempDB poderiam esgotar rapidamente.

Participação em tabelas replicadas nos tipos MERGE e transacional: As operações de replicação baseiam-se fortemente em triggers. Uma vez que o comando TRUNCATE não dispara as triggers não há como funcionar em conjunto com outras replicações que não a SNAPSHOT. No caso da replicação transacional há ainda mais impecilhos, pois, além de logar minimamente a exclusão de registros, seria muito complicado se a replicação transacional utilizasse filtros horizontais. Não haveria como ler que registros deveriam ou não ser replicados e nem como bloqueá-los no log de transações para replicações agendadas.

Participação em Views Indexadas: As views indexadas materializam dados de uma tabela ou de várias tabelas combinadas. Se o TRUNCATE fosse rodado contra uma tabela participante, a view indexada simplesmente ficaria inválida, pois, os comandos de exclusão individuais não seriam logados e uma falha não permitiria que o banco se recuperasse (não haveria tracking das alterações) para refazer o índice da view.

Obrigatoriedade de privilégio db_ddladmin: Como a instrução TRUNCATE exclui todas as linhas e faz intervenções nos metadados é de se esperar que uma permissão de DDL seja necessária (ainda que no final das contas o efeito DML seja o mesmo do DELETE).

Bem, depois de uma explanação tão longa acho que os slides do curso agora estão completos.

[ ]s,

Gustavo

2 Respostas para “Truncate versus Delete – Uma explicação mais detalhada

  1. Muito bom Gustavo, excelente explicação sobre o DELETE e TRUNCATE. Sobre pode incluí-lo em uma transação foi novidade pra mim, além dos efeitos em tabelas com FK (propagação).

    • Olá Leonardo,

      Muita gente pensa que o TRUNCATE não é transacionável e isso me motivou a escrever o post.
      Só é pena que as FKs o atrapalhem (mesmo sem violações). Espero que no futuro alguém retire essa restrição.

      [ ]s,

      Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s