Requisitos de subsistema de E/S do Microsoft SQL Server para o banco de dados tempdb

Este artigo apresenta os requisitos de subsistema de E/S para o banco de dados tempdb no SQL Server.

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

Resumo

O Microsoft SQL Server exige que o subsistema de E/S usado para armazenar bancos de dados de sistema e usuário honre totalmente Write-Ahead requisitos de WAL (registro em log) por meio de entidades de E/S específicas. Esses requisitos são necessários para honrar as propriedades ACID das transações: Atômica, Consistente, Isolada e Durável. Os detalhes sobre os requisitos de conformidade do subsistema de E/S são fornecidos nas seguintes referências:

SQL Server 2000 noções básicas de E/Shttps://technet.microsoft.com/library/cc966500.aspx

A lista a seguir é um resumo rápido dos requisitos:

• A ordenação de gravação deve ser mantida.
• A consistência de gravação dependente deve ser mantida.
• As gravações sempre devem ser protegidas em/em mídia estável.
• A prevenção de E/S rasgada deve ocorrer.

A manutenção da durabilidade permanece fundamental para todos os outros bancos de dados, mas pode ser relaxada para o banco de dados tempdb. A tabela a seguir resume vários dos requisitos críticos de E/S para bancos de dados de SQL Server.

As reescritas do setor transacional envolvem operações totalmente registradas pelo subsistema permitindo que um setor seja totalmente movido, substituído ou revertido para a imagem original. Essas reescritas normalmente são desencorajadas devido à sobrecarga adicional necessária para executar tais ações. Um exemplo disso seria um defragmentation utilitário que está movendo os dados do arquivo. O setor original no arquivo não pode ser substituído pela nova localização do setor até que o novo setor e os dados sejam protegidos. O remapping do setor deve ocorrer de forma transacional para que qualquer falha, incluindo uma falha de energia, cause o restabilização dos dados originais. Verifique se você tem mecanismos de bloqueio disponíveis durante esse tipo de processo para impedir o acesso inválido de dados, mantendo assim os outros locatários do SQL Server E/S.

Sobrevivência em toda a interrupção

O banco de dados tempdb é uma área de risco para SQL Server e é reconstruído em todas as SQL Server inicialização. A inicialização substitui qualquer necessidade de que os dados sobrevivam a uma reinicialização.

Operações de reescrita do setor transacional

Para garantir o sucesso dos processos de recuperação, como reversão e recuperação de falhas, os registros de log devem ser armazenados corretamente em mídia estável antes que a página de dados seja armazenada e não possa ser reescrita sem honrar as propriedades transacionais. Isso requer que o subsistema e o SQL Server mantenham atributos específicos, como ordenação de gravação, gravações alinhadas e dimensionadas do setor e outros atributos de segurança de E/S descritos nos documentos mencionados anteriormente. Para o banco de dados tempdb, a recuperação de falha é desnecessária porque o banco de dados é sempre inicializado durante SQL Server inicialização. No entanto, o banco de dados tempdb ainda requer recursos de reversão. Portanto, alguns atributos do protocolo WAL podem ser relaxados.

O local de armazenamento do banco de dados tempdb deve agir de acordo com protocolos de unidade de disco estabelecidos. De todas as maneiras, o dispositivo no qual o banco de dados tempdb é armazenado deve aparecer e atuar como um disco físico fornecendo recursos de leitura após gravação. As operações de reescrita do setor de transações podem ser um requisito adicional de implementações específicas. Por exemplo, SQL Server não dá suporte a modificações de banco de dados usando a compactação do sistema de arquivos NTFS porque a compactação NTFS pode reescrever setores do log que já foram gravados e considerados endurecidos. Uma falha durante esse tipo de reescrita pode fazer com que o banco de dados seja inutilizável, prejudicando dados que já SQL Server considerados seguros.

As operações de reescrita do setor transacional são pertinentes a todos os bancos de dados SQL Server que incluem o banco de dados tempdb. Uma variedade crescente de tecnologias de armazenamento estendido usa dispositivos e utilitários que podem reescrever dados que SQL Server consideram seguros. Por exemplo, algumas das tecnologias emergentes executam cache na memória ou compactação de dados. Para evitar danos graves ao banco de dados, qualquer reescrita do setor deve ter suporte transacional completo de forma que, se ocorrer uma falha, os dados sejam revertidos para as imagens anteriores do setor. Isso garante que SQL Server nunca seja exposta a uma interrupção inesperada ou a uma condição de dano de dados.

Você pode ser capaz de colocar o banco de dados tempdb em subsistemas especializados, como discos de RAM, estado sólido ou outras implementações de alta velocidade que não podem ser usadas para outros bancos de dados. No entanto, os principais fatores apresentados na seção Mais Informações devem ser considerados quando você avalia essas opções.

Mais informações

Vários fatores devem ser cuidadosamente estudados quando você avalia o local de armazenamento do banco de dados tempdb. Por exemplo, o uso do banco de dados tempdb envolve, mas não se limita a, pegada de memória, plano de consulta e decisões de E/S. O ajuste e a implementação apropriados do banco de dados tempdb podem melhorar a escalabilidade e a capacidade de resposta de um sistema. Esta seção discute os principais fatores para determinar as necessidades de armazenamento para o banco de dados tempdb.

Subsistemas de alta velocidade

Há várias implementações de subsistema de alta velocidade disponíveis no mercado que fornecem SQL Server requisitos de protocolo de subsistema de E/S, mas que não fornecem durabilidade da mídia.

Um disco de RAM é um exemplo comum dessa implementação. Os discos de RAM instalam os drivers necessários e permitem que parte do disco de RAM main apareça como e funcione como qualquer unidade de disco anexada ao sistema. Todos os subsistemas de E/S devem fornecer conformidade total com os requisitos de E/S SQL Server. No entanto, é óbvio que um disco de RAM não é uma mídia durável. Portanto, uma implementação como um disco de RAM só pode ser usada como o local do banco de dados tempdb e não pode ser usada para nenhum outro banco de dados.

Chaves a serem consideradas antes da implementação e implantação

Há vários pontos a serem considerados antes da implantação do banco de dados tempdb nesse tipo de subsistema. Esta seção usa um disco de RAM como base para discussão, mas resultados semelhantes ocorrem em outras implementações de alta velocidade.

Segurança de E/S

A conformidade de leitura após gravação e gravações do setor transacional é imperdível. Nunca implante SQL Server em nenhum sistema que não dê suporte total aos requisitos de E/S SQL Server ou você corre o risco de danos e perda de seus dados.

Páginas já armazenadas em cache (cache de RAM duplo)

Tabelas temporárias são como todas as outras tabelas em um banco de dados. Eles são armazenados em cache pelo pool de buffers e manipulados por operações de gravação lentas. Armazenar páginas de tabela temporárias em um disco de RAM causa cache duplo de RAM, uma no pool de buffers e outra no disco de RAM. Isso tira diretamente o tamanho total possível do pool de buffers e geralmente diminui o desempenho de SQL Server.

Desistir da RAM

O disco de RAM designa uma parte da RAM main como o nome implica. Há várias implementações de discos de RAM e caches de arquivos baseados em RAM disponíveis. Alguns também habilitam operações físicas de backup de E/S. O elemento-chave do cache de arquivo baseado em RAM é que ele tira diretamente da memória física que pode ser usada por SQL Server. Sempre tenha fortes evidências de que adicionar um cache de arquivo baseado em RAM melhora o desempenho do aplicativo e não diminui o desempenho de outra consulta ou aplicativo.

Ajustar primeiro

Um aplicativo deve ajustar para remover tipos desnecessários e indesejados e hashes que possam causar o uso do banco de dados tempdb. Muitas vezes, a adição de um índice pode remover completamente a necessidade do tipo ou hash no plano, levando ao desempenho ideal sem exigir o uso do banco de dados tempdb.

Possíveis pontos de benefício

Os benefícios de colocar o banco de dados tempdb em um sistema de alta velocidade só podem ser determinados por meio de testes rigorosos e medidas das cargas de trabalho do aplicativo. A carga de trabalho precisa ser estudada cuidadosamente para as características das quais o banco de dados tempdb pode se beneficiar e a segurança de E/S deve ser confirmada antes da implantação.

As operações de classificação e hash funcionam em conjunto com os gerentes de memória SQL Server para determinar o tamanho da área de risco na memória para cada tipo ou operação de hash. Assim que os dados de classificação ou hash excederem a área de arranhão alocada na memória, os dados poderão ser gravados no banco de dados tempdb. Esse algoritmo foi expandido em SQL Server 2005, reduzindo os requisitos de uso do banco de dados tempdb em relação às versões anteriores do SQL Server.

SQL Server usa o banco de dados tempdb para lidar com várias atividades envolvendo classificações, hashes, repositório de versões de linha e tabelas temporárias:

• As tabelas temporárias são mantidas pelas rotinas comuns do pool de buffers para páginas de dados e geralmente não exibem benefícios de desempenho de implementações de subsistemas especiais.
• O banco de dados tempdb é usado como uma área de risco para hashes e classificações. Reduzir a latência de E/S para essas operações pode ser benéfico. No entanto, saiba que adicionar um índice para evitar um hash ou uma classificação pode fornecer um benefício semelhante.

Execute linhas de base com e sem o banco de dados tempdb armazenado no subsistema de alta velocidade para comparar benefícios. Parte do teste deve incluir consultas no banco de dados de usuário que não envolvem classificações, hashes ou tabelas temporárias e, em seguida, confirmar que essas consultas não são afetadas negativamente. Quando você avalia o sistema, os seguintes indicadores de desempenho podem ser úteis.

Exemplo de reescrita do setor transacional

O exemplo a seguir elabora a segurança de dados exigida por bancos de dados SQL Server.

Suponha que um fornecedor de disco de RAM use uma implementação de compactação na memória. A implementação deve ser encapsulada corretamente fornecendo a aparência física do fluxo de arquivos como se o setor estivesse alinhado e dimensionado para que SQL Server não esteja ciente e protegido corretamente da implementação subjacente. Examine o exemplo de compactação mais de perto.

• O setor 1 é gravado no dispositivo e é compactado para economizar espaço.
• O setor 2 é gravado no dispositivo e é compactado com o setor 1 para economizar espaço.

O dispositivo pode executar as seguintes ações para ajudar a proteger os dados do setor 1 quando ele é combinado com os dados do setor 2.

• Bloqueie todas as gravações para os setores 1 e 2.
• Descompactar o setor 1 em uma área de zero, deixando o armazenamento atual do setor 1 como os dados ativos a serem recuperados.
• Compacte os setores 1 e 2 em um novo formato de armazenamento.
• Bloqueie todas as leituras e gravações dos setores 1 e 2.
• Troque o armazenamento antigo para os setores 1 e 2 com novo armazenamento. Se a tentativa de troca falhar (reversão):
  • Restaure o armazenamento original para os setores 1 e 2.
  • Remova os setores 1 e 2 dados combinados da área de zero.
  • Falha na operação de gravação do setor 2.
• Desbloqueie leituras e gravações para os setores 1 e 2.

A capacidade de fornecer mecanismos de bloqueio em torno das modificações do setor e reverter as alterações quando a tentativa de troca do setor falhar é considerada em conformidade transitória. Para implementações que usam o armazenamento físico para suporte estendido, ele incluiria os aspectos apropriados do log de transações para ajudar a proteger e reverter as alterações que foram aplicadas às estruturas em disco para manter a integridade dos arquivos de banco de dados SQL Server.

Qualquer dispositivo que habilita a reescrita de setores deve dar suporte às reescritas de forma transacional para que SQL Server não seja exposta à perda de dados.

Tenha cuidado ao mover o banco de dados tempdb

Tenha cuidado ao mover o banco de dados tempdb porque se o banco de dados tempdb não puder ser criado, SQL Server não será iniciado. Se o banco de dados tempdb não puder ser criado, inicie SQL Server usando o parâmetro de inicialização (-f) e mova o banco de dados tempdb para um local válido.

Para alterar o local físico do banco de dados tempdb, siga estas etapas:

1. Use a ALTER DATABASE instrução e a MODIFY FILE cláusula para alterar os nomes de arquivo físico de cada arquivo no banco de dados tempdb para se referir ao novo local físico, como o novo disco.

   ALTER DATABASE tempdb MODIFY FILE 
   (name = tempdev, filename = 'C:\MyPath\tempdb.mdf')
   
   ALTER DATABASE tempdb MODIFY FILE 
   (name = templog, filename = 'C:\MyPath\templog.ldf')
   

2. Pare e reinicie SQL Server.

As certificações do produto parceiro não são uma garantia de compatibilidade ou segurança

Um produto de terceiros ou um fornecedor específico pode receber uma certificação de logotipo da Microsoft. No entanto, a certificação do parceiro ou um logotipo específico da Microsoft não certifica a compatibilidade ou a aptidão para uma finalidade específica em SQL Server.

Suporte

Se você usar um subsistema com SQL Server que dá suporte às garantias de E/S para uso de banco de dados transacional, conforme descrito neste artigo, a Microsoft fornecerá suporte para aplicativos baseados em SQL Server e SQL Server. No entanto, problemas com ou causados pelo subsistema serão encaminhados ao fabricante.

Para problemas relacionados ao banco de dados tempdb, Suporte da Microsoft Services pedirão que você realoque o banco de dados tempdb. Entre em contato com o fornecedor de dispositivos para verificar se você implantou e configurou corretamente o dispositivo para uso de banco de dados transacional.

A Microsoft não certifica nem valida que produtos de terceiros funcionam corretamente com SQL Server. Além disso, a Microsoft não fornece nenhuma garantia, garantia ou instrução da aptidão de qualquer produto de terceiros para uso com SQL Server.

Referências

Para obter mais informações, consulte os seguintes artigos na Base de Dados de Conhecimento Microsoft:

• SQL Server diagnóstico adicionais adicionados para detectar problemas de E/S não relatados

• A mensagem de erro 823 pode indicar problemas de hardware ou de sistema no SQL Server

• Usando o cache da unidade de disco com SQL Server

• Descrição do suporte para arquivos de banco de dados de rede no SQL Server

• A Microsoft não certifica que produtos de terceiros funcionarão com a Microsoft SQL Server

• Diretrizes de desempenho para SQL Server no Azure Máquinas Virtuais

• Otimizando seus planos de consulta com o estimador de cardinalidade do SQL Server 2014

• Desempenho da consulta