Recovery Models – Parte 3 – Full e Bulk Logged: Rebuild e Reorganize

Olá pessoal. Tudo bem?

Na publicação anterior, apresentei mais um teste comparativo entre os recovery models full e bulk logged, onde criamos um índice e então analisamos o comportamento do SQL Server em relação ao consumo de espaço dos transaction logs e arquivos de backup de log.

Nesta terceira publicação, executaremos os comandos ALTER INDEX REBUILD e REORGANIZE afim de analisarmos os resultados usando a mesma perspectiva dos cenários anteriores.

Teste 3 – ALTER INDEX REBUILD (ONLINE)

Novamente, para iniciar este teste, faremos um novo backup de log e um shrink dos arquivos de log dos bancos de dados para facilitar a comparação no consumo de espaço em disco após a execução dos comandos de manunteção dos índices.

USE master
GO
BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_4.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_4.trn'
GO
USE DB_Full
GO
CHECKPOINT
GO
DBCC SHRINKFILE(2,8)
GO
USE DB_Bulk
GO
CHECKPOINT
GO
DBCC SHRINKFILE(2,8)
GO

Com os arquivos de log dos dois bancos de dados consumindo apenas 8 MB cada, podemos executar os comandos abaixo para analisar o comportamento do SQL Server após a conclusão destas instruções.

USE DB_Full
GO
ALTER INDEX IX_1 ON ORDER_LINE REBUILD WITH (ONLINE = ON)
GO
USE DB_Bulk
GO
ALTER INDEX IX_1 ON ORDER_LINE REBUILD WITH (ONLINE = ON)
GO

Nota: Na publicação anterior, vimos que os arquivos de dados de ambos os bancos de dados ocupavam 2760 MB cada um depois de criado o índice IX_1 na tabela ORDER_LINE, mas ao término da execução dos comandos REBUILD apresentados logo acima, os arquivos passaram a alocar 3481 MB.

Essa expansão corresponde à 721 MB, que é basicamente o tamanho do índice (725 MB) depois de reconstruído. Com esses dados, pode-se observar que para reconstruir um índice é necessário ter aproximadamente a mesma quantidade de espaço disponível no arquivo de dados do que o espaço já consumido por aquele mesmo índice.


Arquivos de log

Repare na imagem abaixo que o consumo de espaço dos arquivos de log dos bancos de dados foi significativamente diferente. Enquanto no recovery model bulk logged o consumo de espaço foi de apenas 204 MB, no full o arquivo ocupou 3547 MB.

Imagem 5

Além do espaço consumido, é interessante analisar a quantidade de operações registradas no arquivo de log visando justificar a quantidade extra de espaço em disco consumido pelo banco de dados cuja configuração do recovery model foi definida como full. Para extrair essa informação, usamos a função DB_DBLOG como está exibido na imagem abaixo.

Repare que no banco de dados DB_Full a quantidade de registros é muito superior ao DB_Bulk.

Imagem 6

Backup de log

Depois de executar os comandos para reconstruir o índice IX_1 na tabela ORDER_LINE, podemos providenciar novos backups de log para analisar o consumo de espaço dos arquivos de backup.

BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_5.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_5.trn'
GO
BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_6.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_6.trn'
GO

Repare na imagem abaixo que novamente a quantidade de espaço em disco consumido por ambos os bancos de dados foi consideravelmente diferente. Enquanto o banco de dados com recovery model bulk logged consumiu apenas 846 MB, o outro database consumiu 3505 MB.

Imagem 7

Conclusão

No cenário apresentado acima, os resultados encontrados foram:

  • Arquivo de log
    • Modo de recuperação Full: 3547 MB
    • Modo de recuperação Bulk Logged: 204 MB
  • Backup de log
    • Modo de recuperação Full: 3505 MB
    • Modo de recuperação Bulk Logged: 846 MB

Vimos que o consumo de espaço do transaction log foi significativamente menor para o banco de dados DB_Bulk justamente porque uma quantidade inferior de informações foi gravada nele. Enquanto que no recovery model full o SQL Server registrou cada movimentação de dados realizada no processo de REBUILD do índice, no bulk logged foram registradas apenas as alocações de espaço feitas internamente pelo SQL Server.

Já em relação ao backup de log, no recovery model full todo o conteúdo do transaction log foi copiado para o arquivo de backup, por isso ambos têm aproximadamente o mesmo tamanho (pouco mais de 3500 MB). Enquanto isso, no recovery model bulk logged o SQL Server apenas registrou que o REBUILD foi realizado e copiou cada página de dados envolvida para o arquivo, além de algumas informações internas necessárias para refazer a atividade no caso de um RESTORE. Como o índice têm aproximadamente 749 MB, o backup de log consumiu 846 MB.

Na imagem abaixo constam os consumos de espaço do índice e da tabela (coluna Total_Space_MB) depois das operações realizadas até este momento.

Tabela

Teste 4 – ALTER INDEX REORGANIZE

Adicionalmente, eu gostaria de fazer um novo comparativo usando o comando ALTER INDEX REORGANIZE, que também é utilizado para realizar manutenção dos índices afim de otimizar a performance do ambiente.

Assim como nos testes anteriores, repetirei o procedimento de backup de log + shrink para equiparar os arquivos no tamanho mínimo (8 MB) novamente.

USE master
GO
BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_7.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_7.trn'
GO
BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_8.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_8.trn'
GO

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

Agora que os dois arquivos de log ocupam 8 MB cada, podemos avançar nos testes e executar os comandos abaixo (ALTER INDEX REORGANIZE) nos dois bancos de dados. Depois desta etapa, podemos analisar o consumo de espaço dos arquivos, assim como fizemos nos testes anteriores.

USE DB_Full
GO
ALTER INDEX IX_1 ON ORDER_LINE REORGANIZE
GO
USE DB_Bulk
GO
ALTER INDEX IX_1 ON ORDER_LINE REORGANIZE
GO

Arquivos de log

Repare na imagem abaixo que ambos os arquivos de log estão ocupando a mesma quantidade de espaço. Isso aconteceu porque o comando ALTER INDEX REORGANIZE não é minimamente logado e, portanto, se comporta exatamente igual em todos os recovery models.

imagem 8

Backups de log

Assim como nos testes anteriores, faremos novos backups de log para verificar o consumo de espaço dos arquivos em relação aos arquivos de log propriamente ditos.

BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_9.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_9.trn'
GO
BACKUP LOG DB_Full TO DISK = 'C:\Temp\Files\DB_Full_10.trn'
BACKUP LOG DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk_10.trn'
GO

Considerando o comportamento idêntico dos recovery models em relação ao comando ALTER INDEX REORGANIZE, podemos dizer que o mesmo se aplica ao backup de log. Repare na imagem abaixo que os backups de log executados há pouco ocuparam 1803 MB no disco, somente um pouco menos do que os transaction logs, que neste ponto ocupam 1843 MB.

Imagem 9

Conclusão

Neste ponto, obtivemos os seguintes resultados:

  • Arquivo de log
    • Modo de recuperação Full: 1843 MB
    • Modo de recuperação Bulk Logged: 1843 MB
  • Backup de log
    • Modo de recuperação Full: 1807 MB
    • Modo de recuperação Bulk Logged: 1807 MB

O consumo de espaço dos arquivos de log e também dos arquivos de backup foram idênticos porque o comando ALTER INDEX REORGANIZE não é um comando minimamente logado e, portanto, não provoca nenhum comportamento diferente entre os dois recovery models.

Na próxima publicação, apresentarei dois testes relacionados ao restore de bancos de dados com recovery model bulk logged afim de apresentar o que devemos considerar antes de alterar esta configuração do 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