Arquivo da categoria: SQL SERVER

SQL Saturday 127 – Rio de Janeiro aí vou eu

Oi Pessoal,

Há alguns dias recebi a grata notícia de que serei um dos palestrantes do SQL Saturday 127 que irá ocorrer no dia 14/04. Não preciso nem dizer que fiquei feliz com a oportunidade de estar novamente tão próximo da comunidade levando um pouco da vivência, dos apertos, dos erros e da experiência com o produto. O formato presencial me agrada muito, pois, me lembra a sala de aula nos momentos de MCTs, mas de uma forma mais aberta sem as restrições do MOC ou imposição de temas. O SQL Saturday 100 foi excelente e se o 127 for na mesma linha será um grande evento.

Na minha sessão falarei sobre o processo de ETL e algumas técnicas para escalá-lo de forma incremental e alternativas no SQL Server para processos ETL Near Real Time. O foco será mais forte no SQL Server, mas algumas técnicas são independentes de SGBD. Atualmente estou participando nas definições de infraestrutura de um grande projeto de Business Intelligence e embora BI não seja o meu maior foco de atuação, essa experiência está sendo bem engrandecedora. As definições de uma infraestrutura de BI dependem naturalmente da arquitetura da solução e o contato com arquitetos, consultores, desenvolvedores de ETL, etc é natural. É um excelente aprendizado no qual eu pretendo compartilhar um pedacinho nesse evento (se o tempo permitisse a gente falava por horas).

Será um sábado inteiro de SQL Server e se você é entusiasta do produto, trabalha com ele ou depende dele de alguma forma, essa é sem dúvida uma ótima oportunidade, até porque o evento é gratuito e as vagas são limitadas. Se você pretende participar, não deixe de obter mais informações no site oficial do evento em: http://www.sqlsaturday.com/127/eventhome.aspx

A agenda do evento está com muito boas palestras e naturalmente algum conflito irá acontecer durante as escolhas (eu mesmo tive muita dificuldade). Eis a minha escolha pessoal:

  • 09:15 – Marcelo Sincic – Dimensionando Hardware e Storage para SQL Server
  • 11:15 – Diego Nogare – Pontapé inicial de Business Intelligence na Nuvem
  • 14:15 – Gustavo Maia Aguiar – Algoritmos e alternativas para cargas incrementais
  • 16:15 – Felipe Ferreira – Melhores práticas para o seu Data Warehouse

Claro que também é uma excelente oportunidade para encontrar alguns dos vários conhecidos da comunidade de SQL Server bem como conhecer aqueles que ainda não tive a oportunidade. Então anote aí na agenda. Dia 14/04 é dia de SQL Saturday.

Espero vê-los por lá.

[ ]s,

Gustavo

Anúncios

Iniciando 2012

Olá Pessoal,

Dizem que o ano só começa efetivamente depois do carnaval. Eu gostaria de ser um dos felizardos que pode dizer isso (alguém aí pode ?), mas não é o caso. De fato apenas hoje após alguns dias do término do carnaval, estou conseguindo colocar o meu primeiro post. Desde que comecei a me preparar para palestrar para o TechED 2011 que não consegui parar. Tanto esforço rendeu um bom feedback, pois, as avaliações do TechED foram muito satisfatórias para minha primeira apresentação em um evento desse porte. Na cola nem deu tempo de descansar, pois, iniciou-se a preparação para o SQL Saturday na sua edição de número 100 aqui no Brasil. Evento fantástico onde tive a oportunidade de conhecer e fazer novas amizades além de reencontrar as já existentes. Eu realmente adoro iniciativas com a comunidades, pois, não há nada mais gratificante do que ver a vontade em compartilhar alcançar lugares que nem mesmo imaginávamos. Na lista desses eventos entraram ainda o MVP Open Days e o Community Zone que também são excelentes oportunidades, mas consomem um pouquinho.

Pensei que depois do SQL Saturday eu iria diminuir o ritmo, mas no meu ambiente atual, acabei tendo uma baixa de um dos melhores DBAs da minha equipe e aí e passei a dar mais de mim para compensar essa baixa que espero resolver o quanto antes. Para piorar, coloque um pequeno problema de saúde e aí de fato, posso dizer que já tem algum tempo que eu não parava mesmo e ainda sem estar por aqui.

Pois bem, felizmente, depois de quase 30 dias de férias (como é bom poder tirar tanto tempo de férias e viajar ainda por cima) e praticamente ausente de emails, MSN, blogs, comunidades e iniciativas SQL Server estou de volta. Começando o ano de 2012 ainda um pouco devagar (estamos apenas alguns dias depois da quarta-feira de cinzas), estou com as baterias recarregadas e pronto para contribuir. Os anos de 2008 e 2009 dediquei praticamente 100% ao fórum e ao blog e alguns Webcasts. A partir de 2009, já comecei a utilizar outros tipos de canais como artigos técnicos para revistas, mas apenas em 2011 realmente diversifiquei. Além das palestras presenciais (que gostei muito de fazer), consegui gravar vídeos para o Centro de Treinamento de SQL Server, fazer um WebCast para o Virtual Pass e ainda colocar os vídeos no meu canal no Youtube.

Esse ano eu pretendo investir mais nesse tipo de iniciativa. Não que eu não queira ou não vá continuar blogando ou respondendo threads nos fóruns do MSDN e do Technet, mas é que vejo um alcance muito interessante em outros canais e pretendo explorá-los um pouco mais e não me restringir somente aos mais tradicionais.

Para iniciar acabei de postar o primeiro vídeo de uma coleção que pretendo postar abordando diversos assuntos sobre SQL Server e banco de dados. Os primeiros vídeos serão focados no assunto indexação e pretendo fazer uma introdução bem detalhada no assunto sem naturalmente a prentensão de esgotá-lo.

É nesse ponto e com esse vídeo que começo 2012. Vejamos o que consigo fazer  e até onde ir para ajudar essas pessoas. Feedbacks e sugestões de temas são bem vindos.

[ ]s,

Gustavo

MVP Open Day, TechED & Community Zone

Oi Pessoal,

Após duas semanas da finalização desses três eventos, finalmente consegui um tempinho para contar um pouquinho o resultado desses três excelentes eventos. Dia 28/09 tivemos o MVP Open Day com uma grade de palestras muito interessantes sobre vários produtos Microsoft (Visual Studio, SQL Server, ALM, Windows Phone, etc). Em virtude do NDA não posso abrir muitos detalhes, mas eu diria apenas que o ano de 2012 é um ano bastante promissor para a plataforma de servidores (Windows Server, Hyper-V, etc). Fora as palestras foi ótimo encontrar alguns amigos no evento e compartilhar o happy hour com alguns dos profissionais que tanto contribuem para a comunidade. Não sei o que o futuro reserva, mas espero poder estar no MVP Open Day de 2012, 2013 e por aí vai. Só não aproveitei mais o evento, porque em virtude do TechED tive que ir embora cedo já que precisava dar uma boa revisada nas demos da palestra.

MVPs Brazucas

Dia 29/09 começava o primeiro dia do TechED. A grade de palestras estava interessante, mas com alguns conflitos que não pude evitar. Fiquei chateado de não poder prestigiar vários amigos com palestras muito interessante (BI, Azure, etc). Bem, infelizmente a vida é feita de escolhas e tive que optar pelas sessões que mais agregavam no momento. O grande destaque desse dia para mim foi a palestra do Nilton Pinheiro sobre HADR no Denali. O HADR é sem dúvida uma implementação fantástica e finalmente a Microsoft colocou algo para competir com o Oracle Data Guard no cenário de HA. O DB2 ficou para trás, pois, o HADR dele ainda não possui a funcionalidade de múltiplos sites Standby. Estou na torcida para que o próximo SQL Server depois do Denali finalmente traga algo para competir com o Oracle RAC e o Pure Scale do DB2. O tema foi abordado pelo Nilton com maestria, de forma clara e muito bem apresentada além de responder minhas dúvidas sobre o sistema de quórum do HADR. Tive a oportunidade de conversar com ele na hora do almoço antes da palestra para saber o que viria, e realmente atendeu bem minhas expectativas. Também pude aproveitar para assistir a palestra de troubleshooting do Fabio Gentile e da Renata Festa. Já conhecia o Gentile de alguns chamados no Premier e o assunto muito me interessava. Destaque para as novas DMVs do 2008R2 (SP1) que finalmente possibilitam obter dados de discos Mount Point (não aguentava mais a xp_fixeddrives). Muito boa, mas não espera menos do time de PFEs. Foi bom para atualizar algumas coisas que eu ainda não tinha fuçado.

Dia 30/09 era o grande dia. Eu já tinha participado do TechED em 2010 na mesa do Ask The Experts (que lamento inclusive ter sido retirada do evento em 2011), mas nunca tinha participado como palestrante. Na noite anterior havia finalizado todas as minhas demos e em termos de conteúdo estava tudo certo. Cheguei na sala 30 minutos antes, conversei com os operadores, fiz os testes de som, etc. Minha sessão iniciava às 10:10 e às dez horas eu já estava a postos. Naquela hora contando por alto tinha umas 50 pessoas na sala só. Por um lado fiquei meio chateado, pois, a capacidade da sala era no mínimo cinco vezes maior, mas por outro lado, até que o frio na barriga diminui com pouca gente. Quando foi 10:05 (cinco minutos antes), de repente a sala começa a encher e rapidamente os lugares são ocupados e aí sim fiquei mais feliz por ter mais interessados, mas sem dúvida comecei a ficar inquieto, pois, nunca havia feito uma sessão presencial para tantas pessoas (nas minhas contas deu umas 260, mas acho que foi mais). Pois bem, iniciada as formalidades, comecei a falar do ISO, evolução do T-SQL, novidades do 2008 R2 e chegamos na parte do Denali. Controlei o tempo e consegui apresentar tudo o que eu gostaria. Faltou só mais 5 minutinhos para falar das referências, etc, mas de qualquer forma gostei muito da experiência.

DBP302 - T-SQL: O que você deve saber do Microsoft SQL Server 2008 R2 e as novidades do SQL Server Code-Named Denali

Aproveito para agradecer a todos os presentes na sessão que participaram e aqueles que contribuiram com perguntas, sugestões, etc. A todos os que compareceram o meu muito obrigado. Aqueles que não puderam estar presentes, o PPT com os scripts pode ser baixado no meu Skydrive (20111017_TechED_DBP302_TSQL_2008R2_NewFeaturesDenali.rar)

DBP302 – T-SQL: O que você deve saber do Microsoft SQL Server 2008 R2 e as novidades do SQL Server Code-Named “Denali”
https://skydrive.live.com/?cid=F4F5C630410B9865&id=F4F5C630410B9865%21148

Após a minha sessão, mal saí da sala e já tive de voltar para assistir a sessão do Fabiano e do Luti sobre Cenários de otimização com o SQL Server “Denali” e 2008. Sessão bem interessante sobre estatísticas, cache bloat, parameter sniffing, etc. Foi um belo apanhado geral sobre alguns detalhes do otimizador de consultas além de uma excelente sessão. Na seqüência (e na mesma sala inclusive), veio a palestra do Catae e Pimenta sobre Raio-X do SQL Server: Arquitetura interna do gerenciador de banco de dados. No ano passado eu assisti uma palestra do Catae e do Gentile sobre troubleshooting e gostei bastante, pois, sempre se aprende algo novo além de conhecer alguns detalhes nas entranhas do SQL Server. O que mais gosto mesmo é das perguntas pós apresentação. Essa sessão falou bastante sobre as decisões que otimizador tem de tomar para montar um plano de execução (em especial na avaliação de índices). Se tivessem trocado a ordem da sessão do Fabiano e do Luti com a do Catae e do Pimenta eu acho que teria ficado perfeito, pois, os temas eram complementares, mas a sessão do Fabiano e do Luti entrou mais a fundo no otimizador. Para finalizar bem o evento, assisti a palestra da Viviane Ribeiro e do Ruy Pimentel sobre Soluções de Alta disponibilidade e Disaster Recovery para o SQL Server. Essa já é uma sessão “permanente” no TechED, pois, já a vi na agenda no anos anteriores, mas esse ano com outros apresentadores. Embora a sessão tenha abordado as implementações de alta disponibilidade de uma forma geral, houve um grande foco da apresentação no Failover Clustering. Achei interessante, pois, das técnicas de alta disponibilidade, a que mais trabalho no dia a dia é o Failover Clustering. Foi muito proveitoso ver como é gerenciado a parte de clustering de uma grande empresa como a DELL. Tive a oportunidade de ficar no mesmo quarto que o Ruy durante o evento e trocamos várias figurinhas sobre o assunto (DTC, distribuição de instâncias, divisão de memória, etc). O que mais valeu a pena pra mim foram os detalhes de como “emular” múltiplas instâncias Default no mesmo cluster. Esse macete já tinha valido a sessão e espero implementar no ambiente de um dos clientes muito em breve.

Palestrantes do TechED (boa parte de SQL Server)

No dia 01/10, terminei minha saga em São Paulo e eventos Microsoft com o Community Zone. Nem preciso dizer que eu adoro participar e contribuir com a comunidade Microsoft e esse evento foi bem direcionado para contribuidores. Foi ótimo para conhecer algumas pessoas do fórum que a gente conversa, lê blog, vê webcast, mas nunca conhece pessoalmente (de uma forma geral, os três dias foram assim). Aproveitei a ocasião e pude para discutir algumas iniciativas e eventos futuros para a comunidade de SQL Server. Alinhamentos gerais e boas coisas por vir…

DSC02622

De volta a Brasília e muita coisa pra estudar, organizar e entregar, mas o próximo encontro já está marcado. Dia 26/11 teremos o SQL Saturday que promete bastante. Mais um evento presencial com vários especialistas em SQL Server (e o melhor que é gratuito). Vou marcar presença. Se você quer se inscrever não perca tempo. Nos vemos lá.

Welcome to SQLSaturday #100  – Bem Vindo ao SQLSaturday #100
http://www.sqlsaturday.com/100/eventhome.aspx

[ ]s,

Gustavo

MCTS & MCITP – Business Intelligence Developer

Olá Pessoal,

Acabo de sair da prova PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008 finalizando assim uma maratona de 4 meses de prova de certificação. Ao longo desse ano, concluí a 70-445 (MCTS BI 2005), 70-446 (MCITP BI 2005), 70-448 (MCTS BI 2008) e agora a 70-452 (MCITP BI 2008). Juntando com as certificações anteriores, finalmente completei a coleção de certificações de SQL Server MCTS e MCITP. Já era uma meta antiga, mas como as provas de SQL Server 2005 estão sendo descontinuadas, não ia ter mais jeito de adiar e aproveitei para fechar logo as certificações de SQL Server 2008.

Após ter concluído todas essas provas, é muito mais perceptível pra mim o quanto a suíte SQL Server evoluiu desde a versão 7 & 2000 (quando comecei a conhecer o produto). Antigamente era bem mais fácil realmente conhecer o SQL Server. Havia apenas o Database Engine com alguns serviços acessórios como o DTS e Analysis Services (sucessor do Olap Services). O Reporting Services sequer vinha junto com o CD de instalação, pois, só foi lançado em 2003 acompanhado de dois service packs. Reconheço que era difícil, mas de certa forma, não era tão complicado conhecer a fundo o SQL Server e por isso eu sempre tentei conhecer o máximo possível de todas as suas features.

Já no SQL Server 2005, eu percebi que manter esse ritmo e adentrar feature a feature seria uma tarefa hercúlia, mas nem por isso impossível. O Database Engine sofreu várias melhorias, o DTS evoluiu para o Integration Services e o Analysis Services e o Reporting Services foram praticamente reinventados. Era um desafio difícil, mas sempre tentei acompanhar. Com o SQL Server 2008, o que era difícil ficou quase impossível, pois, as evoluções dos produtos foram ainda maiores. Hoje com o SQL Server 2008 R2 e o Denali eu concluo que realmente perseguir feature a feature do SQL Server para tentar dominá-la beira o impossível.

Consegui tirar todas as certificações até aqui, mas o que isso realmente significa ? Absolutamente nada além de uma satisfação pessoal, pois, sinceramente falando não creio que obter todas as certificações significa realmente conhecer todas as features do SQL Server. Acho que significa apenas conhecê-las em um nível superficial e dominar algumas poucas. Não creio que conseguirei manter o ritmo quando sair o Denali, mas estudar para todas essas provas amplia os horizontes. Espero agora poder retornar às comunidades, aos estudos no Database Engine e aos projetos pessoais.

[ ]s,

Gustavo

Novos rumos para o Blog (http://gustavomaiaaguiar.spaces.live.com) – Mudança de Endereço

Olá Pessoal,

Esse provavelmente será meu último post no blog (Calma, Calma). Não vou deixar de postar e nem de compartilhar conhecimentos, experiência e artigos como tradicionalmente eu faço. Apenas quero dizer que terei de trocar de endereço por contas das mudanças que o Windows Live vem fazendo. Após estar presente no Spaces há quase dois anos e meio, terei que trocar o endereço.

Por um lado é bem gratificante, pois, imagino que a WordPress tenha mais recursos que o Spaces em relação a blogs, mas por outro lado arrumar todos os links, referências, etc que fizeram com que esse blog fosse encontrado é que é complicado, mas enfim, evoluir é preciso. Eu gostava muito da praticidade da plataforma do Spaces, mas a ausência de estatísticas era uma coisa que me fazia muita falta. Acredito que a nova plataforma seja muito boa, pois, diversos amigos já fizeram a troca.

Espero continuar contando com todos os que visitam o blog, fazem comentários, indicações, etc. A maior fonte de inspiração é justamente saber que existem pessoas que o apreciam esse trabalho. A partir de 2011, lembrem-se de atualizar e mudar o link trocando spaces.live.com por wordpress.com. Ao invés de ser encontrado em http://gustavomaiaaguiar.spaces.live.com serei encontrado em https://gustavomaiaaguiar.wordpress.com

Aproveito aqui para felicitar todos os que lêem essa postagem. Desejo a todos um feliz 2011. Que tenhamos um ano melhor que 2010 com muita alegria, saúde, felicidade, prosperidade e vários artigos de SQL Server.

[ ]s,

Gustavo

TechED 2010

Bom Dia Pessoal,

Estou na contagem regressiva para ir para mais uma edição do TechED em São Paulo. Desde muito tempo venho tentando comparecer a esse evento. Para quem mora em SP, é um pouco mais fácil, mas para quem não mora o que mais complica é ficar três dias sem trabalhar (e sem receber) e ainda ter algumas despesas com passagem, hospedagem e locomoção. Conciliar aulas, projetos, liberação do trabalho e família também são outro obstáculos. Felizmente esse ano, consegui me organizar e finalmente poderei ir ao evento após tantas tentativas.

O evento esse ano é bem promissor para o mundo SQL Server especialmente por conta do lançamento do SQL Server 2008 na sua versão R2. Embora exista um EBook gratuito (Introducing Microsoft SQL Server 2008 R2) e até um curso oficial sobre as New Features (10337A – Updating Your Microsoft SQL Server 2008 BI Skills to SQL Server 2008 R2) sobre o assunto, nada como ver algumas experiências mais de perto. Além das palestras, também colaborarei com as dúvidas participando da mesa "Ask The Experts" junto com outros MVPs, funcionários da MS e profissionais de SQL Server.

A escolha das palestras não foi muito fácil. Com tantas palestras em apenas três dias, o conflito é algo natural. Se eu pudesse assistiria ao maior número de palestras que pudesse, pois há muitos temas interessantes, mas infelizmente não é bem assim que funciona. Alguns horários eu fiquei muito em dúvida e foi difícil escolher. Minha agenda ficou da seguinte forma:

Programação do dia 13/09/2010
13:45 – 15:00 Título: Solução Microsoft para Business Intelligence
Palestrante(s): Luiz Felipe Ribeiro Pimenta
15:30 – 16:45 Título: Tecnologias para Data warehouse: SQL Server Enterprise, Fast Track e PDW
Palestrante(s): Gustavo Gattass Ayub
17:15 – 18:30 Título: Garantindo a qualidade dos dados com Master Data Services
Palestrante(s): Luciano Moreira
18:45 – 20:00 Título: DBA: mais que uma profissão!
Palestrante(s): Luiz Felipe Ribeiro Pimenta

Programação do dia 14/09/2010
09:00 – 10:15 Título: SQL Azure – Cenários de Uso, Migração e Operação
Palestrante(s): Waldemir Cambiucci
10:45 – 12:00 Título: Mitos e realidades sobre consolidação e virtualização com Microsoft SQL Server
Palestrante(s): Alexandre Oliveira, Rafael Veronezi
13:45 – 15:00 Título: Fim-a-Fim : Análise e Solução de Problemas para SQL Server 2005/2008
Palestrante(s): André Hass, Ioannis Xylaras
15:30 – 16:45 Título: Auditoria de um ambiente SQL Server 2008 R2
Palestrante(s): Herleson Pontes, Nilton Pinheiro
17:15 – 18:30 Título: Arquitetura e preparação de infraestrutura de PowerPivot no Excel e Sharepoint
Palestrante(s): Thiago Henrique Hernandes Zavaschi

Programação do dia 15/09/2010
09:00 – 10:15 Título: PowerPivot Avançado: Modelagem, formulas e DAX
Palestrante(s): Thiago Henrique Hernandes Zavaschi
10:45 – 12:00 Título: Estratégias para otimizar a concorrência dentro do Microsoft SQL Server 2008 R2
Palestrante(s): Luiz Felipe Ribeiro Pimenta
13:45 – 15:00 Título: Implementando um Plano de Recuperação de Desastres com Clusters de Virtualização em Multiplas Localidades
Palestrante(s): Marcelo Hunecke, Ricardo Siqueira
15:30 – 16:45 Título: Conhecendo o Submundo dos Crimes por Meios Eletrônicos
Palestrante(s): Marcos Tupinambá
17:15 – 18:30 Título: SQL Server com foco em Diagnóstico de Desempenho
Palestrante(s): Fabio Gentile, Fabricio Catae

No geral eu gostei das palestras ofertadas. Agora é aguardar até o dia 13. Estou certo que vou encontrar muitos de vocês por lá.

[ ]s,

Gustavo

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