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

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

Resumo

Este artigo discute como a Microsoft SQL Server algoritmos de log e dados 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 Exploração de Isolamento Semântica (ARIES), confira o seguinte documento transações do ACM em Sistemas de Banco de Dados (em Volume 17, Número 1, março de 1992):

Link externo: ARIES: um método de Recuperação de Transações com suporte Fine-Granularity bloqueio e reversões parciais usando Write-Ahead registro em log

O documento aborda as técnicas de SQL Server para estender a confiabilidade e a integridade dos dados conforme relacionado a falhas.

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

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

• Informações sobre como usar caches de unidade de disco com SQL Server que todos os administradores de banco de dados devem saber

Termos usados neste artigo

Antes de iniciarmos a discussão detalhada, alguns dos termos usados ao longo deste artigo são definidos na tabela a seguir.

protocolo wal (registro em log de Write-Ahead)

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 do ARIES define o WAL da seguinte maneira:

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 involatile. Ou seja, o sistema não tem permissão para gravar uma página atualizada na versão de armazenamento nãovolatile 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 com antecedência de gravação, consulte o tópico Log de Transações Com Antecedência em SQL Server Livros Online.

SQL Server e o WAL

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

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

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.

Não se confunda com os termos "bloqueio" e "registro em log". Embora importantes, bloqueios e logs sejam problemas separados quando você lida com o WAL. No exemplo anterior, 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 o tempo inteiro 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 SQL Server Books Online para obter mais detalhes sobre tipos de bloqueio.

Olhando para o exemplo com mais detalhes, você pode perguntar o que acontece quando os processos LazyWriter ou CheckPoint são executados. SQL Server emite todas as liberações apropriadas para armazenamento estável para registros de log transacionais associados à página sujo 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 transacionais associados tenham sido liberados.

SQL Server e armazenamento estável

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

Para manter as propriedades ACID de uma transação, o SQL Server deve responder por 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 do setor. A maioria das especificações garante a conclusão de uma gravação de um único setor quando ocorre uma falha.

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, SQL Server poderá responder por operações de gravação maiores que um setor empregando a paridade de log e técnicas de gravação rasgadas.

Detecção de página rasgada

Essa opção permite que 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 KB (8 quilobytes) sempre que a página é gravada em disco. Se um bit estiver no estado errado quando a página for lida posteriormente por SQL Server, a página será escrita incorretamente; uma página rasgada será detectada. Páginas rasgadas são detectadas durante a recuperação porque qualquer página que foi escrita incorretamente provavelmente será lida pela recuperação.

Embora SQL Server páginas de banco de dados sejam 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 de banco de dados. Uma página rasgada 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 em 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 de banco de dados em disco será exibida como atualizada, embora possa não ter sido bem-sucedida.

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

Paridade de log

A verificação de paridade de log é semelhante à detecção de página rasgada. 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, SQL Server pode garantir que as operações de confirmação sejam gravadas nos setores de disco físico.

Impactos de desempenho

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

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

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 ao protocolo WAL que garante os dados.

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

Muitos fornecedores de hardware fornecem soluções de controlador de disco com suporte à bateria. Esses caches de 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 escritos são liberados antes que o acesso a dados adicional 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 de memória. De fato, para um segmento específico do mercado, alguns fornecedores de hardware fornecem sistemas de controlador de cache de disco com suporte de bateria high-end com 6 GB de cache. Isso pode melhorar significativamente o desempenho do banco de dados.

Implementações avançadas de cache lidarão com a FILE_FLAG_WRITE_THROUGH solicitação não desabilitando o cache do controlador porque elas podem fornecer recursos de reescrita verdadeiros no caso de uma redefiniçã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 as cabeças de 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 do setor. Para evitar o movimento mecânico da cabeça, as solicitações de leitura/gravação são classificadas, permitindo que um movimento mais consistente da cabeça recupere ou armazene 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 SQL Server do protocolo WAL exigem a liberação das gravações de log para o armazenamento estável antes que a gravação da página possa ser emitida. No entanto, o uso do cache pode retornar o sucesso de uma solicitação de gravação de log sem que os dados sejam gravados na unidade real (ou seja, gravados em armazenamento estável). Isso pode levar a SQL Server emitir 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 win32, exatamente como SQL Server vê a atividade, um código de retorno bem-sucedido foi obtido. SQL Server ou qualquer processo que use a chamada de 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 gravar 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 apoiado 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 ?"

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 SQL Server, confira o seguinte artigo do CSS SQL Server Engineers Blog:

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 de fabricante específico ou usando jumpers localizados na própria unidade. Para garantir que o cache de gravação esteja desabilitado para a unidade em si, 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 utilitários apropriados.

Empilhamento de cache de gravação

O Empilhamento de Cache de Gravação é semelhante ao Pedido do Setor. A definição a seguir foi tirada diretamente de um site líder do fabricante de unidade IDE:

Normalmente, esse 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 em 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 ruins durante a manipulação de dados. A explicação a seguir vem de um site líder do fabricante de unidade 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 de disco, a tarefa de 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é que ela seja concluída.

Esse pode ser um recurso poderoso se o backup de bateria for fornecido para o cache. Isso fornece a modificação apropriada após a reinicialização. É preferível detectar os erros de disco, mas a segurança de dados do protocolo WAL exigiria novamente que isso fosse feito em tempo real e não de forma adiada. Dentro dos parâmetros wal, a técnica AWR não pode considerar uma situação em que uma gravação de log falha devido a um erro do 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 corretamente anulada, o administrador possa ser alertado e as etapas corretas tomadas para proteger os dados e corrigir a situação de falha de mídia.

Segurança de dados

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

• É sempre uma boa ideia garantir que sua estratégia de Backup seja suficiente para se recuperar de uma falha catastrófica. O armazenamento fora do local 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.
• Verifique se todos os dispositivos de cache podem lidar com todas as situações de falha (interrupção de energia, setores ruins, unidades ruins, interrupção do sistema, bloqueios, pico de energia e assim por diante).
• Verifique se seu dispositivo de cache:
  • Tem backup integrado de bateria
  • Pode reedição de gravações no power-up
  • Pode ser totalmente desabilitado se for necessário
  • Manipula o remapping de setor ruim em tempo real
• Habilitar a detecção de página rasgada. (Isso tem pouco efeito no desempenho.)
• Configure unidades RAID que permitem uma troca quente de uma unidade de disco ruim, se for possível.
• Use controladores de cache mais recentes que permitem adicionar mais espaço em disco sem reiniciar o sistema operacional. Essa pode ser uma solução ideal.

Testando unidades

Para proteger totalmente seus dados, você deve garantir 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.

A Microsoft realizou testes em várias unidades SCSI e IDE usando o SQLIOSim utilitário. Esse utilitário simula uma atividade de leitura/gravação assíncrona pesada para um dispositivo de dados simulado e um dispositivo de log. As estatísticas de desempenho de teste mostram as operações médias 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, confira o seguinte artigo na Base de Dados de Conhecimento da Microsoft:

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

Muitos fabricantes de computador encomendam as unidades tendo o cache de gravação desabilitado. No entanto, os testes mostram que isso pode nem sempre ser o caso. Portanto, sempre teste completamente.

Dispositivos de dados

Em todas as situações, mas não registradas, SQL Server exigirá apenas que 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 no caso de uma falha.

As páginas de dados podem permanecer em 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 armazenada 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, é possível truncar registros de log que seriam usados para recuperar uma página em caso de falha. Verifique se seus dados e dispositivos de log acomodam o armazenamento estável corretamente.

Aumentando o desempenho

A primeira pergunta que pode ocorrer a você é: "Eu tenho uma unidade IDE que estava em cache. Mas quando o desabilitei, meu desempenho se tornou 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ê desabilitar 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: "Endereçar 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 a um cache de gravação e fornecer o aumento de desempenho desejado, ao mesmo tempo em que garante a integridade dos dados.

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

Os testes mostram que a atividade de gravação alta de buffers com menos de 512 KB ou superior a 2 MB pode causar desempenho lento.

Considere o exemplo seguinte:

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 os resultados do teste de exemplo 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 encapsular 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 que são 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 de unidade mecânica significativa.

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

Examine as instruções associadas ao log flush para determinar se o valor do Log Bytes Flushed/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

SQL Server exige que os sistemas ofereçam suporte à entrega garantida para mídia estável, conforme descrito no documento de download dos Requisitos de Revisão do Programa de Confiabilidade de E/S SQL Server. Para obter mais informações sobre os requisitos de entrada e saída do mecanismo de banco de dados SQL Server, consulte Mecanismo de Banco de Dados do Microsoft SQL Server Requisitos de Entrada/Saída.