Recovery Models – Parte 1 – Full e Bulk Logged: Select Into

Olá pessoal, tudo bem?

Eu gostaria de interromper brevemente minha série de publicações sobre a funcionalidade In-Memory OLTP para compartilhar alguns testes e comparações em relação ao funcionamento dos modos de recuperação full e bulk logged.

Observação: esta publicação também está disponível no blog da DataEX, juntamente à outros conteúdos sobre SQL Server (performance, boas práticas, administração, etc.), que pode ser acessado pelo linkhttp://dataexblog.azurewebsites.net/.

O principal objetivo dos testes é mostrar como a escolha do modo de recuperação de um banco de dados impacta no tempo de execução das atividades minimamente logadas, no tamanho dos arquivos de log e também tamanho dos backups de log.

Em todos os testes documentados nesta publicação, utilizei o SQL Server 2016 (Build 13.0.1601.5) executando sobre minha estação de trabalho, que funciona com o Windows 10. Vale dizer que os conceitos abaixo não se aplicam apenas ao SQL Server 2016, mas também à suas versões anteriores, embora pequenos detalhes podem apresentar diferenças.

Separei os testes em algumas publicações visando facilitar a leitura e o entendimento. Seguem abaixo os scripts utilizados na parte 1, juntamente com os resultados, observações e conclusões.

Teste 1 – SELECT INTO

Inicialmente, criei dois bancos de dados usando todas configurações padrão do SQL Server, ajustei o recovery model de ambos (um deles para full e o outro para bulk_logged) e depois realizei um backup full para retirá-los do auto truncate mode.

Nota: ao criar o banco de dados sem especificar o tamanho dos arquivos, o banco de dados foi criado com o arquivo de transaction log consumindo apenas 8 MB pois é essa a configuração do banco de dados model da instância utilizada nos testes aqui apresentados.

USE Master
GO
CREATE DATABASE DB_Full
ON (NAME = 'DB_Full',
FILENAME = 'C:\Temp\Files\DB_Full.mdf')
LOG ON (NAME = 'DB_Full_log',
FILENAME = 'C:\Temp\Files\DB_Full_log.ldf')
GO
CREATE DATABASE DB_Bulk
ON (NAME = 'DB_Bulk',
FILENAME = 'C:\Temp\Files\DB_Bulk.mdf')
LOG ON (NAME = 'DB_Bulk_log',
FILENAME = 'C:\Temp\Files\DB_Bulk_log.ldf')
GO
ALTER DATABASE DB_Full SET RECOVERY FULL
ALTER DATABASE DB_Bulk SET RECOVERY BULK_LOGGED
GO
BACKUP DATABASE DB_Full TO DISK = 'C:\Temp\Files\DB_Full.bak'
BACKUP DATABASE DB_Bulk TO DISK = 'C:\Temp\Files\DB_Bulk.bak'
GO

Na sequência, copiei uma tabela (ORDER_LINE) do banco de dados tpcc (banco de dados utilizado para execução de benchmarks) que criei usando uma aplicação chamada Hammer. No meu ambiente, essa tabela tem pouco menos de 2 GB.

Mais detalhes sobre a aplicação Hammer podem ser encontrados no site do Brent Ozar:

https://www.brentozar.com/archive/2012/06/load-test-sqlserver/

A cópia dos dados foi realizada o comando SELECT INTO, que também é considerado minimamente logado, ou seja, já neste primeiro comando foi possível comparar a diferença de comportamento entre os dois diferentes recovery models.

USE DB_Full
GO
SELECT * INTO ORDER_LINE FROM tpcc..ORDER_LINE
GO
USE DB_Bulk
GO
SELECT * INTO ORDER_LINE FROM tpcc..ORDER_LINE
GO

Depois de executados os comandos acima, executei dois backups de log em cada banco de dados e comparei o tamanho consumido em disco por cada um deles, levando em consideração tanto os próprios arquivos de backup quanto os arquivos do log de transações.

Imagem 1

Arquivo de log

Repare na imagem acima que o arquivo de log fo banco de dados cujo recovery model estava configurado como full tem alocado pouco mais de 2 GB, enquanto o arquivo de log do outro banco de dados (recovery model bulk logged) está alocando aproximadamente 200 MB.

Essa diferença acontece porque ao executar operações minimamente logadas em um banco de dados com o recovery model full faz com que o SQL Server copie o conteúdo de cada página de dados que foi alterada diretamente para o transaction log.

O arquivo de log do banco de dados configurado com o recovery model bulk logged ocupa menos espaço porque ao invés de gravar todo o conteúdo das páginas de dados, o SQL Server apenas registra que o comando minimamente logado foi executado e adicionalmente atualiza o status das páginas alteradas naquela operação em uma página de dados chamada ML page. Desta forma, é necessário menos espaço no arquivo.

Backup de log

Repare na imagem abaixo que, em contrapartida aos arquivos do transaction log, os backups gerados após a execução do comando SELECT INTO ocuparam espaços semelhantes em disco.

Imagem 2

O backup de log do banco de dados cujo modo de recuperação foi ajustado como full apenas copiou o conteúdo do transaction log, que já incluía uma cópia das páginas de dados modificadas pelo comando minimamente logado executado anteriormente (SELECT INTO).

Enquanto isso, o backup de log do outro banco de dados (modo de recuperação bulk logged) ocupa espaço semelhante porque, embora apenas o comando minimamente logado esteja registrado no transaction log, o SQL Server precisa copiar em tempo de backup o conteúdo das páginas marcadas como “modificadas” na página ML, já mencionada anteriormente. Desta forma, garante-se que ao restaurar o banco de dados, todo os dados estejam disponíveis.

Conclusão

Resumindo, os resultados obtidos foram:

  • Arquivo de log
    • Modo de recuperação Full: 2039 MB
    • Modo de recuperação Bulk Logged: 204 MB
  • Backup de log
    • Modo de recuperação Full: 2027 MB
    • Modo de recuperação Bulk Logged: 1994 MB

Como vimos nas imagens acima, executar comandos minimamente logados em bancos de dados cujo modo de recuperação esteja como full e bulk logged resulta em comportamentos diferentes em relação ao espaço consumido em disco pelos arquivos de log e também pelos arquivos resultantes dos backups de log.

A principal diferença apresentada nesta publicação está relacionada ao consumo de espaço no transaction log, que é significativamente menor quando o modo de configuração é definido como bulk logged. Em relação ao backup de log, ambos os bancos de dados utilizados no laboratório acima resultaram em arquivos com tamanhos similares.

Nos próximos testes, avançarei nos testes usandos o mesmo banco de dados e a mesma tabela para mostrar a diferença de performance e espaço em disco para algumas operações executadas (create e rebuild index, restore, etc.) sobre bancos de dados com ambos os modos de compatibilidade.

Até a próxima pessoal.

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