Backup de Log e o checkpoint: Dúvida no ar

Publicado 4 de janeiro de 2016 por Leandro Ribeiro
Categorias: Administração, Checkpoint, MTAC, PASS, SQL Server, Transaction Log, Virtual PASS BR

Olá Pessoal, Feliz 2016.

Este é um post importante para mim, pois depois de muito tempo longe do blog por motivos profissionais e pessoais, volto a postar.

Muita coisa aconteceu na minha vida com planos que começaram no segundo semestre de 2014, onde me dediquei quase que integralmente. Desde já peço desculpas pelo silêncio no blog e minha ausência nos eventos do PASS pelo Brasil.

Mas vamos ao que interessa.

Recentemente, o meu grande amigo Edvaldo Castro [Blog | twitter] compartilhou com um grupo de amigos um post do Brent Ozar, onde ele escreve sobre realizar o backup de log a cada minuto.

Sim, é isso mesmo que você leu. Backup de log a cada minuto.

Começou então a análise sobre o custo x benefício desta prática dentro do grupo.

O Brent faz excelentes argumentações e ponderações em seu post, principalmente no que se refere (falei igual a Dilma agora. rs… putz!) a questão estratégica envolvida na política de backup e recuperação dos dados.

O post é muito interessante e vale a leitura.

Segue o link: http://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/

Mas o assunto deste meu post ainda não é este.

O compartilhamento deste post do Brent Ozar pelo o Edvaldo, me fez lembrar do último encontro do PASS Chapter aqui da cidade onde moro.

Neste encontro, em um determinado momento estava sendo discutido a frequência do backup de log, onde o palestrante estava justamente afirmando fazer o backup de log a cada minuto e uma pergunta foi feita por uma ouvinte na pequena plateia:

“Você já parou para pensar que a cada backup de log você está forçando um checkpoint? ”

Silêncio na sala e o palestrante responde:

“Esta é uma boa pergunta! Não pensei nisso.”

Não estava 100% certo se a pergunta feita fazia total sentido, mas no momento me pareceu coerente.

Não dei muita atenção para o assunto pois não estava trabalhando como DBA no momento e com pouca motivação para fazer um teste deste caso em casa, o que normalmente faria quando fico em dúvida sobre algum assunto em palestras que assisto. Tomo nota e faço testes.

Mas agora é outra história. Estou voltando à ativa após alguns meses de férias do SQL Server.😀

Sendo 100% correta a pergunta feita no encontro do PASS Chapter e considerando a frequência sugerida para o backup de log a cada minuto, fiquei muito preocupado com o possível overhead gerado em ambientes de alto volume transacional, em função da escrita das dirty pages em disco pelo checkpoint.

Isso me intrigou demais e estava custando a acreditar que a cada execução de um backup de log um checkpoint é executado.

Conversei com alguns amigos e 100% acreditavam que todo o backup (FULL, DIFF e LOG) força um checkpoint.

Ainda desconfiado, fui atrás de documentação pois acredito que está é a melhor forma de tirar a sua dúvida e encontrei o que procurava no documento oficial do produto.

https://technet.microsoft.com/en-us/library/ms189573(v=sql.105).aspx

Causas_de_Checkpoint

Ou seja, o checkpoint somente é chamado em operações de backup de banco de dados e não de backup de log.

Para obter mais informações sobre o checkpoint, acessem os links abaixo:

https://technet.microsoft.com/en-us/library/ms188748(v=sql.105).aspx

http://www.sqlskills.com/blogs/paul/how-do-checkpoints-work-and-what-gets-logged/

O leitor amigo poderia então ponderar: “Ok Leandro, mas esta parte da documentação pode ter duplo sentido e o backup de log pode estar sendo considerado nesta afirmação.”

Ok. Apesar de considerar que a afirmação contida na documentação oficial é direta ao ponto, vamos testar.

Iniciando os testes…

Para realizar o teste, vamos fazer uso da função fn_dblog para leitura das operações realizadas no arquivo de log de uma base dados.

Você pode encontrar maiores informações sobre esta função na internet, uma vez que não é o objetivo deste post explicar maiores detalhes sobre esta função.

Primeiramente, iremos criar a nossa base de dados.

CREATE DATABASE [BkpLog_vs_Checkpoint]

ON PRIMARY

( NAME = N'BkpLog_vs_Checkpoint', FILENAME = N'C:\SQLServer\Data\BkpLog_vs_Checkpoint.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'BkpLog_vs_Checkpoint_log', FILENAME = N'C:\SQLServer\Log\BkpLog_vs_Checkpoint_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)
GO

A título de curiosidade, vamos verificar quantos VLFs temos e também quantos estão ativos (status = 2) usando o comando “DBCC LOGINFO ()”. Obviamente, deverão existir 4 VLFs neste nosso ambiente considerando o tamanho do arquivo de log na criação da base de dados.

USE [BkpLog_vs_Checkpoint]
GO
DBCC LOGINFO()

Qtd_Vlfs

Está aí. 4 VLFs criados na criação da base de dados (CreateLSN = 0) e apenas um VLF ativo (Status = 2). Tudo normal.

Apesar deste banco estar em recovery model FULL, como vocês devem saber, até que seja realizado um backup FULL em uma base de dados recém-criada, o comportamento desta base de dados será como se estivesse em recovery model SIMPLE, ou seja, operando em auto-truncate mode.

Vamos confirmar o recovery model desta base de dados. Aproveito também e verifico se tem algo impactando a reutilização dos VLFs.

Recovery_model_e_log_reuse_wait

Como deveria ser, está tudo normal.

Fazendo o backup FULL e saindo do modo auto-truncate. Vamos usar o GETDATE para dar a noção da hora de execução, pois iremos usar esta informação mais na frente.bkpfull

Após a execução do backup FULL, vamos verificar quantas operações temos no arquivo de log usando a função fn_dblog. Qtd_operacoes_log_apos_bkpfull

Analisando estas operações existentes no transaction log.

A considerar:

  • Hora aproximada de início do backup FULL: 2016-01-02 22:59:06.090
  • Hora aproximada de término do backup FULL: 2016-01-02 22:59:06.167

fn_dblog_apos_bkpfull_1

Temos três operações de checkpoint (em vermelho), que são: LOP_BEGIN_CKPT, LOP_XACT_CKPT e LOP_END_CKPT. Resumindo:

  • LOP_BEGIN_CKPT é o início da operação de checkpoint e nesta linha, temos a data de início do checkpoint através da coluna “Checkpoint_Begin”, que ocorre exatamente dentro do intervalo de hora da execução do backup FULL, executado anteriormente.
  • LOP_XACT_CKPT é gerado para transações ativas (uncommitted) e informa a quantidade de transações ativas no momento em que o checkpoint inicia.
  • LOP_END_CKPT é o fim da operação de checkpoint e nesta linha, também temos a data que é logicamente a de término da operação através da coluna “Checkpoint End”, que também está dentro do intervalo de hora da execução do nosso backup FULL.

Temos então o intervalo de execução do checkpoint:

  • Hora de início do checkpoint: 2016-01-02 22:59:06.113
  • Hora de término do checkpoint: 2016-01-02 22:59:06.113

Deste modo podemos então concluir, como esperado, que o checkpoint é executado em backup FULL.

Vamos então partir para o teste do Backup de Log que é o nosso alvo.

Primeiro, vamos criar uma tabela de teste bem básica e fazer um insert também bem básico, apenas com intuito de gerar operações no transaction log. Vou manter uma transação aberta apenas por que quero.:)

USE [BkpLog_vs_Checkpoint]
GO
CREATE TABLE [Test_Table] (ID_Column INT IDENTITY(1,1), Column_1 VARCHAR(100))
GO

BEGIN TRANSACTION
INSERT INTO [Test_Table] (Column_1) VALUES ('BkpLog_vs_Checkpoint')

Após a criação da tabela e a execução insert, vamos utilizar a função fn_dblog para procurar dentro do transaction log por operações de checkpoint, em nosso script abaixo, por operações que terminam com “_CKPT”.

Vemos na imagem abaixo, que temos agora 117 operações dentro do arquivo de log e apenas o checkpoint realizado durante o nosso backup FULL.

Até aqui, tudo normal.

fn_dblog_apos_create_Table_e_Insert_com_transacao_aberta

Com a transação ainda aberta, vamos realizar então o alvo de nosso teste que é o backup de log em outra sessão, registrando também a data para podemos comparar com o possível registro a ser criado para a operação de checkpoint.

Backup_Log_apos_insert_transacao_aberta

Finalizado o backup de log, vamos então procurar pela nova operação de checkpoint dentro do arquivo de log, considerando o intervalo de data e hora de execução do backup de log anterior.

fn_dblog_apos_bkpLog

Percebam que o volume de operações no transaction log aumentou, porém, continuamos apenas com os registros das operações de checkpoint do momento da execução do Backup FULL.

Podemos então concluir que o checkpoint não foi executado no backup de Log.

Mas vamos continuar….

Vamos então usar a função fn_dblog para ler o arquivo de backup de log que foi gerado.

fn_dblog_lendoarquivobkpLog

Vemos na imagem acima que o arquivo de backup de log contém exatamente os registros de checkpoint durante a execução do Backup FULL, o que era absolutamente esperado.

Já que estamos com a transação do insert ainda em aberto, vamos fazer o commit para movimentar um pouco mais o arquivo de log e fazer novamente um backup de log para verificar se algo mudaria. (Claro que acredito que não vai mudar. rs)

Backup_Log_apos_commit

Novo backup de log feito após o commit e agora é verificar se temos algum novo registro de checkpoint no arquivo de log.

Desta vez, vou acrescentar na query a função GETDATE para demonstrar que a query foi executada após a execução este último backup de log.:)

fn_dblog_apos_segundo_bkpLog

Continuamos apenas com a operação de checkpoint do Backup FULL.

Cai totalmente por terra a questão levantada de que em todo backup de Log é forçado uma operação de checkpoint. Esta afirmação é falsa. Ufa!

Por fim, vamos apenas ler o nosso segundo e último arquivo de backup de log e verificar se existe alguma informação de checkpoint dentro dele.

fn_dblog_lendoarquivobkpLog_apos_commit

Como era esperado, nenhuma informação de checkpoint existe dentro deste último arquivo, já que esta informação já existe no primeiro arquivo de backup de log.

Bem pessoal como mensagem final deste post, sugiro que nunca confiem plenamente no que é dito nos eventos em geral.

O palestrante normalmente está bem preparado para a sua própria palestra, mas como vocês bem devem prever, ele não tem como saber tudo sobre tudo.

Muitas vezes surgem perguntas nas palestras, como esta por exemplo, que deixam uma dúvida no ar e ainda o que é pior, perguntas e/ou afirmações que nem dúvidas deixam e passam bem longe de serem verdades.

Tomem nota, principalmente daquilo que lhe parecer estranho e testem.

Documentação e posts nós temos de montão por aí.

Espero que este post seja útil em nossos estudos.

Um grande abraço e Feliz ano novo.

Leandro Ribeiro

LANÇAMENTO – SQL Server Além do Conceito – Blog Post Collection.

Publicado 8 de abril de 2015 por Leandro Ribeiro
Categorias: Estudo, PASS, Virtual PASS BR

Tags: , , , ,

Olá Pessoal.

É com enorme prazer que hoje divulgo o E-book SQL Server Além do Conceito – Blog Post Collection. (Clique na imagem para ir ao site de Download)

Capa

SQL Server Além do Conceito – Blog Post Collection

 

Esta obra é uma coletânea de posts de alguns dos principais nomes da comunidade técnica em SQL Server no Brasil.

Ao todo somos 15 autores que sempre com o intuito de compartilhar conhecimento, tivemos a ideia de consolidar os principais posts de cada autor em um livro. 

Com isso, o leitor pode ter acesso em um único local a um excelente conteúdo técnico da plataforma de dados da Microsoft .

Tive o imenso prazer de fazer parte deste projeto junto com uma galera que sempre admirei na comunidade SQL Server Brasil.

Autores:

Diego Nogare – Edvaldo Castro – Demetrio Silva – Marcel Inowe – Marcos Freccia – Ivan Lima – Fabiano Amorim – Sulamita Dantas – Marcelo Fernandes – Cibelle Castro – Leandro Ribeiro – Luciano Moreira – Nilton Pinheiro – Tiago Balabuch – Felipe Ferreira

Esta obra é e sempre será gratuita. Vamos estudar galera!:-)

Bom estudo e Sucesso sempre!

 

Pass Chapter – SQL Server RJ

Publicado 9 de agosto de 2014 por Leandro Ribeiro
Categorias: PASS, Virtual PASS BR

Tags: , ,

Olá pessoal.

No fim do ano passado, mudei de emprego e meu tempo ficou bem curto.
Por isso, tenho estado ausente do blog. Sorry!

Neste post, quero compartilhar com vocês uma grande notícia.

Recentemente, assumi como Chapter Leader do MS-InfraRio.
MS-InfraRio é um grupo conduzido pelo MVP Alexandro Prado [Blog | MVP], que foi Regional Mentor (RM) do PASS no Brasil.

O MS-InfraRio tem como característica principal compartilhar conhecimento em diversas tecnologias Microsoft e dentre elas, o nosso querido SQL Server, tendo também a chancela do PASS como chapter no município do Rio de Janeiro.

Um grande desafio e responsabilidade!

Uma das primeiras ações que realizamos, foi a mudança do nome do Chapter para SQL Server RJ.

Nossa nova Logo.

logo-GRUPO-SQL

Objetivo é trazer maior identidade do chapter com a plataforma SQL Server e também utilizar um padrão de nome de chapters, como o SQL Server DF de Brasília e o SQL Server RS do Rio Grande do Sul, ambos com forte presença em seus estados.

Muitos projetos sendo desenvolvidos… Aguardem por novidades.

Agradeço pela confiança do Alexandro Prado e também ao Diego Nogare e Marcus Bittencourt atuais RM no Brasil.

Até o próximo post.

Leandro Ribeiro

Docs – SQL Server virtualizado

Publicado 30 de janeiro de 2014 por Leandro Ribeiro
Categorias: Estudo, SQL Server, Virtual PASS BR, Virtualização

Tags: , , , , ,

Olá Pessoal.

Seguindo a sugestão do amigo e MVP Felipe Ferreira [sqlboy], este post tem por objetivo compartilhar algumas documentações referente a virtualização do SQL Server seja no Hyper-v ou VMWare.

Estes documentos e alguns sites, tem me ajudado bastante nas melhores práticas, configuração e desempenho do SQL Server em ambientes virtuais.

WhitePapers

  • Hyper-V is the best virtualization solution for SQL Server – Link Download
  • SQL Server Performance in a VMware Infrastructure 3 Environment – Link Download
  • Microsoft® SQL Server on VMware® Best Practices Guide  – Link Download
  • Best Practices for Virtualizing and Managing SQL Server – Link Download
  • High Performance SQL Server Workloads on Hyper-V – Link Download
  • Running SQL Server with Hyper-v Dynamic Memory – Link Download

Blogs

  • Team Blog SQLOS (Dynamic Memory) – Acessar
  • Blog Ben Amstrong – Acessar
  • Pluralsight – SQL Server: Virtualization – Acessar

Espero que ajudem vocês…

Até o próximo post.

MVPShowCast

Publicado 11 de dezembro de 2013 por Leandro Ribeiro
Categorias: Eventos, Virtual PASS BR

Tags: , ,

Olá Pessoal.

Passando pra informar sobre mais um evento com ótimos palestrantes e conteúdo, o MVPShowCast.

MVPShowCast Flayer

MVP ShowCast é um evento virtual gratuito sobre tecnologias da Microsoft, sendo composto por uma série de webcasts transmitidos ao vivo e gravados para serem disponibilizados depois. O evento foi idealizado e é organizado por MVPs do Brasil com apoio da Microsoft.

O Evento terminará esta semana e você ainda pode se inscrever…

Tive a honra de ser convidado pelos meus amigos MVPs Nilton Pinheiro [Linkedin | Twitter] e Luan Moreno [Blog | Twitter] para ser um dos palestrantes como membro do grupo MTAC.

Irei palestrar sobre “Melhores Práticas de Virtualização do SQL Server no Hyper-V” no dia 12 de Dezembro as 12:00hs.
Nesta palestra, mostrarei as principais novidades do Hyper-v 2012 e quais as configurações recomendadas para virtualização de ambientes de banco de dados.

Link de inscrição

Palestra

Espero vocês…

Até o próximo post.

24 Horas de PASS em português 2013 – 24HoP

Publicado 30 de outubro de 2013 por Leandro Ribeiro
Categorias: Eventos, Virtual PASS BR

Tags: , , , ,

Olá Pessoal.

Voltando de férias! \o/

Compartilhando mais um grande evento promovido pelo PASS (Professional Association for SQL Server).

24 Hours of PASS

Esta é uma edição em português do 24 Hours of PASS  que é um evento online e gratuito com alguns dos principais especialistas em SQL Server do Brasil e de Portugal.

O evento será realizado nos dias 13 e 14 de novembro e estarei palestrando no evento pela primeira vez.

Você pode se inscrever no site do evento:
http://www.sqlpass.org/24hours/2013/portuguese/Home.aspx

Minha sessão será no dia 14 de Novembro as 19hs (Horário de Brasília) e vou falar sobre “Virtualizar o SQL Server de alto desempenho é possível?”

Lembrando pessoal… Está uma ótima oportunidade de aprender um pouco mais do SQL Server de forma GRATUITA.
Não percam!

Espero vocês lá.

Webcast Virtual PASS PT – 03/10/2013

Publicado 2 de outubro de 2013 por Leandro Ribeiro
Categorias: Estudo, Eventos, Virtual PASS BR

Tags: , , ,

Olá Pessoal.

Nesta quinta-feira (03/10/2013) as 21hs, estarei falando na webcast do VIRTUAL PASS PT sobre Hyper-V 2012 com foco na virtualização de SGBD’s.

Se você está pensando em virtualizar seu banco de dados, não perca a oportunidade.

Então pessoal, espero vocês amanhã.

Link de inscrição

Abs.

Leandro Ribeiro


Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 601 outros seguidores