Início / dba / Perguntas / 129169
Accepted
Aasim Abdullah
Asked: 2016-02-13 23:00:33 +0800 CST

Contenções TempDB

  • 772

Temos um banco de dados OLTP 40GB ativo no SQL Server 2014 SP1. As consultas são consideradas lentas com esperas de IO_Completion, tamanho da fila de disco subindo para 900 e o SQL Server para de responder. O que tentamos:

  1. Reinicie a instância e em um minuto ela começa a se comportar da mesma maneira.

  2. Após a segunda reinicialização, alteramos o tamanho inicial de cada arquivo de dados tempdb (há 16 arquivos de dados criados) e ele começou a funcionar corretamente.

Observação: estamos usando variáveis ​​de tabela para conjuntos de resultados intermediários. Esses conjuntos de resultados são muito pequenos.

Aconteceu duas vezes em um mês. Toda vez que adiciono um pouco de espaço manualmente aos arquivos de dados, ele começa a funcionar normalmente. O mais interessante é que a mesma configuração (mesmo hardware, mesma configuração de pastas e arquivos, mesma carga de trabalho) que temos no SQL Server 2008 R2 e SQL Server 2012 está funcionando bem.

Por favor, ajude-nos a encontrar uma solução permanente.

O tamanho inicial de todos os arquivos de dados é o mesmo de 1.000 MB, o atual é de 1.500 MB cada. Todos são idênticos. O crescimento automático é de 100 MB para cada um. Antes disso, enfrentávamos contenção de páginas PFS e GAM e aumentamos para 16 e o ​​problema foi resolvido. Ambos os sinalizadores de rastreamento 1117 e 1118 estão ativados. 24 núcleos em 2 nós NUMA. Todos os arquivos de dados estão no mesmo volume. Disco simples, sem SAN.

A instância está em uma máquina física. Consultas com variáveis ​​de tabela e consultas com junções de hash geralmente geram esperas de IO_Completion.

A resposta detalhada de wBob nos levou a pesquisar mais detalhadamente. Como é que perdemos isso antes:

O crescimento automático do arquivo 'templog' no banco de dados 'tempdb' foi cancelado pelo usuário ou expirou após 7704 milissegundos. Use ALTER DATABASE para definir um valor FILEGROWTH menor para este arquivo ou para definir explicitamente um novo tamanho de arquivo.

Isso encontramos no log sempre que esse tipo de problema está ocorrendo. Estamos movendo o TempDB para uma unidade rápida separada.

sql-server sql-server-2014

1 respostas

  1. Best Answer

    Acho que você superfragmentou seu tempdb e há uma incompatibilidade entre a CPU do servidor e a configuração do disco, mas vamos coletar mais algumas informações:

    Dúvidas / Mais informações necessárias

    • Confirme o nome e o tipo do processador (estou basicamente tentando estabelecer se é 2 x hex-core com HT ). Use as informações do sistema (por exemplo, Painel de controle > Sistema e segurança > Sistema no Windows Server 2012 R2) e/ou a ferramenta sysinternals CoreInfo para confirmar.
    • Por favor, confirme o maxdop do servidor (por exemplo EXEC sp_configure 'max degree of parallelism'). Se as CPUs forem hex-core, o maxdop do servidor deve ser de no máximo 6 (conforme aqui ), ou possivelmente menor em um sistema OLTP. Eu normalmente mantenho meus arquivos tempdb alinhados com o DOP do meu servidor em um máximo de 8, mas vamos chegar a isso.
    • Confirme a memória total do servidor na caixa e o limite de memória do SQL Server (por exemplo EXEC sp_configure 'max server memory (MB)').
    • Confirme se algum outro serviço está sendo executado na caixa (por exemplo, SSIS, SSAS, SSRS, o aplicativo, iTunes etc.)
    • Confirme se a inicialização instantânea de arquivo está habilitada para a conta de serviço do SQL Server. (Maneiras de testá-lo aqui ).
    • Por que existe uma discrepância tão grande entre a CPU (configuração robusta de 2 nós NUMA) e o único disco (PC doméstico)? Considere adicionar discos, striping, SSD para tempdb (embora evite exagerar : ).
    • Adicione um plano de execução real para uma das consultas de problemas. Anonimize com o SQL Sentry Plan Explorer, se desejar.

    • Hash se junta a variáveis ​​de tabela em um sistema OLTP? Isso sugere falta de indexação na variável da tabela, tabela principal ou ambas. Você está declarando suas variáveis ​​de tabela assim (sem índices)?

      DECLARE @t TABLE ( x INT )

    • Não economize na definição da variável de tabela, mesmo que ela contenha pequenos conjuntos de resultados. É sempre melhor fornecer ao otimizador o máximo de informações possível, portanto, seja explícito com nulidade, exclusividade, independentemente de o índice ser ou não clusterizado / não clusterizado, por exemplo

      DECLARE @t TABLE ( x INT PRIMARY KEY )
DECLARE @u TABLE ( x INT PRIMARY KEY NONCLUSTERED, u INT NOT NULL UNIQUE CLUSTERED, z INT NOT NULL UNIQUE, a CHAR(1) NULL ) -- not sure why you would do this but you can
DECLARE @v TABLE ( x INT NOT NULL, y INT NOT NULL, PRIMARY KEY ( x, y ) )   -- multi-column primary key

    • Publicar o plano de execução ajudará a diagnosticar isso.

    • Verifique o código que impede o cache de variáveis ​​de tabela conforme aqui , aqui . Acho que o SQL dinâmico e o proc executado WITH RECOMPILE são os únicos que afetam as variáveis ​​da tabela.

      DECLARE @u TABLE ( x INT )

INSERT @u
EXEC('DECLARE @t TABLE ( x INT ); INSERT INTO @t VALUES ( 1 ); SELECT x FROM @t;' )

SELECT *
FROM @u

    • Verifique o Log do SQL Server (Pesquisador de Objetos > Gerenciamento > Logs do SQL Server) em busca de mensagens, por exemplo, avisos de E/S.

    • Verifique o Visualizador de Eventos do Windows
    • Vários builds foram lançados desde o SP1. Revise as correções de CU colocadas desde o SP1 . É possível que haja bugs no SP1 corrigidos em CUs subsequentes, por exemplo, CORREÇÃO: o operador de classificação derrama para tempdb no SQL Server 2012 ou SQL Server 2014 quando o número estimado de linhas e o tamanho da linha estão corretos https://support.microsoft.com/en- us/kb/3088480
    • Estabeleça esta é sua causa antes de aplicar qualquer hotfix, embora seja mais importante manter-se atualizado com CUs com SQL Server 2014, devido ao número de novos recursos (OLTP in-memory, clustered columnstore).
    • Por fim, a necessidade de um arquivo tempdb por núcleo é um mito e, olhando para a configuração do disco, acho que o tempdb está excessivamente fragmentado. Tenho a sensação incômoda de que você tem uma cabeça de disco, tempdb tem um grupo de arquivos, muitos arquivos.

    No entanto, esqueça o que pensamos que sabemos; crie um equipamento de teste que reproduza seu problema e experimente reduzir o número de arquivos temporários ... comece em 1, 2, 4, 6 etc. reúna as informações para tomar uma decisão baseada em evidências. Agora, esta é a parte mais difícil, pois seu problema parece intermitente e você pode não conseguir mexer na configuração do tempdb, mas é assim que eu abordaria isso.

    Boa sorte. Deixe-nos saber como você se sai.

    • 6

relate perguntas