Fundamentos de E/S do SQL Server

Aplica-se a:SQL ServerInstância Gerenciada de SQL do AzureSQL Server em Máquinas Virtuais do Azure

A principal finalidade de um banco de dados do SQL Server é armazenar e recuperar dados, de modo que a intensa E/S de disco é uma característica importante do mecanismo de banco de dados. Como as operações de E/S de disco podem consumir muitos recursos e levar um tempo relativamente longo para terminar, o SQL Server se concentra em tornar a E/S altamente eficiente.

Os subsistemas de armazenamento do SQL Server são fornecidos em vários fatores de forma, incluindo unidades mecânicas e armazenamento de estado sólido. Este artigo contém detalhes sobre como usar princípios de cache da unidade para melhorar a E/S do mecanismo de banco de dados.

O SQL Server requer que os sistemas ofereçam suporte à entrega garantida para mídia estável, conforme descrito nos Requisitos 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 (E/S) de disco do mecanismo de banco de dados do SQL Server.

E/S de disco

O gerenciador de buffer apenas faz leituras e gravações no banco de dados. Outras operações de arquivo e banco de dados, como abrir, fechar, estender e reduzir são executadas pelos componentes do gerenciador de banco de dados e de arquivos.

As operações de E/S de disco pelo gerenciador de buffer têm as seguintes características:

• A E/S normalmente é executada de forma assíncrona, o que permite ao thread de chamada continuar o processamento enquanto a operação de E/S acontece em segundo plano. Em algumas circunstâncias (por exemplo, E/S de log desalinhadas), pode haver E/S síncrona.

• Todas as E/S são emitidas nos threads de chamada, a menos que a opção affinity I/O esteja em uso. A opção affinity I/O mask associa o E/de disco de SQL Server a um subconjunto especificado de CPUs. Em ambientes OLTP (online transactional processing) avançados de SQL Server , esta extensão pode aumentar o desempenho de threads SQL Server que emitem E/S.

• Várias E/Ss de página são realizadas com E/S de dispersar e reunir, que permite transferir dados para dentro ou para fora de áreas não contíguas de memória. Isso significa que o SQL Server pode preencher ou liberar o cache do buffer rapidamente enquanto evita várias solicitações de E/S físicas.

Solicitações de E/S demoradas

O gerenciador de buffer informa sobre qualquer solicitação de E/S pendente durante pelo menos 15 segundos. Esse processo ajuda o administrador do sistema a distinguir entre problemas do SQL Server e problemas de subsistema de E/S. A Mensagem de erro MSSQLSERVER_833 é informada e exibida no log de erros do SQL Server da seguinte forma:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

Uma operação de E/S longa pode ser uma leitura ou uma gravação; a mensagem não indica atualmente qual. Mensagens de E/S demoradas são avisos, não erros. Elas não indicam problemas com o SQL Server, mas com o sistema de E/S subjacente. As mensagens ajudam o administrador do sistema a encontrar a causa de tempos de resposta ruins do SQL Server mais rapidamente e a distinguir problemas que estão fora do controle do SQL Server. Como tal, eles não exigem nenhuma ação, mas o administrador do sistema deve investigar por que a solicitação de E/S demorou tanto e se o tempo é justificável.

Causas de solicitações de E/S demoradas

Uma mensagem de E/S longa pode indicar que uma E/S está permanentemente bloqueada e nunca será concluída (conhecida como E/S perdida) ou apenas que ela ainda não está concluída. Você não pode determinar pela mensagem qual cenário está ocorrendo, embora um I/O perdido geralmente leve a um tempo limite de trava.

Operações de E/S longas geralmente indicam uma demanda de trabalho do SQL Server muito intensa para o subsistema de disco. Um subsistema de disco inadequado pode ser indicado quando:

• Várias mensagens de E/S demorada são exibidas no log de erros durante uma carga de trabalho pesada do SQL Server.
• Contadores de monitor de desempenho mostram latências de disco demoradas, filas de disco demoradas ou nenhum tempo ocioso de disco.

Um componente no caminho de E/S (por exemplo, um driver, controlador ou firmware) pode causar E/Ss longas, adiando continuamente a manutenção de uma solicitação de E/S antiga, em favor da manutenção de solicitações mais recentes. Esse problema pode ocorrer em ambientes interconectados, como redes iSCSI e Fibre Channel (devido a uma falha de configuração ou de caminho). A ferramenta Monitor de Desempenho pode dificultar a confirmação desse problema porque a maioria das E/Ss está sendo atendida prontamente. Cargas de trabalho que executam grandes quantidades de E/S sequencial, como backup e restauração, verificações de tabela, classificação, criação de índices, carregamentos em massa e zerar arquivos, podem agravar solicitações longas de E/S.

E/Ss demoradas e isoladas que não aparecem relacionadas a quaisquer condições anteriores podem ser causadas por um problema de hardware ou driver. O log de eventos do sistema pode conter um evento relacionado que ajuda a diagnosticar o problema.

Problemas de desempenho de E/S causados por consultas ou drivers de filtro ineficientes

A E/S lenta pode ser causada por consultas que não são escritas com eficiência ou ajustadas corretamente com índices e estatísticas. Outro fator comum na latência de E/S é a presença de antivírus ou outros programas de segurança que varrem os arquivos de banco de dados. Esse software de varredura pode se estender para a camada de rede, o que aumenta a latência da rede, por sua vez afetando indiretamente a latência do banco de dados. Embora o cenário descrito com cerca de 15 segundos de E/S seja mais comum com componentes de hardware, atrasos de E/S mais curtos são observados com mais frequência com consultas não otimizadas ou programas antivírus mal configurados.

Para obter informações detalhadas sobre como tratar desses problemas, consulte Solucionar problemas de desempenho lento do SQL Server causado por problemas de E/S.

Para obter informações sobre como configurar a proteção antivírus no SQL Server, consulte Configurar o software antivírus para funcionar com o SQL Server.

Cache de gravação em controladores de armazenamento

Transferências de E/S que não utilizam cache podem demorar muito mais em unidades mecânicas devido às taxas de rotação dos discos rígidos, ao tempo necessário para mover mecanicamente as cabeças da unidade, além de outros fatores limitantes. As instalações do SQL Server têm como destino sistemas de destino que fornecem controladores de cache. Esses controladores desabilitam os caches em disco e fornecem caches de mídia estáveis para atender aos requisitos de E/S do SQL Server. Eles evitam problemas de desempenho relacionados aos tempos de busca e gravação no armazenamento, usando as diversas otimizações do controlador de cache.

O uso de um controlador de armazenamento com cache de gravação (também chamado de cache com write-back) pode melhorar o desempenho do SQL Server. Os controladores de cache de gravação e os subsistemas de armazenamento são seguros para o SQL Server, se forem projetados para uso em um ambiente DBMS (sistema de gerenciamento de banco de dados transacional) crítico de dados. Esses recursos de projeto devem preservar os dados armazenados em cache se ocorrer uma falha do sistema. O uso de uma UPS (fonte de alimentação não ininterrupta) externa para obter essa proteção geralmente não é suficiente, pois os modos de falha que não estão relacionados à energia podem ocorrer.

Os controladores de cache e os subsistemas de armazenamento podem ser seguros para uso pelo SQL Server. A maioria das novas plataformas de servidor criadas com finalidade que incorporam esses controladores são seguras. No entanto, você deve verificar com o fornecedor de hardware para ter certeza de que o subsistema de armazenamento foi testado e aprovado para uso em um ambiente RDBMS (sistema de gerenciamento de banco de dados relacional) crítico a dados.

Diretrizes de segurança do subsistema de cache

Os controladores de cache de write-back poderão melhorar o desempenho se atenderem a requisitos de segurança específicos:

• Inclua cache com suporte de bateria ou memória não volátil, como NVDIMM ou flash com suporte de super capacitor.
• Seja certificado pelo fornecedor para ambientes de banco de dados OLTP críticos a dados.
• Forneça proteção que abrange todas as condições de falha, não apenas a perda de energia.

Registro em logs write-ahead

As instruções de modificação de dados do SQL Server geram gravações de página lógicas. Você pode imaginar esse fluxo de gravações como sendo direcionado a dois locais: o log e o próprio banco de dados. Por motivos de desempenho, o SQL Server adia gravações no banco de dados por meio de seu próprio sistema de buffer de cache. O sistema adia apenas momentaneamente as gravações no log até o momento de COMMIT. Ele não armazena essas escritas em cache da mesma maneira que as escritas de dados. Como as gravações de log de uma determinada página sempre vêm antes das gravações de dados da página, o log às vezes é chamado de WAL ( log de gravação antecipada ).

Protocolo de log write-ahead (WAL)

O termo protocolo é uma excelente maneira de descrever o WAL. O WAL usado pelo SQL Server é conhecido como ARIES (Algorithm for Recovery and Isolation Exploiting Semantics). Para obter mais informações, confira Gerenciar recuperação acelerada de banco de dados.

É 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 em caso de 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. Todas as versões do SQL Server abrem os arquivos de log e de dados usando a função CreateFile do Win32. O membro dwFlagsAndAttributes inclui a opção FILE_FLAG_WRITE_THROUGH quando aberto pelo SQL Server.

FILE_FLAG_WRITE_THROUGH

O SQL Server cria seus arquivos de banco de dados usando o sinalizador FILE_FLAG_WRITE_THROUGH. Essa opção instrui o sistema a atravessar qualquer cache intermediário e ir diretamente para o armazenamento. O sistema ainda pode armazenar em cache operações de gravação, mas não pode esvaziá-las tardiamente. Para obter mais informações, confira CreateFileA.

A FILE_FLAG_WRITE_THROUGH opção garante que, quando uma operação de gravação retornar a conclusão bem-sucedida, os dados sejam armazenados corretamente no armazenamento estável. Esse recurso está alinhado com a especificação do protocolo Write-Ahead Logging (WAL) para garantir a integridade dos dados. Muitos dispositivos de armazenamento (baseados em NVMe, PCIe, SATA, ATA, SCSI e IDE) contêm caches integrados de 512 KB, 1 MB ou mais. Os caches de armazenamento geralmente contam com uma solução de suporte com capacitor, e nã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. À medida que os dispositivos de armazenamento continuam a aumentar de tamanho, os caches se tornam maiores e podem expor maiores quantidades de dados durante uma falha.

Para saber mais sobre o suporte do FUA pela distribuição do Linux e o impacto dele no SQL Server, confira SQL Server em Linux: Elementos internos do FUA (Acesso forçado à unidade).

Integridade transacional e recuperação do SQL Server

A integridade transacional é um dos conceitos fundamentais de um sistema de banco de dados relacional. As transações são unidades atômicas de trabalho que são totalmente aplicadas ou totalmente revertidas. O log write-ahead de transações do SQL Server é um componente vital na implementação da integridade transacional.

Qualquer sistema de banco de dados relacional também deve lidar com um conceito intimamente relacionado à integridade transacional, que é a recuperação de falha não planejada do sistema. No mundo real, vários efeitos não ideais podem causar essa falha. Em muitos sistemas de gerenciamento de banco de dados, a falha do sistema pode resultar em um longo processo de recuperação manual conduzido por humanos.

Por outro lado, o mecanismo de recuperação do SQL Server é automático e opera sem intervenção humana. Por exemplo, o SQL Server pode estar dando suporte a uma aplicação de produção de missão crítica e enfrentar uma falha do sistema devido a uma flutuação momentânea de energia. Após a restauração da energia, o hardware do servidor é reiniciado, o software de rede carrega e inicializa e o SQL Server é reiniciado. À medida que o SQL Server é inicializado, ele executa automaticamente seu processo de recuperação com base nos dados no log de transações. Todo esse processo ocorre sem intervenção humana. Quando as estações de trabalho do cliente são reiniciadas, os usuários encontram todos os dados presentes até a última transação inserida.

A integridade transacional e a recuperação automática no SQL Server constituem um poderoso recurso de economia de tempo e trabalho. Se um controlador de cache de gravação não for projetado corretamente para uso em um ambiente DBMS transacional crítico de dados, ele poderá comprometer a capacidade do SQL Server de se recuperar, potencialmente corrompendo o banco de dados. Esse problema poderá ocorrer se o controlador interceptar as gravações do log de transações do SQL Server e os armazenar em buffer em um cache de hardware na placa do controlador, mas não preservar essas páginas escritas durante uma falha do sistema.

Riscos de cache de gravação em disco

A maioria dos controladores de cache de dispositivo de armazenamento executa cache de gravação. Nem sempre é possível desabilitar a função de cache de gravação.

Mesmo que o servidor use um UPS, o dispositivo não garante a segurança das gravações armazenadas em cache. Pode haver muitos tipos de falhas do sistema que um no-break não resolve. Por exemplo, um erro de paridade de memória, uma interceptação (trap) do sistema operacional (SO) ou uma falha de hardware que causa uma reinicialização do sistema podem produzir uma interrupção descontrolada do sistema. Uma falha de memória no cache de gravação de hardware também pode resultar na perda de informações vitais do log.

Outro possível problema relacionado a um controlador de cache de gravação pode ocorrer no desligamento do sistema. Não é incomum ciclo do sistema operacional ou reiniciar o sistema durante as alterações de configuração. Mesmo que um operador cuidadoso siga a recomendação do sistema operacional de aguardar até que toda a atividade de armazenamento cesse antes de reiniciar o sistema, ainda pode haver gravações armazenadas em cache no controlador. Quando a combinação de teclas Ctrl+Alt+Del é pressionada ou um botão de reinicialização no hardware é pressionado, as gravações armazenadas em cache podem ser descartadas, potencialmente danificando o banco de dados.

É possível criar um cache de gravação de hardware que leve em conta todas as possíveis causas do descarte de dados de cache sujos, o que o torna seguro para uso por um servidor de banco de dados. Alguns desses recursos de design incluem a interceptação do sinal de barramento RST (redefinição) para evitar a redefinição descontrolada do controlador de cache, backup de bateria integrado e memória espelhada ou memória com verificação e correção de erros (ECC). Verifique com o fornecedor do hardware para garantir que o cache de gravação inclua esses e quaisquer outros recursos necessários para evitar a perda de dados.

Usar caches de armazenamento com o SQL Server

Um sistema de banco de dados é, em primeiro lugar, responsável pelo armazenamento e recuperação precisos de dados, mesmo em caso de falhas inesperadas do sistema.

O sistema deve garantir a atomicidade e a durabilidade das transações, ao mesmo tempo em que contabiliza a execução atual, várias transações e vários pontos de falha. Essa propriedade geralmente é conhecida como as propriedades ACID (Atomicidade, Consistência, Isolamento e Durabilidade).

Esta seção aborda as implicações dos caches de armazenamento. Para obter mais informações sobre o cache e discussões de modo de falha alternativo, consulte os seguintes artigos:

• 86903 SQL Server e cache de controladores de disco
• Descrição dos algoritmos de log e armazenamento de dados que estendem a confiabilidade de dados no SQL Server

Examine também o seguinte conteúdo arquivado:

• Fundamentos de E/S do SQL Server 2000
• Fundamentos de E/S do SQL Server, Capítulo 2

Os conceitos nesses dois artigos permanecem amplamente aplicáveis às versões atuais do SQL Server.

Soluções de cache com bateria

Os sistemas avançados de controlador de cache desativam o cache em disco e oferecem uma solução funcional de cache com bateria. Esses caches podem manter os dados no cache por vários dias e até mesmo permitir que a placa de cache seja colocada em um segundo computador. Quando a energia é devidamente restaurada, os dados não gravados são completamente liberados antes que qualquer acesso adicional aos dados seja permitido. Muitos desses sistemas permitem estabelecer um percentual de cache de leitura versus gravação para um desempenho ideal. Alguns sistemas contêm grandes áreas de armazenamento de memória. Alguns fornecedores de hardware fornecem sofisticados sistemas de cache de unidade com bateria, com vários gigabytes de cache. Esses sistemas podem melhorar significativamente o desempenho do banco de dados. As soluções de cache com suporte a bateria fornecem a durabilidade e a consistência dos dados esperados pelo SQL Server.

Implementações de subsistemas de armazenamento

Os subsistemas de armazenamento existem em muitos tipos. Dois exemplos comuns são RAID (matriz redundante de discos independentes) e SAN (rede de área de armazenamento). Esses sistemas normalmente usam unidades baseadas em SCSI. A seção a seguir descreve considerações de armazenamento de alto nível.

SCSI, SAS e NVMe

Dispositivos de armazenamento SCSI, SAS e NVMe:

• Normalmente, são projetados para uso pesado.
• Normalmente são direcionados a implementações multiusuário, baseadas em servidor.
• Normalmente, tem melhor tempo médio para taxas de falha do que outras implementações.
• Contêm heurísticas sofisticadas para ajudar a prever falhas iminentes.

Não-SCSI

Outras implementações de unidade, como IDE, ATA e SATA:

• Normalmente, são projetados para uso leve a médio.
• Normalmente, são direcionados a aplicativos de usuário único.

Controladores baseados em área de trabalho não SCSI exigem mais capacidade de processamento da CPU (processador principal) e são frequentemente limitados por um único comando ativo. Por exemplo, quando uma unidade não-SCSI está ajustando um bloco defeituoso, a unidade requer que os comandos do host aguardem. O barramento ATA apresenta outro exemplo: o barramento ATA suporta dois dispositivos, mas apenas um único comando pode estar ativo. Essa limitação deixa um disco ocioso enquanto o outro disco atende ao comando pendente. Os sistemas RAID baseados em tecnologias de desktop podem experimentar esses sintomas e ser muito afetados pelo respondedor mais lento. A menos que esses sistemas usem projetos avançados, seu desempenho não é tão eficiente quanto o desempenho de sistemas baseados em SCSI.

Considerações de armazenamento

Uma unidade ou matriz baseada em desktop pode ser uma solução de baixo custo para algumas situações. Por exemplo, se você configurar um banco de dados somente leitura para relatórios, não encontrará muitos dos fatores de desempenho de um banco de dados OLTP quando o cache de disco estiver desabilitado.

Os tamanhos dos dispositivos de armazenamento continuam a aumentar. Unidades de alta capacidade e baixo custo podem ser atraentes. Mas ao configurar a unidade para o SQL Server e suas necessidades de tempo de resposta operacional, considere cuidadosamente as seguintes questões:

• Projeto do caminho de acesso
• O requisito para desativar o cache em disco

Discos rígidos mecânicos

Unidades mecânicas usam pratos magnéticos giratórios para armazenar dados. Eles estão disponíveis em várias capacidades e fatores de forma, como IDE, SATA, SCSI e SAS (Serial Attached SCSI). Algumas unidades SATA incluem constructos de previsão de falha. As unidades SCSI são projetadas para ciclos de trabalho mais pesados e menores taxas de falha.

Os sistemas baseados em IDE e ATA podem adiar comandos de host quando executam atividades como ajuste de bloco defeituoso, levando a períodos de atividade de E/S paralisada.

Os benefícios da SAS incluem fila avançada de até 256 níveis, prioridade na fila e fila fora de ordem. O backplane SAS é projetado de uma forma que permite o uso de drives SAS e SATA dentro do mesmo sistema.

A instalação do SQL Server depende da capacidade do controlador de desabilitar o cache em disco e fornecer um cache de E/S estável. Gravar dados fora de ordem em várias unidades não é um obstáculo para o SQL Server, desde que o controlador forneça os recursos corretos de cache de mídia estável. A complexidade do projeto do controlador aumenta com técnicas avançadas de segurança de dados, como espelhamento.

Armazenamento de estado sólido

O armazenamento de estado sólido tem vantagens sobre discos rígidos mecânicos (girando), mas é suscetível a muitos dos mesmos padrões de falha que a mídia giratória. Você pode conectar o armazenamento de estado sólido ao servidor usando várias interfaces, incluindo NVM Express (NVMe), PCI Express (PCIe) e SATA. Trate a mídia de estado sólido como faria com a mídia giratória e certifique-se de que as proteções apropriadas estejam em vigor para falta de energia, como um controlador de cache com bateria.

Problemas comuns causados por uma falha de energia incluem:

• Corrupção de bits: os registros mostram erros aleatórios de bits.
• Gravações fora do lugar: registros bem formados acabam no lugar errado.
• Gravações cortadas: as operações são parcialmente feitas em um nível abaixo do tamanho esperado do setor.
• Corrupção de metadados: os metadados na camada de tradução flash (FTL) estão corrompidos.
• Dispositivo sem resposta: o dispositivo não funciona de jeito nenhum ou, na maioria das vezes, não funciona.
• Desserialização: o estado final do armazenamento não reflete uma ordem de operação serializável.

512e

A maioria dos dispositivos de armazenamento de estado sólido relata tamanhos de setor de 512 bytes, mas usam páginas de 4 KB dentro dos blocos de apagamento de 1 MB. O uso de setores alinhados em 512 bytes para o dispositivo de log do SQL Server pode gerar mais atividades de leitura/modificação/gravação (RMW), o que pode contribuir para um desempenho mais lento e desgaste da unidade.

Recomendação: verifique se o controlador de cache está ciente do tamanho correto da página do dispositivo de armazenamento e pode alinhar corretamente as gravações físicas com a infraestrutura de armazenamento de estado sólido.

0xFFFFFFFF

Uma unidade recém-formatada geralmente contém somente zeros. Um bloco apagado de um dispositivo de estado sólido é preenchido com 1s, fazendo com que uma leitura bruta de um bloco apagado seja composta inteiramente por caracteres 0xFF. No entanto, é incomum que um usuário leia um bloco apagado durante operações normais de E/S.

Estampagem de padrões

Uma técnica usada antigamente é gravar um padrão conhecido em todo o disco. Em seguida, à medida que a atividade do banco de dados é executada na mesma unidade, o comportamento incorreto (leitura obsoleta, gravação perdida ou leitura de deslocamento incorreto) pode ser detectado quando o padrão aparece inesperadamente.

Essa técnica não funciona bem no armazenamento de estado sólido. As atividades de apagamento e RMW para gravações destroem o padrão. A atividade de coleta de lixo no armazenamento de estado sólido (GC), o nivelamento de desgaste, os blocos de listas proporcionais/reservados e outras otimizações tendem a fazer com que as gravações sejam feitas em locais físicos diferentes, ao contrário da reutilização de setores da mídia de disco giratório.

Microprograma

O firmware usado no armazenamento de estado sólido tende a ser complexo quando comparado com as contrapartes de mídia giratória. Muitas unidades usam vários núcleos de processamento para lidar com solicitações de entrada e atividades de coleta de lixo. Certifique-se de manter o firmware do dispositivo de estado sólido atualizado para evitar problemas conhecidos.

Danos pela leitura de dados e nivelamento do desgaste

Uma abordagem comum de coleta de lixo (GC) para armazenamento de estado sólido ajuda a evitar danos pela leitura repetida dos dados. Ao ler a mesma célula repetidamente, é possível que a atividade dos elétrons possa vazar e causar danos às células vizinhas. O armazenamento de estado sólido protege os dados com vários níveis de código de correção de erros (ECC) e outros mecanismos.

Um desses mecanismos diz respeito ao nivelamento do desgaste. O armazenamento de estado sólido controla a atividade de leitura e gravação no dispositivo de armazenamento. A coleta de lixo pode determinar pontos de acesso frequente ou locais de desgaste mais rápido do que outros locais. Por exemplo, o GC determina que um bloco está num estado de somente leitura e precisa ser movido. Este movimento é geralmente para um bloco com mais desgaste, de modo que o bloco original possa ser usado para gravações. Esse processo ajuda a equilibrar o desgaste na unidade, mas move dados de leitura para um local que tem maior desgaste e, matematicamente, aumenta ligeiramente as chances de falha.

Outro efeito colateral do nivelamento de desgaste pode ocorrer com o SQL Server. Suponha que você execute DBCC CHECKDB e ele relate um erro. Se você executá-lo uma segunda vez, há uma pequena chance de que DBCC CHECKDB informe erros adicionais ou um padrão diferente de erros, porque a atividade de GC do armazenamento de estado sólido pode fazer alterações entre as execuções de DBCC CHECKDB.

Erro 665 do sistema operacional e desfragmentação

A mídia giratória precisa manter os blocos próximos uns dos outros para reduzir o movimento da cabeça da unidade e aumentar o desempenho. O armazenamento de estado sólido não tem uma cabeça física, o que elimina o tempo de busca. Muitos dispositivos de estado sólido são projetados para permitir operações paralelas em blocos diferentes. A desfragmentação da mídia de estado sólido é, portanto, desnecessária. As atividades seriais são os melhores padrões de E/S para maximizar a produtividade de E/S em dispositivos de armazenamento de estado sólido.

Recomendações:

• Use um controlador com bateria apropriado, projetado para otimizar as atividades de gravação. Essa opção pode melhorar o desempenho, diminuir o desgaste do disco e reduzir os níveis de fragmentação física.

• Considere usar o sistema de arquivos ReFS para evitar as limitações de atributo do NTFS.

• Verifique se as configurações de crescimento de arquivo são apropriadas.

Para obter mais informações sobre como solucionar o erro 665 do sistema operacional relacionado à fragmentação, consulte Erros 665 e 1450 do sistema operacional são relatados para arquivos do SQL Server.

Compactação

Desde que a unidade mantenha a intenção de mídia estável, a compactação pode estender a vida útil da unidade e pode afetar positivamente o desempenho. No entanto, alguns firmwares já podem compactar dados invisivelmente. Lembre-se de testar novos cenários de armazenamento antes de implantá-los em seu ambiente de produção.

Resumo

• Mantenha procedimentos e processos adequados de backup e recuperação de desastres.
• Mantenha o firmware do dispositivo atualizado.
• Ouça atentamente as diretrizes do fabricante de hardware.

Configuração do cache da unidade

Para usar uma unidade com o SQL Server, desabilite o cache da unidade. O cache da unidade está habilitado por padrão. No Windows Server, use a guia Propriedades do Disco>Hardware>Política para desabilitar o cache de gravação no nível do sistema operacional.

Não confie apenas nas configurações no nível do sistema operacional. Algumas unidades ignoram as configurações do Windows e exigem utilitários fornecidos pelo fabricante ou configurações de firmware para desabilitar o cache de gravação. Confirme por meio das ferramentas do fornecedor que o cache de gravação está realmente desabilitado.

Considerações sobre cache e SQLIOSim

Para confirmar as garantias de durabilidade transacional, valide seu subsistema de E/S usando SQLIOSim antes de migrar para a produção. Esse utilitário simula atividade de leitura e gravação assíncrona pesada em um dispositivo de dados simulado e dispositivo de log. Para obter mais informações, consulte Usar o utilitário SQLIOSim para simular a atividade do SQL Server em um subsistema de disco.

Muitos fabricantes de PCs encomendam as unidades com o cache de gravação desativado. No entanto, o teste mostra que essa condição pode nem sempre ser o caso, portanto, você deve sempre testá-la completamente. Se você tiver dúvidas sobre o status de cache do dispositivo de armazenamento, entre em contato com o fabricante e obtenha o utilitário apropriado para desabilitar operações de cache de gravação. Na mídia de armazenamento mais antiga, talvez você também precise de configurações de jumper.

O SQL Server requer que os sistemas ofereçam suporte à entrega garantida para mídia estável, conforme descrito nos Requisitos 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 (E/S) de disco do mecanismo de banco de dados do SQL Server.

Riscos de cache de gravação inadequados

Quando você habilita o cache de gravação sem as proteções adequadas, alguns subsistemas de armazenamento reconhecem as operações de gravação como concluídas antes que os dados sejam gravados com segurança na mídia durável. Se ocorrer uma perda de energia ou falha no sistema, essa condição poderá resultar em:

• Perda de dados, em que as transações confirmadas nunca são persistentes.
• Corrupção de banco de dados devido a garantias de ordem de gravação desfeitas.

Conteúdo relacionado

• Guia de arquitetura de página e extensões
• Guia de arquitetura de gerenciamento de memória
• Armazenamento: melhores práticas de desempenho do SQL Server nas VMs do Azure
• SQL Server no Linux: Detalhes internos do Acesso Unitário Forçado (FUA)
• Fundamentos de E/S do SQL Server, Capítulo 2