Fundamentos de E/S do SQL Server

Aplica-se a:SQL ServerAzure SQL Managed InstanceSQL Server on Azure Virtual Machines

O objetivo principal de um banco de dados do SQL Server é armazenar e recuperar dados, portanto, a entrada/saída de disco (E/S) intensiva é uma característica central 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 serem concluídas, o SQL Server se concentra em tornar a E/S altamente eficiente.

Os subsistemas de armazenamento para SQL Server são fornecidos em múltiplos formatos, incluindo drives mecânicos e armazenamento de estado sólido. Este artigo fornece detalhes sobre como utilizar os princípios de caching de unidade de disco para melhorar a E/S do Mecanismo de Base de Dados.

O SQL Server requer que os sistemas ofereçam suporte garantido à 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 de disco (E/S) do Mecanismo de Banco de Dados do SQL Server.

E/S de disco

O gerenciador de buffer só executa 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 do gerenciador 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 que o thread de chamada continue o processamento enquanto a operação de E/S ocorre em segundo plano. Em certas circunstâncias (por exemplo, I/O de log desalinhado), podem ocorrer E/S síncronas.

• Todas as E/S são emitidas nos threads de chamada, a menos que a opção de E/S de afinidade esteja em uso. A opção de máscara de E/S de afinidade vincula a E/S de disco do SQL Server a um subconjunto especificado de CPUs. Em ambientes OLTP (processamento transacional online) de alto padrão do SQL Server, esta extensão pode melhorar o desempenho dos processos do SQL Server emitindo E/S.

• As operações de E/S de várias páginas são realizadas com E/S scatter-gather, o que permite que os dados sejam transferidos para dentro ou para fora de áreas não contíguas da memória. Isso significa que o SQL Server pode preencher ou liberar rapidamente o cache do buffer, evitando várias solicitações de E/S físicas.

Solicitações de E/S longas

O gerenciador de buffer informa sobre qualquer solicitação de E/S pendente por pelo menos 15 segundos. Este processo ajuda o administrador do sistema a distinguir entre problemas do SQL Server e problemas de subsistemas de I/O. A mensagem de erro MSSQLSERVER_833 é relatada e aparece no log de erros do SQL Server da seguinte maneira:

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 Entrada/Saída longa pode ser uma leitura ou uma escrita; a mensagem neste momento não indica qual. As mensagens de E/S longas são avisos, não erros. Eles 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 mais rapidamente a causa dos tempos de resposta fracos do SQL Server e a distinguir problemas que estão fora do controlo 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 tempo e se o tempo é justificável.

Causas de solicitações de E/S longas

Uma mensagem longa de I/O pode indicar que uma E/S está permanentemente bloqueada e nunca será concluída (conhecida como I/O perdida), ou simplesmente que ainda não está concluída. Não é possível identificar pela mensagem qual é o cenário, embora uma falha de I/O muitas vezes conduza a um tempo limite de bloqueio.

Operaçõoes de entrada/saída longas frequentemente indicam uma carga de trabalho no SQL Server demasiado intensa para o subsistema de disco. Um subsistema de disco inadequado pode ser indicado quando:

• Várias mensagens de E/S longas aparecem no log de erros durante uma carga de trabalho pesada do SQL Server.
• Os contadores do Monitor de Desempenho mostram latências de disco longas, longas filas de disco ou nenhum tempo ocioso de disco.

Um componente no caminho de I/O (por exemplo, um driver, controlador ou firmware) pode causar longos I/Os ao adiar continuamente a manutenção de um pedido antigo de I/O, em favor de atender pedidos mais recentes. Este problema pode ocorrer em ambientes interligados, como redes iSCSI e Fibre Channel (seja devido a uma configuração incorreta ou falha de caminho). A ferramenta Performance Monitor pode dificultar a confirmação deste problema porque a maioria das E/S está a ser atendida rapidamente. Cargas de trabalho que realizam grandes quantidades de I/O sequencial, como backup e restauro, digitalizações de tabelas, ordenação, criação de índices, cargas em massa e anulação de ficheiros, podem agravar pedidos longos de I/O.

I/Os longas isoladas que não parecem relacionadas com nenhuma das 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 ineficientes ou controladores de filtro

A entrada/saída lenta pode ser causada por consultas que não são escritas eficientemente ou não otimizadas 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 verificam arquivos de banco de dados. Esse software de varredura pode se estender para a camada de rede, que adiciona latência de rede, afetando indiretamente a latência do banco de dados. Embora o cenário descrito de 15 segundos de E/S seja mais comum em componentes de hardware, atrasos de E/S mais curtos são mais frequentemente observados com consultas não otimizadas ou antivírus mal configurados.

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

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

Cache de escrita em controladores de armazenamento

Transferências de I/O que não usam cache podem demorar muito mais tempo em discos mecânicos devido às taxas de rotação do disco rígido, ao tempo mecânico necessário para mover as cabeças dos discos e outros fatores limitantes. As instalações do SQL Server visam sistemas 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 à busca de armazenamento e tempos de gravação usando as várias otimizações do controlador de cache.

O uso de um controlador de armazenamento de cache de gravação (também chamado de cache write-back) pode melhorar o desempenho do SQL Server. Controladores de cache de escrita e subsistemas de armazenamento são seguros para SQL Server, se forem concebidos para utilização num ambiente de gestão de bases de dados transacionais (SGBD) crítico para dados. Esses recursos de design devem preservar os dados armazenados em cache se ocorrer uma falha do sistema. A utilização de uma fonte de alimentação externa ininterrupta (UPS) para garantir esta proteção geralmente não é suficiente, pois podem ocorrer modos de falha não relacionados com a energia.

Controladores de cache e subsistemas de armazenamento podem ser seguros para uso pelo SQL Server. A maioria das novas plataformas de servidores construídas de raiz que incorporam estes controladores são seguras. No entanto, deve verificar com o seu fornecedor de hardware para garantir que o subsistema de armazenamento foi testado e aprovado para utilização num ambiente de gestão de bases de dados relacional transacional (RDBMS) crítico para dados.

Diretrizes de segurança de subsistemas de cache

Controladores de cache de write-back podem melhorar o desempenho se cumprirem requisitos específicos de segurança:

• Inclui cache com bateria ou memória não volátil, como NVDIMM ou flash super-capacitor.
• Ser reconhecido pelo fornecedor como adequado para ambientes de bases de dados OLTP com importância crítica para os dados.
• Forneça proteção que cubra todas as condições de avaria, não apenas a perda de energia.

Registo de escrita antecipada

As instruções de modificação de dados do SQL Server geram gravações lógicas de página. Pode imaginar este fluxo de escritas a ir para dois locais: o registo e a própria base de dados. Por razões de desempenho, o SQL Server adia as escritas para a base de dados através do seu próprio sistema de cache buffer. O sistema só adia momentaneamente as escritas para o log até COMMIT. Não armazena em cache essas escritas da mesma forma que as escritas de dados. Como as escritas de registo para uma dada página ocorrem sempre antes das escritas de dados da página, o registo é por vezes referido como registo de escrita antecipada (WAL).

Protocolo WAL (write-ahead logging)

O termo protocolo é uma excelente maneira de descrever a WAL. A WAL utilizada pelo SQL Server é conhecida como ARIES (Algorithm for Recovery and Isolation Exploiting Semantics). Para obter mais informações, consulte 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 forma consistente e protegida, a WAL também descreve o protocolo para proteger dados. Todas as versões do SQL Server abrem os arquivos de log e dados usando a função Win32 CreateFile . O dwFlagsAndAttributes membro 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 FILE_FLAG_WRITE_THROUGH sinalizador. Esta opção instrui o sistema a escrever através de qualquer cache intermediário e ir diretamente para o armazenamento. O sistema ainda pode armazenar em cache as operações de escrita, mas não pode esvaziá-las de forma tardia. Para obter mais informações, consulte CreateFileA.

A FILE_FLAG_WRITE_THROUGH opção garante que, quando uma operação de escrita retorna à conclusão bem-sucedida, os dados são corretamente armazenados em armazenamento estável. Esta funcionalidade está alinhada 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 dependem de um capacitor e não de uma solução alimentada por 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 crescer em tamanho, os caches tornam-se maiores e podem expor maiores quantidades de dados durante uma falha.

Para obter mais informações sobre o suporte de FUA pela distribuição Linux e o seu efeito no SQL Server, consulte SQL Server On Linux: Forced Unit Access (FUA) Internals.

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 de transações write-ahead 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. Vários efeitos não ideais, no mundo real, 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 dirigido 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 um aplicativo 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 reinicia, o software de rede carrega-se e inicializa-se, e o SQL Server reinicia-se. À medida que o SQL Server é inicializado, ele executa automaticamente seu processo de recuperação com base nos dados no log de transações. Todo este processo ocorre sem intervenção humana. Quando as estações de trabalho cliente reiniciam, os utilizadores encontram todos os seus dados presentes, até à última transação que introduziram.

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 escrita inadequadamente concebido for usado num ambiente de SGBD transacional onde os dados são críticos, pode comprometer a capacidade de recuperação do SQL Server e potencialmente corromper a base de dados. Este problema pode ocorrer se o controlador intercetar os registos de transações escritas do SQL Server e os armazenar numa cache de hardware na placa controladora, mas não preservar essas páginas escritas durante uma falha do sistema.

Riscos de cache de escrita em disco

A maioria dos controladores de cache para dispositivos de armazenamento realizam cache de escrita. Nem sempre podes desativar a função de cache de escrita.

Mesmo que o servidor use um UPS, o dispositivo não garante a segurança das escritas em cache. Podem ocorrer muitos tipos de falhas do sistema que uma UPS não resolve. Por exemplo, um erro de paridade de memória, um 'trap' do sistema operativo (SO) ou uma falha de hardware que leva a uma reinicialização do sistema podem causar uma interrupção incontrolada do sistema. Uma falha de memória no cache de escrita do hardware também pode resultar na perda de informações vitais do log.

Outro problema potencial relacionado com um controlador de cache de escrita pode surgir durante o encerramento do sistema. Não é incomum alternar o sistema operativo ou reiniciar o sistema durante alterações de configuração. Mesmo que um operador cuidadoso siga a recomendação do sistema operacional de esperar até que toda a atividade de armazenamento cesse antes de reiniciar o sistema, as gravações em cache ainda podem estar presentes no controlador. Quando a combinação de Ctrl+Alt+Del teclas é pressionada ou um botão de redefinição de hardware é pressionado, as gravações em cache podem ser descartadas, potencialmente danificando o banco de dados.

É possível desenhar um cache de escrita por hardware que tenha em conta todas as possíveis causas de descartar dados de cache sujos, tornando-o seguro para uso por um servidor de base de dados. Algumas destas funcionalidades de design incluem a interceção do sinal do barramento RST (reset) para evitar um reset descontrolado do controlador de cache, backup de bateria a bordo e memória espelhada ou de verificação e correção de erros (ECC). Verifique com seu fornecedor de 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 e acima de tudo, responsável pelo armazenamento e recuperação precisos de dados, mesmo no caso de falhas inesperadas do sistema.

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

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

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

Consulte também o seguinte conteúdo arquivado:

• Noções básicas de E/S do SQL Server 2000
• Noções básicas de E/S do SQL Server Capítulo 2

Os conceitos destes dois artigos continuam a ser amplamente aplicáveis às versões atuais do SQL Server.

Soluções de cache alimentadas por bateria

Os sistemas de controladores de cache aprimorados desabilitam o cache em disco e fornecem uma solução funcional de cache com bateria reserva. Esses caches podem manter os dados no cache por vários dias e até mesmo permitir que o cartão de cache seja colocado em um segundo computador. Quando a alimentação é restaurada corretamente, os dados não gravados são completamente descartados antes que qualquer acesso adicional aos dados seja permitido. Muitos destes sistemas permitem estabelecer uma percentagem do cache de leitura versus escrita para um desempenho ótimo. Alguns sistemas contêm grandes áreas de armazenamento de memória. Alguns fornecedores de hardware fornecem sistemas de cache com suporte de bateria de alto desempenho e vários gigabytes de cache. Estes sistemas podem melhorar significativamente o desempenho da base de dados. As soluções de cache apoiadas por bateria proporcionam a durabilidade e consistência dos dados que o SQL Server espera.

Implementações do subsistema de armazenamento

Os subsistemas de armazenamento existem em muitos tipos. Dois exemplos comuns são RAID (array redundante de discos independentes) e SAN (storage area network). Estes sistemas utilizam tipicamente discos baseados em SCSI. A secção seguinte descreve considerações de armazenamento de alto nível.

SCSI, SAS e NVMe

Dispositivos de armazenamento SCSI, SAS e NVMe:

• São normalmente concebidos para uso intensivo.
• Normalmente são direcionados a implementações multiusuário baseadas em servidor.
• Normalmente têm melhores taxas médias de tempo até falha do que outras implementações.
• Contenha heurísticas sofisticadas para ajudar a prever falhas iminentes.

Não SCSI

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

• São normalmente concebidos para uso leve a médio.
• São normalmente direcionados para aplicações de utilizador único.

Controladores baseados em desktop não SCSI requerem mais largura de banda do processador principal (CPU) 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. Esta limitação deixa um disco ocioso enquanto o outro serve o comando pendente. Os sistemas RAID baseados em tecnologias de desktop podem experimentar esses sintomas e ser muito afetados pela resposta mais lenta. 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 sobre armazenamento

Um disco ou array baseado em desktop pode ser uma solução de baixo custo para algumas situações. Por exemplo, se configurar uma base de dados só de leitura para relatórios, não encontrará muitos dos fatores de desempenho de uma base de dados OLTP quando a cache do disco está desativada.

Os tamanhos dos dispositivos de armazenamento continuam a aumentar. Discos de baixo custo e alta capacidade podem ser apelativos. Mas ao configurar o disco para SQL Server e as necessidades de tempo de resposta do seu negócio, considere cuidadosamente as seguintes questões:

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

Discos rígidos mecânicos

As unidades mecânicas utilizam discos magnéticos rotativos para armazenar dados. Estão disponíveis em várias capacidades e formatos, como IDE, SATA, SCSI e Serial Attached SCSI (SAS). Algumas unidades SATA incluem construções de previsão de falhas. As unidades SCSI são projetadas para ciclos de trabalho mais pesados e taxas de falha reduzidas.

Sistemas baseados em IDE e ATA podem adiar comandos do host quando realizam atividades como mau ajuste de blocos, levando a períodos de interrupção da atividade de I/O.

Os benefícios do SAS incluem filas avançadas até 256 níveis, gestão de fila principal e fila fora de ordem. O backplane SAS é projetado de forma a permitir 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 de cache de mídia estáveis corretos. A complexidade do projeto do controlador aumenta com técnicas avançadas de segurança de dados, como espelhamento.

Armazenamento em estado sólido

O armazenamento de estado sólido tem vantagens em relação aos discos rígidos mecânicos (giradouros), mas é suscetível a muitos dos mesmos padrões de falha dos suportes giratórios. Pode ligar armazenamento de estado sólido ao seu servidor através de 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 falhas de energia, como um controlador de cache alimentado por bateria.

Problemas comuns causados por uma falha de alimentação incluem:

• Corrupção de bits: Os registos mostram erros aleatórios de bits.
• Flying escreve: Discos bem formados acabam no lugar errado.
• Shorn escreve: 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 ou, na maioria das vezes, não funciona.
• Unserializability: O estado final de armazenamento não resulta de uma ordem de operação serializável.

512e

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

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

0xFFFFFFFF

Uma unidade recém-formatada geralmente contém todos os zeros. Um bloco apagado de um dispositivo de estado sólido consiste completamente em 1, resultando em todos os caracteres 0xFF numa leitura bruta de um bloco apagado. No entanto, é incomum para um usuário ler um bloco apagado durante operações normais de E/S.

Estampagem de padrões

Uma técnica usada no passado consistia em escrever um padrão conhecido para todo o disco. Depois, à medida que a atividade da base de dados é executada nesse mesmo disco, um comportamento incorreto (leitura obsoleta, escrita perdida ou leitura de offset incorreto) pode ser detetado quando o padrão aparece inesperadamente.

Esta técnica não funciona bem no armazenamento de estado sólido. As atividades de apagamento e Read-Modify-Write (RMW) para operações de escrita destroem o padrão. A atividade de recolha de lixo (GC) do armazenamento em estado sólido, nivelamento de desgaste, blocos de lista com distribuição proporcional/dedicados e outras otimizações tendem a fazer com que as escritas adquiram diferentes localizações físicas, ao contrário do reaproveitamento de setores nos media giratórios.

firmware

O firmware usado no armazenamento de estado sólido tende a ser complexo quando comparado com os equivalentes de mídia giratória. Muitas unidades de disco 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.

Leia os dados sobre danos e nivelamento de desgaste

Uma abordagem comum de coleta de lixo (GC) para armazenamento em estado sólido ajuda a evitar danos repetidos em dados de leitura. Ao ler a mesma célula repetidamente, é possível que a atividade eletrônica 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 recolha de lixo pode determinar zonas críticas ou locais que se desgastam mais rapidamente do que outros locais. Por exemplo, o GC determina que um bloco está num estado de leitura única e tem de ser movido. Este movimento é geralmente para um bloco que já está mais degradado, de modo que o bloco original pode ser usado para registos. Este processo ajuda a equilibrar o desgaste do disco, mas move os dados de apenas leitura para um local com maior desgaste e aumenta matematicamente as probabilidades de falha, mesmo que ligeiramente.

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

Erro 665 do SO 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 procura. Muitos dispositivos de estado sólido são concebidos para permitir operações paralelas em diferentes blocos. A desfragmentação dos suportes de estado sólido é, portanto, desnecessária. As atividades seriais são os melhores padrões de E/S para maximizar a taxa de transferência de E/S em dispositivos de armazenamento de estado sólido.

Recomendações:

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

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

• Certifique-se de que as definições de crescimento de ficheiros são adequadas.

Para mais informações sobre a resolução do erro 665 do sistema operacional relacionado à fragmentação, consulte Os erros 665 e 1450 do sistema operacional são relatados para arquivos do SQL Server.

Compressão

Desde que o disco mantenha a função de suporte estável, a compressão pode prolongar a vida útil do disco e pode ter um impacto positivo no desempenho. No entanto, alguns firmware podem já comprimir dados de forma invisível. Lembre-se de testar novos cenários de armazenamento antes de os implementar no seu ambiente de produção.

Resumo

• Mantenha procedimentos e processos adequados de backup e recuperação de desastres.
• Mantenha o firmware atualizado.
• Ouça atentamente as orientações do fabricante do seu hardware.

Configuração da cache do disco

Para usar um disco com SQL Server, desative o cache do disco. Por padrão, o cache da unidade está habilitado. No Windows Server, use o separador Propriedades do Disco>Política de Hardware> para desativar a cache de escrita ao nível do sistema operativo.

Não confies apenas nas definições ao nível do sistema operativo. Alguns discos ignoram as definições do Windows e exigem utilitários fornecidos pelo fabricante ou definições de firmware para desativar a cache de escrita. Confirme através de ferramentas do fornecedor que o cache de escrita está realmente desativado.

Considerações sobre cache e SQLIOSim

Para confirmar garantias de durabilidade transacional, valide o seu subsistema de E/S usando SQLIOSim antes de passar para produção. Esta ferramenta simula uma intensa atividade assíncrona de leitura e escrita num dispositivo de dados simulado e num dispositivo de registo. Para mais informações, consulte Usar a utilidade SQLIOSim para simular atividade do SQL Server num subsistema de disco.

Muitos fabricantes de PC encomendam as unidades com o cache de gravação desativado. No entanto, os testes mostram que esta condição pode nem sempre ser o caso, por isso deve sempre testá-la completamente. Se tiver alguma dúvida sobre o estado da cache do seu dispositivo de armazenamento, contacte o fabricante e obtenha a ferramenta adequada para desativar as operações de cache de escrita. Em suportes de armazenamento mais antigos, também podes precisar de definiçõ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 de disco (E/S) do Mecanismo de Banco de Dados do SQL Server.

Riscos associados à cache de escrita inadequada

Quando ativa a cache de escrita sem as devidas salvaguardas, alguns subsistemas de armazenamento reconhecem as operações de escrita como completas antes de os dados serem gravados em segurança para suportes duráveis. Se ocorrer uma perda de energia ou falha do sistema, esta condição pode resultar em:

• Perda de dados, onde as transações comprometidas nunca são mantidas.
• Corrupção da base de dados devido à quebra das garantias de ordem de escrita.

Conteúdo relacionado

• Guia de arquitetura de páginas e extensões
• Guia de arquitetura de gerenciamento de memória
• Armazenamento: práticas recomendadas de desempenho para o SQL Server em VMs do Azure
• SQL Server no Linux: Os Internos do Acesso Forçado à Unidade (FUA)
• Noções básicas de E/S do SQL Server, Capítulo 2