Recovery Models – Parte 2 – Full e Bulk Logged: Create Index

Olá amigos, tudo bem?

Na publicação anterior, criamos o ambiente de testes e comparamos o comportamento dos recovery models com o comando SELECT INTO. Desta vez, criaremos um índice e analisaremos as diferenças causadas pela configuração do recovery model no comportamento do transaction log e no backup de log.

Teste 2 – CREATE INDEX (OFFLINE)

No teste 1, criamos os bancos de dados, configuramos os recovery models e carregamos os dados de uma tabela. Desta vez, faremos um shrink nos arquivos de transaction log para que ocupem novamente a mesma quantidade de espaço em disco nos dois databases.

Vale lembrar que o comando shrink nunca é considerado uma boa prática de administração, mas neste caso em específico, usamos esse comando apenas para igualar o tamanho dos arquivos e facilitar a comparação de consumo de espaço entre os dois bancos de dados.

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

Nota: Talvez seja necessário executar explicitamente o comando checkpoint ou gerar backups de log para conseguir que o arquivo alcance 8 MB através do comando SHRINKFILE. Essa necessidade pode acontecer devido ao modelo de estrutura do transaction log, formado por setores chamados VLFs, e também pelo seu modelo de funcionamento cíclico. Não entraremos em detalhes a respeito deste assunto pois não é o foco desta publicação.

Neste ponto, os arquivos de log dos dois bancos de dados devem ocupar apenas 8 MB em cada banco de dados para facilitar a comparação entre eles depois de executados os próximos comandos. Com o tamanho dos dois arquivos de log equiparados, podemos submeter os comandos abaixo para criar um índice na tabela ORDER_LINE de cada database.

USE DB_Full
GO
CREATE INDEX IX_1 ON ORDER_LINE
(ol_o_id,ol_number,ol_d_id,ol_w_id)
GO
USE DB_Bulk
GO
CREATE INDEX IX_1 ON ORDER_LINE
(ol_o_id,ol_number,ol_d_id,ol_w_id)
GO

Depois de completada a execução dos comandos acima, podemos analisar os resultados observando o consumo de espaço dos transaction logs e dos arquivos de backup.

Arquivo de log

Repare na imagem abaixo que após a criação do índice, o banco de dados DB_Full (recovery model full) passou a consumir quase 800 MB, enquanto o arquivo do banco de dados bulk logged alocou apenas 200 MB aproximadamente. Essa diferença de alocação de espaço aconteceu porque o comando CREATE INDEX é considerado uma instrução minimamente logada, ou seja, provoca basicamente o mesmo comportamento apresentado no SELECT INTO executado na publicação anterior.

 

Imagem 3

Backup de log

Para completar a comparação proposta neste cenário, faremos mais um backup de log dos dois bancos de dados e analisaremos quanto espaço em disco cada arquivo de backup consumiu.

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

Imagem 4

Repare na imagem acima que, embora os arquivos de log (DB_Full_log e DB_Bulk_log) estejam consumindo espaços diferentes (204 MB e 794 MB, respectivamente) os arquivos de backup alocaram praticamente a mesma quantidade de espaço em disco.

Esse consumo de espaço semelhante entre os dois arquivos acontece porque, independente do recovery model, o SQL Server precisa copiar todas as páginas de dados alteradas pelos comandos minimamente logados para o arquivo de backup afim de garantir a recuperação dos dados em uma eventual necessidade de restore.

Conclusão

Resumindo, os resultados obtidos foram:

  • Arquivo de log
    • Modo de recuperação Full: 794 MB
    • Modo de recuperação Bulk Logged: 204 MB
  • Backup de log
    • Modo de recuperação Full: 741 MB
    • Modo de recuperação Bulk Logged: 728 MB

Na prática, quando um banco de dados é configurado com recovery model full, todas as páginas de dados são copiadas imediatamente para o arquivo de log.  Depois, quando um backup de log é executado, essas páginas também são copiadas para o backup de log.

Com o recovery model bulk logged, as páginas de dados afetadas por comandos minimamente logados não são copiadas para o arquivo de log assim que são alteradas, o que pode salvar espaço no transaction log e possivelmente até tornar a execução do comando mais rápida. Entretanto, as páginas modificadas são copiadas para o arquivo de backup normalmente.

No próximo teste, executaremos dois comandos extremamente comuns e necessários na administração de ambientes de bancos de dados (rebuild e reorganize dos índices) e analisaremos como o recovery model muda o comportamento também neste cenário.

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