Descrição dos algoritmos de log e armazenamento de dados que estendem a confiabilidade de dados no SQL Server

Versão original do produto: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Número original do KB: 230785

Resumo

Este artigo discute como os algoritmos de dados e log do Microsoft SQL Server estendem a confiabilidade e a integridade dos dados.

Para saber mais sobre os conceitos subjacentes dos mecanismos e sobre o Algoritmo para Recuperação e Isolamento Explorando a Semântica (ARIES), consulte o seguinte documento ACM Transactions on Database Systems (no Volume 17, Número 1, março de 1992):

Link externo: ARIES: Um método de recuperação de transação que suporta bloqueio de granularidade fina e reversões parciais usando registro write-ahead

O documento aborda as técnicas do SQL Server para estender a confiabilidade e a integridade dos dados em relação a falhas.

Recomendamos que você leia os seguintes artigos na Base de Dados de Conhecimento da Microsoft para obter mais informações sobre o cache e discussões sobre modos de falha alternativos:

• Descrição dos controladores de disco de cache no SQL Server

• Informações sobre o uso de caches de unidade de disco com o SQL Server que todo administrador de banco de dados deve saber

Termos usados neste artigo

Antes de começarmos a discussão aprofundada, alguns dos termos usados ao longo deste artigo são definidos na tabela a seguir.

Termo	Definição
Alimentado por bateria	Recurso de backup de bateria separado e localizado diretamente disponível e controlado pelo mecanismo de cache para evitar a perda de dados. Esta não é uma fonte de alimentação ininterrupta (UPS). Um no-break não garante nenhuma atividade de gravação e pode ser desconectado do dispositivo de cache.
Cache	Mecanismo de armazenamento intermediário usado para otimizar as operações de E/S física e melhorar o desempenho.
Página suja	Página que contém modificações de dados que ainda não foram liberadas para armazenamento estável. Para obter mais informações sobre buffers de página suja, consulte Escrevendo páginas nos Manuais Online do SQL Server. O conteúdo também se aplica ao Microsoft SQL Server 2012 e versões posteriores.
Falha	Qualquer coisa que possa causar uma interrupção inesperada do processo do SQL Server. Os exemplos incluem: queda de energia, reinicialização do computador, erros de memória, outros problemas de hardware, setores defeituosos, interrupções de unidade, falhas do sistema e assim por diante.
Liberar	Forçar um buffer de cache para armazenamento estável.
Trava	Objeto de sincronização usado para proteger a consistência física de um recurso.
Armazenamento não volátil	Qualquer meio que permaneça disponível em caso de falhas do sistema.
Página fixada	Página que permanece no cache de dados e não pode ser liberada para armazenamento estável até que todos os registros de log associados sejam protegidos em um local de armazenamento estável.
Armazenamento estável	O mesmo que armazenamento não volátil.
Armazenamento volátil	Qualquer meio que não permanecerá intacto entre falhas.

Protocolo WAL (Write-Ahead Logging)

O termo protocolo é uma excelente maneira de descrever o WAL. É um conjunto específico e definido de etapas de implementação necessárias para garantir que os dados sejam armazenados e trocados corretamente e possam ser recuperados para um estado conhecido se houver uma falha. Assim como uma rede contém um protocolo definido para trocar dados de maneira consistente e protegida, o WAL também descreve o protocolo para proteger dados.

O documento ARIES define o WAL da seguinte forma:

O protocolo WAL afirma que os registros de log que representam alterações em alguns dados já devem estar em armazenamento estável antes que os dados alterados possam substituir a versão anterior dos dados no armazenamento não volátil. Ou seja, o sistema não tem permissão para gravar uma página atualizada na versão de armazenamento não volátil da página até que pelo menos as partes de desfazer dos registros de log, que descrevem as atualizações na página, tenham sido gravadas no armazenamento estável.

Para obter mais informações sobre o log write-ahead, consulte o tópico Log de transações write-ahead nos Manuais Online do SQL Server.

SQL Server e o WAL

O SQL Server usa o protocolo WAL. Para garantir que uma transação seja confirmada corretamente, todos os registros de log associados à transação devem ser protegidos no armazenamento estável.

Para esclarecer essa situação, considere o seguinte exemplo específico.

Observação

Para este exemplo, suponha que não haja índice e que a página afetada seja a página 150.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Em seguida, divida a atividade em etapas de log simplistas, conforme descrito na tabela a seguir.

Instrução	Ações realizadas
BEGINTRANSACTION	Gravado na área de cache de log. No entanto, não é necessário liberar para o armazenamento estável porque o SQL Server não fez nenhuma alteração física.
INSERIR EM tblTest	1. A página de dados 150 é recuperada no cache de dados do SQL Server, se ainda não estiver disponível. 2. A página é travada, fixada e marcada como suja, e as travas apropriadas são obtidas. 3. Um registro de Inserir Log é criado e adicionado ao cache de log. 4. Uma nova linha é adicionada à página de dados. 5. A trava é liberada. 6. Os registros de log associados à transação ou página não precisam ser liberados neste momento porque todas as alterações permanecem no armazenamento volátil.
COMMIT TRANSACTION	1. Um registro de log de confirmação é formado e os registros de log associados à transação devem ser gravados no armazenamento estável. A transação não é considerada confirmada até que os registros de log sejam atribuídos corretamente ao armazenamento estável. 2. A página de dados 150 permanece no cache de dados do SQL Server e não é liberada imediatamente para o armazenamento estável. Quando os registros de log são protegidos corretamente, a recuperação pode refazer a operação, se necessário. 3. Os bloqueios transacionais são liberados.

Não se confunda com os termos "bloqueio" e "registro em log". Embora importantes, o bloqueio e o registro em log são problemas separados quando você lida com o WAL. No exemplo anterior, o SQL Server geralmente mantém a trava na página 150 pelo tempo necessário para executar as alterações de inserção física na página, não durante todo o tempo da transação. O tipo de bloqueio apropriado é estabelecido para proteger a linha, o intervalo, a página ou a tabela, conforme necessário. Consulte as seções de bloqueio dos Manuais Online do SQL Server para obter mais detalhes sobre os tipos de bloqueio.

Observando o exemplo com mais detalhes, você pode perguntar o que acontece quando os processos LazyWriter ou CheckPoint são executados. O SQL Server emite todas as liberações apropriadas para o armazenamento estável para registros de log transacionais associados à página suja e fixada. Isso garante que a página de dados do protocolo WAL nunca possa ser gravada no armazenamento estável até que os registros de log transacional associados tenham sido liberados.

SQL Server e armazenamento estável

O SQL Server aprimora as operações de log e página de dados incluindo o conhecimento dos tamanhos dos setores de disco (geralmente 4.096 bytes ou 512 bytes).

Para manter as propriedades ACID de uma transação, o SQL Server deve levar em conta os pontos de falha. Durante uma falha, muitas especificações de unidade de disco garantem apenas um número limitado de operações de gravação de setor. A maioria das especificações garante a conclusão de uma única gravação de setor quando ocorre uma falha.

O SQL Server usa páginas de dados de 8 KB e o log (se liberado) em múltiplos do tamanho do setor. (A maioria das unidades de disco usa 512 bytes como o tamanho padrão do setor.) Se houver uma falha, o SQL Server poderá considerar operações de gravação maiores que um setor empregando paridade de log e técnicas de gravação interrompida.

Detecção de página interrompida

Essa opção permite que o SQL Server detecte operações de E/S incompletas causadas por falhas de energia ou outras interrupções do sistema. Quando true, ele faz com que um bit seja invertido para cada setor de 512 bytes em uma página de banco de dados de 8 kilobytes (KB) sempre que a página é gravada no disco. Se um bit estiver no estado errado quando a página for lida posteriormente pelo SQL Server, a página foi gravada incorretamente; uma página interrompida é detectada. Páginas interrompidas são detectadas durante a recuperação porque qualquer página que foi gravada incorretamente provavelmente será lida pela recuperação.

Embora as páginas do banco de dados do SQL Server tenham 8 KB, os discos executam operações de E/S usando um setor de 512 bytes. Portanto, 16 setores são gravados por página do banco de dados. Uma página interrompida pode ocorrer se o sistema falhar (por exemplo, devido a uma falha de energia) entre o momento em que o sistema operacional grava o primeiro setor de 512 bytes no disco e a conclusão da operação de E/S de 8 KB. Se o primeiro setor de uma página de banco de dados for gravado com êxito antes da falha, a página do banco de dados no disco aparecerá como atualizada, embora possa não ter sido bem-sucedida.

Usando caches de controlador de disco com suporte de bateria, você pode garantir que os dados sejam gravados com êxito no disco ou não sejam gravados. Nessa situação, não defina a detecção de página interrompida como "true" porque isso não é necessário.

Observação

A detecção de página interrompida não está habilitada por padrão no SQL Server. Para obter mais informações, veja Opções ALTER DATABASE SET (Transact-SQL).

Paridade de log

A verificação de paridade de log é semelhante à detecção de página interrompida. Cada setor de 512 bytes contém bits de paridade. Esses bits de paridade são sempre gravados com o registro de log e avaliados quando o registro de log é recuperado. Ao forçar gravações de log em um limite de 512 bytes, o SQL Server pode garantir que as operações de confirmação sejam gravadas nos setores do disco físico.

Impactos no desempenho

Todas as versões do SQL Server abrem os arquivos de log e dados usando a função Win32 CreateFile. O membro dwFlagsAndAttributes inclui a FILE_FLAG_WRITE_THROUGH opção quando eles são abertos pelo SQL Server.

FILE_FLAG_WRITE_THROUGH Instrui o sistema a gravar em qualquer cache intermediário e ir diretamente para o disco. O sistema ainda pode armazenar as operações de gravação em cache, mas não pode liberá-las lentamente.

A FILE_FLAG_WRITE_THROUGH opção garante que, quando uma operação de gravação retornar uma conclusão bem-sucedida, os dados sejam armazenados corretamente no armazenamento estável. Isso se alinha com o protocolo WAL que garante os dados.

Muitas unidades de disco (SCSI e IDE) contêm caches integrados de 512 KB, 1 MB ou mais. No entanto, os caches da unidade geralmente dependem de um capacitor e não de uma solução com bateria. Esses mecanismos de cache não podem garantir gravações em um ciclo de energia ou ponto de falha semelhante. Eles apenas garantem a conclusão das operações de escrita do setor. É especificamente por isso que a gravação interrompida e a detecção de paridade de log foram incorporadas ao SQL Server 7.0 e versões posteriores. À medida que as unidades continuam a crescer em tamanho, os caches se tornam maiores e podem expor maiores quantidades de dados durante uma falha.

Muitos fornecedores de hardware fornecem soluções de controlador de disco com suporte de bateria. Esses caches do controlador podem manter os dados no cache por vários dias e até mesmo permitir que o hardware de cache seja colocado em um segundo computador. Quando a energia é restaurada corretamente, os dados não gravados são liberados antes que o acesso adicional aos dados seja permitido. Muitos deles permitem que uma porcentagem de cache de leitura versus gravação seja estabelecida para um desempenho ideal. Alguns contêm grandes áreas de armazenamento em memória. Na verdade, para um segmento específico do mercado, alguns fornecedores de hardware fornecem sistemas controladores de cache de disco com bateria de última geração com 6 GB de cache. Estes podem melhorar significativamente o desempenho do banco de dados.

As implementações avançadas de cache lidarão com a FILE_FLAG_WRITE_THROUGH solicitação não desabilitando o cache do controlador porque podem fornecer recursos de regravação verdadeiros no caso de uma reinicialização do sistema, falha de energia ou outro ponto de falha.

As transferências de E/S sem o uso de um cache podem ser mais longas devido ao tempo mecânico necessário para mover os cabeçotes da unidade, as taxas de rotação e outros fatores limitantes.

Ordenação do setor

Uma técnica comum usada para aumentar o desempenho de E/S é a ordenação de setores. Para evitar o movimento mecânico do cabeçote, as solicitações de leitura/gravação são classificadas, permitindo um movimento mais consistente do cabeçote para recuperar ou armazenar dados.

O cache pode conter várias solicitações de gravação de log e dados ao mesmo tempo. O protocolo WAL e a implementação do protocolo WAL do SQL Server exigem a liberação das gravações de log para o armazenamento estável antes que a gravação de página possa ser emitida. No entanto, o uso do cache pode retornar êxito de uma solicitação de gravação de log sem que os dados sejam gravados na unidade real (ou seja, gravados no armazenamento estável). Isso pode fazer com que o SQL Server emita a solicitação de gravação da página de dados.

Com o envolvimento do cache de gravação, os dados ainda são considerados em armazenamento volátil. No entanto, na chamada WriteFile da API do Win32, exatamente como o SQL Server vê a atividade, um código de retorno bem-sucedido foi obtido. O SQL Server ou qualquer processo que use a chamada à API WriteFile pode determinar apenas que os dados obtiveram corretamente o armazenamento estável.

Para fins de discussão, suponha que todos os setores da página de dados sejam classificados para gravação antes dos setores dos registros de log correspondentes. Isso viola imediatamente o protocolo WAL. O cache está gravando uma página de dados antes dos registros de log. A menos que o cache seja totalmente alimentado por bateria, uma falha pode causar resultados catastróficos.

Quando você avalia os fatores de desempenho ideais para um servidor de banco de dados, há muitos fatores a serem considerados. O mais importante deles é: "Meu sistema permite recursos válidos FILE_FLAG_WRITE_THROUGH ?"

Observação

Qualquer cache que você estiver usando deve oferecer suporte total a uma solução com suporte de bateria. Todos os outros mecanismos de cache são propensos a corrupção e perda de dados. O SQL Server faz todos os esforços para garantir o WAL habilitando FILE_FLAG_WRITE_THROUGHo .

Os testes mostraram que muitas configurações de unidade de disco podem conter cache de gravação sem o backup de bateria apropriado. As unidades SCSI, IDE e EIDE aproveitam ao máximo os caches de gravação. Para obter mais informações sobre como os SSDs funcionam em conjunto com o SQL Server, consulte o seguinte artigo do blog CSS SQL Server Engineers:

SQL Server e SSDs - Notas de Aprendizagem do RDORR - Parte 1

Em muitas configurações, a única maneira de desabilitar corretamente o cache de gravação de uma unidade IDE ou EIDE é usando um utilitário específico do fabricante ou usando jumpers localizados na própria unidade. Para garantir que o cache de gravação esteja desabilitado para a própria unidade, entre em contato com o fabricante da unidade.

As unidades SCSI também têm caches de gravação. No entanto, esses caches geralmente podem ser desabilitados pelo sistema operacional. Se houver alguma dúvida, entre em contato com o fabricante da unidade para obter os utilitários apropriados.

Empilhamento de cache de gravação

O empilhamento de cache de gravação é semelhante à ordenação de setor. A definição a seguir foi tirada diretamente do site de um fabricante líder de unidades IDE:

Normalmente, este modo está ativo. O modo de cache de gravação aceita os dados de gravação do host no buffer até que o buffer esteja cheio ou a transferência do host seja concluída.

Uma tarefa de gravação de disco começa a armazenar os dados do host no disco. Os comandos de gravação do host continuam a ser aceitos e os dados transferidos para o buffer até que a pilha de comandos de gravação esteja cheia ou o buffer de dados esteja cheio. A unidade pode reordenar comandos de gravação para otimizar a taxa de transferência da unidade.

Realocação automática de gravação (AWR)

Outra técnica comum usada para proteger dados é detectar setores defeituosos durante a manipulação de dados. A explicação a seguir vem do site de um fabricante líder de unidades IDE:

Esse recurso faz parte do cache de gravação e reduz o risco de perda de dados durante operações de gravação adiadas. Se ocorrer um erro de disco durante o processo de gravação do disco, a tarefa do disco será interrompida e o setor suspeito será realocado para um pool de setores alternativos localizados no final da unidade. Após a realocação, a tarefa de gravação de disco continua até ser concluída.

Esse pode ser um recurso poderoso se o backup de bateria for fornecido para o cache. Isso fornece a modificação apropriada na reinicialização. É preferível detectar os erros de disco, mas a segurança dos dados do protocolo WAL exigiria novamente que isso fosse feito em tempo real e não de maneira diferida. Dentro dos parâmetros WAL, a técnica AWR não pode levar em conta uma situação em que uma gravação de log falha devido a um erro de setor, mas a unidade está cheia. O mecanismo de banco de dados deve saber imediatamente sobre a falha para que a transação possa ser anulada corretamente, o administrador possa ser alertado e as etapas corretas sejam executadas para proteger os dados e corrigir a situação de falha de mídia.

Segurança de dados

Existem várias precauções que um administrador de banco de dados deve tomar para garantir a segurança dos dados.

• É sempre uma boa ideia certificar-se de que sua estratégia de backup seja suficiente para se recuperar de uma falha catastrófica. O armazenamento externo e outras precauções são apropriados.
• Teste a operação de restauração de banco de dados em um banco de dados secundário ou de teste com frequência.
• Certifique-se de que todos os dispositivos de cache possam lidar com todas as situações de falha (queda de energia, setores defeituosos, unidades defeituosas, interrupção do sistema, travamentos, pico de energia e assim por diante).
• Certifique-se de que seu dispositivo de cache:
  • Possui backup de bateria integrado
  • Pode reemitir gravações na inicialização
  • Pode ser totalmente desativado se for necessário
  • Lida com o remapeamento de setores defeituosos em tempo real
• Ative a detecção de página interrompida. (Isso tem pouco efeito no desempenho.)
• Configure unidades RAID permitindo uma troca a quente de uma unidade de disco defeituosa, se possível.
• Use controladores de cache mais recentes que permitem adicionar mais espaço em disco sem reiniciar o sistema operacional. Esta pode ser uma solução ideal.

Unidades de teste

Para proteger totalmente seus dados, você deve certificar-se de que todo o cache de dados seja tratado corretamente. Em muitas situações, você deve desabilitar o cache de gravação da unidade de disco.

Observação

Certifique-se de que um mecanismo de cache alternativo possa lidar corretamente com vários tipos de falha.

A Microsoft realizou testes em várias unidades SCSI e IDE usando o SQLIOSim utilitário. Esse utilitário simula atividade de leitura/gravação assíncrona pesada para um dispositivo de dados simulado e um dispositivo de log. As estatísticas de desempenho do teste mostram a média de operações de gravação por segundo entre 50 e 70 para uma unidade com cache de gravação desabilitado e um intervalo de RPM entre 5.200 e 7.200.

Para obter mais informações sobre o SQLIOSim utilitário, consulte o seguinte artigo na Base de Dados de Conhecimento da Microsoft:

Como usar o utilitário SQLIOSim para simular a atividade do SQL Server em um subsistema de disco

Muitos fabricantes de computadores solicitam as unidades desativando o cache de gravação. No entanto, os testes mostram que nem sempre é esse o caso. Portanto, sempre teste completamente.

Dispositivos de dados

Em todas as situações, exceto as não registradas, o SQL Server exigirá que apenas os registros de log sejam liberados. Ao fazer operações não registradas, as páginas de dados também devem ser liberadas para armazenamento estável; Não há registros de log individuais para regenerar as ações em caso de falha.

As páginas de dados podem permanecer no cache até que o processo LazyWriter ou CheckPoint as libere para o armazenamento estável. Usar o protocolo WAL para garantir que os registros de log sejam armazenados corretamente garante que a recuperação possa recuperar uma página de dados para um estado conhecido.

Isso não significa que seja aconselhável colocar arquivos de dados em uma unidade em cache. Quando o SQL Server libera as páginas de dados para o armazenamento estável, os registros de log podem ser truncados do log de transações. Se as páginas de dados forem armazenadas em cache volátil, será possível truncar registros de log que seriam usados para recuperar uma página em caso de falha. Certifique-se de que seus dispositivos de dados e log acomodem o armazenamento estável corretamente.

Aumentando o desempenho

A primeira pergunta que pode ocorrer a você é: "Eu tenho uma unidade IDE que estava armazenando em cache. Mas quando eu o desativei, meu desempenho ficou menor do que o esperado. Por quê?"

Muitas das unidades IDE testadas pela Microsoft são executadas a 5.200 RPM e as unidades SCSI a 7.200 RPM. Quando você desativa o cache de gravação da unidade IDE, o desempenho mecânico pode se tornar um fator.

Para resolver a diferença de desempenho, o método a seguir é claro: "Abordar a taxa de transação".

Muitos sistemas OLTP (processamento de transações online) exigem uma alta taxa de transação. Para esses sistemas, considere usar um controlador de cache que possa dar suporte apropriado a um cache de gravação e fornecer o aumento de desempenho desejado, garantindo a integridade dos dados.

Para observar alterações significativas de desempenho que ocorrem no SQL Server em uma unidade de cache, a taxa de transação foi aumentada usando pequenas transações.

Os testes mostram que a alta atividade de gravações de buffers com menos de 512 KB ou mais de 2 MB pode causar desempenho lento.

Considere o seguinte exemplo:

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

Veja a seguir exemplos de resultados de teste para SQL Server:

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

O processo de encapsulamento de toda a série de INSERT operações em uma única transação é executado em aproximadamente quatro segundos em todas as configurações. Isso ocorre devido ao número de liberações de log necessárias. Se você não criar uma única transação, cada INSERT uma será processada como uma transação separada. Portanto, todos os registros de log da transação devem ser liberados. Cada flush tem 512 bytes de tamanho. Isso requer uma intervenção significativa do acionamento mecânico.

Quando uma única transação é usada, os registros de log da transação podem ser agrupados e uma única gravação maior pode ser usada para liberar os registros de log coletados. Isso reduz significativamente a intervenção mecânica.

Aviso

Recomendamos que você não aumente o escopo da transação. Transações de longa duração podem causar bloqueio excessivo e indesejado e aumento da sobrecarga. Use os contadores de desempenho SQL Server:Databases SQL Server para exibir os contadores baseados em log de transações. Especificamente, Bytes de Log Liberados/s pode indicar muitas transações pequenas que podem causar alta atividade de disco mecânico.

Examine as instruções associadas à liberação de log para determinar se o valor de Bytes de Log Liberados/s pode ser reduzido. No exemplo anterior, uma única transação foi usada. No entanto, em muitos cenários, isso pode causar um comportamento de bloqueio indesejado. Examine o design da transação. Você pode usar um código semelhante ao código a seguir para executar lotes para reduzir a atividade de liberação de log frequente e pequena:

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

O SQL Server requer que os sistemas ofereçam suporte à entrega garantida para mídia estável, conforme descrito no documento de download Requisitos de revisão do programa de confiabilidade de E/S do SQL Server. Para obter mais informações sobre os requisitos de entrada e saída para o mecanismo de banco de dados do SQL Server, consulte Requisitos de entrada/saída do mecanismo de banco de dados do Microsoft SQL Server.