Recovery Models – Parte 6 – Rebuild online e offline

Olá pessoal, tudo bem?

Esta é uma última publicação desta série abordando testes e diferenças práticas entre os recovery models full e bulk logged. Nos posts anteriores, abordamos diversas perspectivas diferentes em relação aos dois recovery models, como por exemplo consumo do transaction log, tamanho dos arquivos de backup, performance das operações REBUILD e REORGANIZE, etc.

Nesta última etapa, voltaremos ao comando REBUILD para avaliar e comparar a performance da execução quando executamos a instrução usando os parâmetros ONLINE e OFFLINE.

Teste 7 – ALTER INDEX REBUILD (ONLINE) – Performance

Assim como nos testes apresentados anteriormente, o primeiro passo será executar o comando shrinkfile nos arquivos de log para igualar o consumo de espaço entre eles.

Desta vez, o objetivo de reduzir o arquivo de log não é facilitar a comparação de quanto cada arquivo expandiu depois da execução do comando, mas simplesmente garantir a mesma condição entre os dois bancos de dados para que a própria expansão do arquivo em disco não seja um fator determinante no tempo de execução do comando REBUILD.

No procedimento de shrinkfile, pode ser necessário a execução dos comandos de backup de log e checkpoint mais de uma vez afim de liberar os VLFs do final do arquivo de log (conceitos sobre transaction log internals vão além do escopo dessa publicação, mas excelentes materiais foram publicados pela comunidade brasileira). Adicionalmente, o comando DBCC LOGINFO exibe o status das VLFs e é extremamente útil para a realização do shrink do arquivo de log.

USE DB_Full
GO
CHECKPOINT
GO
USE DB_Bulk
GO
CHECKPOINT
GO

USE master
GO
BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_1.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_1.trn'
GO

USE DB_Full
GO
DBCC SHRINKFILE(2,8)
GO
USE DB_Bulk
GO
DBCC SHRINKFILE(2,8)
GO

USE DB_Full
GO
DBCC LOGINFO
GO
USE DB_Bulk
GO
DBCC LOGINFO
GO

Depois de ajustarmos os arquivos de log dos dois bancos de dados para 8 MB cada um, podemos prosseguir com a execução do comando REBUILD, que neste primeiro momento usará a opção ONLINE. Reparem que logo abaixo da execução do comando, adicionei algumas linhas de comentários contendo o consumo de log do banco de dados antes e depois do comando e também o tempo de execução da operação propriamente dita.

USE DB_Full
GO
ALTER INDEX IX_1 ON ORDER_LINE REBUILD WITH (ONLINE = ON)
--antes, log em: 8.192 KB
--depois, log em: 3.547.136 KB
--tempo: 1 minuto e 43 segundos
GO

USE DB_Bulk
GO
ALTER INDEX IX_1 ON ORDER_LINE REBUILD WITH (ONLINE = ON)
--antes, log em: 8.192 KB
--depois, log em: 204.800 KB
--tempo: 1 minuto e 7 segundos
GO

Conclusão

No teste anterior, vimos que executar o comando REBUILD com a opção ONLINE é consideravelmente mais rápido (aprox. 35%) em um banco de dados com recovery model bulk logged (1:07) quando comparado à uma base de dados com recovery model full (1:43).

Adicionalmente, assim como já apresentado nas publicações anteriores, o consumo de log também foi extremamente inferior no recovery model bulk logged em relação ao full.

Teste 8 – ALTER INDEX REBUILD (OFFLINE) – Performance

Neste último teste, repetiremos basicamente o procedimento feito no cenário acima, porém executaremos o comando REBUILD usando a opção OFFLINE.

Novamente, vamos igualar os arquivos de log dos dois bancos de dados em 8 MB para garantir exatamante a mesma condição na execução dos comandos propostos.

USE DB_Full
GO
CHECKPOINT
GO
USE DB_Bulk
GO
CHECKPOINT
GO

USE master
GO
BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_2.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_2.trn'
GO

USE DB_Full
GO
DBCC SHRINKFILE(2,8)
GO
USE DB_Bulk
GO
DBCC SHRINKFILE(2,8)
GO

USE DB_Full
GO
DBCC LOGINFO
GO
USE DB_Bulk
GO
DBCC LOGINFO
GO

Agora que os arquivos de log dos dois bancos de dados possuem ambos 8 MB, podemos prosseguir com a execução do comando REBUILD.

USE DB_Full
GO
ALTER INDEX IX_1 ON ORDER_LINE REBUILD WITH (ONLINE = OFF)
--antes, log em: 8192 KB
--depois, log em: 794 MB
--tempo: 36 segundos
GO

USE DB_Bulk
GO
ALTER INDEX IX_1 ON ORDER_LINE REBUILD WITH (ONLINE = OFF)
--antes, log em: 8.192 KB
--depois, log em: 204 MB
--tempo: 24 segundos
GO

Conclusão

Reparem que mesmo utilizando a opção OFFLINE, os tempos de execução obtidos foram diferentes, sendo a execução sobre o banco de dados com recovery model bulk logged ainda a mais rápida (24 segundos) em relação ao recovery model full (36 segundos). É uma diferença de aproximadamente 33% no tempo de execução.

Adicionalmente, assim como no teste anterior, mantive a informação referente ao consumo de log mesmo não sendo o objetivo principal desta publicação. No recovery model bulk logged, após a execução do comando o transaction log ocupava 204 MB enquanto que no recovery model full o arquivo equivalente passou a consumir 794 MB.

Considerações Finais

O objetivo principal desta série de testes e publicações foi apresentar uma comparação prática e direta entre os dois recovery models abordados (full e recovery model). Existem diversas publicações e fontes de conhecimento na comunidade SQL Server (internacional e nacional) que abordaram os detalhes técnicos e fizeram análises aprofundadas a respeito do mesmo assunto, mas nem todas compartilharam testes e comparações práticas, simples e diretas como as que foram publicadas nas últimas semanas através destes últimos posts.

Com essas comparações, espera-se ajudar profissionais e interessados no assunto a entender através de exemplos básicos a diferença dos recovery models full e bulk logged quando aplicados em situações e cenários corriqueiros do cotidiado de um ambiente de banco de dados.

Felipe de Assis

https://br.linkedin.com/in/fdassis

Anúncios

Deixe um comentário

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