Partilhar via


Atualizações e opções de configuração recomendadas para SQL Server com cargas de trabalho de alto desempenho

Este artigo inclui uma lista de melhorias de desempenho e opções de configuração disponíveis para o SQL Server 2012 e versões posteriores.

Versão original do produto: SQL Server 2014, SQL Server 2012
Número original do KB: 2964518

Este artigo descreve as melhorias e alterações de desempenho disponíveis para as versões do SQL Server 2014 e do SQL Server 2012 por meio de várias atualizações de produto e opções de configuração. Você pode considerar a aplicação dessas atualizações para melhorar o desempenho da instância do SQL Server. O grau de melhoria que você verá dependerá de vários fatores que incluem padrão de carga de trabalho, pontos de contenção, layout do processador (número de grupos de processadores, soquetes, nós NUMA, núcleos em um nó NUMA) e quantidade de memória presente no sistema. A equipe de suporte do SQL Server usou essas atualizações e alterações de configuração para obter ganhos de desempenho razoáveis para cargas de trabalho do cliente que usavam sistemas de hardware que tinham vários nós NUMA e muitos processadores. A equipe de suporte continuará atualizando este artigo com outras atualizações no futuro.

Sistemas high-end Um sistema high-end normalmente tem vários soquetes, oito núcleos ou mais por soquete e meio terabyte ou mais de memória.

Observação

No SQL Server 2016 e versões posteriores, muitos dos sinalizadores de rastreamento mencionados neste artigo são o comportamento padrão e você não precisa habilitá-los nessas versões.

As recomendações são agrupadas em três tabelas da seguinte forma:

  • A Tabela 1 contém as atualizações recomendadas com mais frequência e os sinalizadores de rastreamento para escalabilidade em sistemas high-end.
  • A Tabela 2 contém recomendações e orientações para ajuste de desempenho adicional.
  • A Tabela 3 contém correções de escalabilidade adicionais que foram incluídas junto com uma atualização cumulativa.

Tabela 1. Atualizações importantes e sinalizadores de rastreamento para sistemas high-end

Examine a tabela a seguir e habilite os sinalizadores de rastreamento na coluna Sinalizador de rastreamento depois de verificar se sua instância do SQL Server atende aos requisitos na coluna Versão aplicável e intervalos de compilação.

Observação

  • Versão e build aplicáveis indicam a atualização específica na qual o sinalizador de alteração ou rastreamento foi introduzido. Se nenhuma for especificada, todas as CUs na controladora de armazenamento serão incluídas.

  • Versão e build não aplicáveis indica a atualização específica na qual o sinalizador de alteração ou rastreamento se tornou o comportamento padrão. Portanto, apenas aplicar essa atualização será suficiente para obter os benefícios.

Importante

Ao habilitar correções com sinalizadores de rastreamento em ambientes Always On, lembre-se de que você precisa habilitar os sinalizadores de correção e rastreamento em todas as réplicas que fazem parte do Grupo de Disponibilidade.

Cenário e sintoma a serem considerados Sinalizador de rastreamento Versão aplicável e intervalos de compilação Não aplicável Versão e intervalos de compilação Artigo da Base de Conhecimento/Link do blog que fornece mais detalhes
  • Você encontra altas esperas de CMEMTHREAD.
  • O SQL Server é instalado em sistemas com 8 ou mais núcleos por soquete.
T8048
  • SQL Server 2012 RTM para o Service Pack (SP)/atual
  • SQL Server 2014 RTM para SP1
  • SQL Server 2014 SP2 para SP/atual
  • SQL Server 2016 RTM para SP/atual
  • SQL Server 2017 RTM para SP/atual
  • Você encontra altas esperas de CMEMTHREAD.
  • O SQL Server é instalado em sistemas com 8 ou mais núcleos por soquete.
T8079 SQL Server 2014 SP2 para SP/atual
  • SQL Server 2016 RTM para SP/atual
  • SQL Server 2017 RTM para SP/atual
  • Você está usando recursos que dependem do cache do pool de logs. (por exemplo, Always On)
  • O SQL Server é instalado em sistemas com vários soquetes.
T9024 Pacote de atualização cumulativa 3 para o SQL Server 2012 Service Pack 1 para o SP2 SQL Server 2014 RTM
  • SQL Server 2012 SP3 para SP/CUSQL atual
  • Server 2014 SP1 para SP/atual
  • SQL Server 2016 RTM para SP/atual
  • SQL Server 2017 RTM para SP/atual
CORREÇÃO: Alto valor do contador de "esperas de gravação de log" em uma instância do SQL Server 2012 ou SQL Server 2014
Sua instância do SQL Server está lidando com milhares de redefinições de conexão devido ao pool de conexões. T1236 Pacote de atualização cumulativa 9 para SQL Server 2012 Service Pack 1 para SP2 Atualização cumulativa 1 para SQL Server 2014
  • SQL Server 2012 SP3 para SP/CUSQL atual
  • Server 2014 SP1 para SP/CUSQL atual
  • Server 2016 RTM para SP/atual
  • SQL Server 2017 RTM para SP/atual
  • A carga de trabalho do aplicativo envolve o uso frequente de tempdb (criação e descarte de tabelas temporárias ou variáveis de tabela).
  • Você observa solicitações de usuário aguardando recursos de página tempdb devido à contenção de alocação.
T1118
  • SQL Server 2012 RTM para SP/atual
  • SQL Server 2014 RTM para SP/atual
  • SQL Server 2016 RTM para SP/atual
  • SQL Server 2017 RTM para SP/atual
Aprimoramentos de simultaneidade para o banco de dados tempdb

OBSERVAÇÃO Ative o sinalizador de rastreamento e adicione vários arquivos de dados para o banco de dados tempdb.
  • Você tem vários arquivos de dados tempdb.
  • Os arquivos de dados no início são definidos com o mesmo tamanho.
  • Devido à atividade pesada, os arquivos tempdb encontram crescimento e nem todos os arquivos crescem ao mesmo tempo e causam contenção de alocação.
T1117
  • SQL Server 2012 RTM para SP/atual
  • SQL Server 2014 RTM para SP/atual
  • SQL Server 2016 RTM para SP/atual
  • SQL Server 2017 RTM para SP/atual
Recomendações para reduzir a contenção de alocação no banco de dados tempdb do SQL Server.
A contenção de spinlock pesada SOS_CACHESTORE ou seus planos estão sendo removidos com frequência em cargas de trabalho de consulta ad hoc. T174 Nenhum
  • As entradas no cache do plano são removidas devido ao crescimento em outros caches ou administradores de memória
  • Alto consumo de CPU devido a recompilações frequentes de consultas
T8032
  • SQL Server 2012 RTM para SP/atual
  • SQL Server 2014 RTM para SP/atual
Nenhum
As estatísticas existentes não são atualizadas com frequência devido ao grande número de linhas na tabela. T2371
  • SQL Server 2012 RTM para SP/atual
  • SQL Server 2014 RTM para SP/atual
Nenhum
  • Os trabalhos de estatística levam muito tempo para serem concluídos.
  • Não é possível executar vários trabalhos de atualização de estatísticas em paralelo.
T7471 SQL Server 2014 SP1 CU6 para SP/atual Nenhum Aumentando o desempenho das estatísticas de atualização com o SQL 2014 e o SQL 2016
CHECKDB leva muito tempo para bancos de dados grandes.
  • T2562
  • T2549
    • SQL Server 2012 RTM para SP/atual
    • SQL Server 2014 RTM para SP/atual
    Nenhum
    CHECKDB leva muito tempo para bancos de dados grandes. T2566
    • SQL Server 2012 RTM para SP/atual
    • SQL Server 2014 RTM para SP/atual
    Nenhum
    A execução de consultas simultâneas de data warehouse que levam muito tempo de compilação resulta em RESOURCE_SEMAPHORE_QUERY_COMPILE esperas. T6498 Pacote de atualização cumulativa 6 para SQL Server 2014 para SP1
    • SQL Server 2014 SP2 para SP/CUSQL atual
    • Server 2016 RTM para SP/atual
    • SQL Server 2017 RTM para SP/atual
    Você está solucionando problemas específicos de desempenho de consulta As correções do Optimizer estão desativadas por padrão. T4199
    • SQL Server 2012 RTM para SP4
    • SQL Server 2014 RTM para o mais recente
    Nenhum
    Você experimenta um desempenho lento usando operações de consulta com tipos de dados espaciais.
    • T6532
    • T6533
    • T6534
    • SQL Server 2012 SP3 para SP/atual
    • SQL Server 2014 SP2 para SP/atual
      • SQL Server 2016 RTM para SP/atual
      • SQL Server 2017 RTM para SP/atual
        • As consultas encontram SOS_MEMORY_TOPLEVELBLOCKALLOCATOR e CMEMTHREAD aguarda.
        • Há pouco espaço de endereço virtual disponível para o processo do SQL Server.
        T8075
        • SQL Server 2012 SP2 CU8 para SP/atual
        • SQL Server 2014 RTM CU10 para SP/atual
        • SQL Server 2016 RTM para SP/atual
        • SQL Server 2017 RTM para SP/atual
        CORREÇÃO: Erro de falta de memória quando o espaço de endereço virtual do processo do SQL Server é baixo no SQL Server
        • O SQL Server é instalado em um computador com grandes quantidades de memória.
        • A criação de novos bancos de dados leva muito tempo.
        T3449
        • SQL Server 2012 SP3 CU3 para SP/atual
        • SQL Server 2014 RTM CU14 para a RTM atual
        • SQL Server 2014 SP1 CU7 para SP/atual
        • SQL Server 2016 RTM para SP/atual
        • SQL Server 2017 RTM para SP/atual
        CORREÇÃO: A criação do banco de dados do SQL Server em um sistema com um grande volume de memória leva mais tempo do que o esperado

        Tabela 2. Considerações gerais e práticas recomendadas para melhorar o desempenho da sua instância do SQL Server

        Revise o conteúdo do artigo da Base de Dados de Conhecimento/coluna Recursos dos Manuais Online e considere implementar as diretrizes na coluna Ações recomendadas.

        Artigo da Base de Dados de Conhecimento/Recurso dos Manuais Online Ações recomendadas
        Configurar a opção de configuração de servidor max degree of parallelism Use o procedimento armazenado sp_configure para fazer alterações de configuração para Configurar a opção de configuração do servidor de grau máximo de paralelismo para sua instância do SQL Server de acordo com o artigo da Base de Dados de Conhecimento.
        Computar limites de capacidade por edição do SQL Server O licenciamento Enterprise Edition com CAL (Licença de Acesso para Cliente) Server + Client é limitado a 20 núcleos por instância do SQL Server. Não há limites no modelo de Licenciamento de Servidor Baseado em Núcleo. Considere atualizar sua edição do SQL Server para o SKU apropriado para aproveitar todos os recursos de hardware.
        Desempenho lento no Windows Server ao usar o plano de energia "Balanceado" Examine o artigo e trabalhe com o administrador do Windows para implementar uma das soluções observadas na seção "Resolução" do artigo.
        Atribua manualmente nós NUMA a grupos K.
        Otimizar para cargas de trabalho ad hoc PARAMETRIZAÇÃO FORÇADA As entradas no cache de planos são removidas devido ao crescimento em outros caches ou administradores de memória. Você também pode encontrar a remoção do cache de plano quando o cache atingir seu número máximo de entradas. Além do sinalizador de rastreamento 8032 discutido acima, considere a opção de servidor otimizar para cargas de trabalho ad hoc e também a opção de banco de dados FORCED PARAMETERIZATION .
        Como reduzir a paginação da memória do pool de buffers no SQL Server Considerações sobre configuração e dimensionamento de memória no SQL Server 2012 e versões posteriores Atribua o direito de usuário Habilitar as Páginas de Bloqueio na Opção de Memória (Windows) à conta de inicialização do serviço SQL. Consulte Como habilitar o recurso "páginas bloqueadas" no SQL Server 2012. Defina a memória máxima do servidor para aproximadamente 90% da memória física total. Certifique-se de que a configuração de opções de configuração de memória do servidor considere a memória apenas dos nós configurados para usar as configurações de máscara de afinidade.
        SQL Server e páginas grandes explicadas... Opções de ajuste para SQL Server ao executar em cargas de trabalho de alto desempenho Considere habilitar o TF 834 se você tiver um servidor com uma grande quantidade de memória, especialmente com uma carga de trabalho analítica ou de data warehouse. Lembre-se de que o TF 834 não é recomendado se você estiver usando índices columnstore.
        Descrição das opções "contagem de buckets de cache de verificação de acesso" e "cota de cache de verificação de acesso" que estão disponíveis no procedimento armazenado sp_configure Use as opções de configuração do servidor de cache de verificação de acesso para configurar esses valores de acordo com as recomendações no artigo da Base de Dados de Conhecimento. Os valores recomendados para sistemas high-end são os seguintes:
        "Contagem de buckets de cache de verificação de acesso": 256
        "Cota de cache de verificação de acesso": 1024

        ALTER WORKLOAD GROUP Dicas de consulta de concessão de memória Se você tiver muitas consultas que estão esgotando grandes concessões de memória, reduza request_max_memory_grant_percent para o grupo de carga de trabalho padrão na configuração do administrador de recursos do padrão 25% para um valor menor. Novas opções de concessão de memória de consulta estão disponíveis (min_grant_percent e max_grant_percent) no SQL Server
        Inicialização instantânea de arquivos Trabalhe com o administrador do Windows para conceder à conta de serviço do SQL Server o direito de usuário "Executar Tarefas de Manutenção de Volume" de acordo com as informações no tópico dos Manuais Online.
        Considerações sobre as configurações "autogrow" e "autoshrink" no SQL Server Verifique as configurações atuais do seu banco de dados e certifique-se de que elas estejam configuradas de acordo com as recomendações no artigo da Base de Dados de Conhecimento.
        Pontos de verificação de banco de dados (SQL Server) Considere habilitar pontos de verificação indiretos em bancos de dados de usuário para otimizar o comportamento de E/S no SQL Server 2012 e 2014.
        CORREÇÃO: Sincronização lenta quando os discos têm tamanhos de setor diferentes para arquivos de log de réplica primária e secundária em ambientes SQL Server AG e Logshipping Se você tiver um Grupo de Disponibilidade em que o log de transações na réplica primária está em um disco com tamanho de setor de 512 bytes e o log de transações da réplica secundária está em uma unidade com tamanho de setor de 4K, você pode ter um problema em que a sincronização é lenta. Nesses casos, habilitar o TF 1800 deve corrigir o problema. Para obter mais informações, consulte Sinalizador de rastreamento 1800.
        Se o SQL Server ainda não estiver vinculado à CPU e uma sobrecarga de 1,5% a 2% for insignificante para suas cargas de trabalho, recomendamos que você habilite o TF 7412 como um sinalizador de rastreamento de inicialização. Esse sinalizador permite a criação de perfil leve no SQL Server 2014 SP2 ou posterior, o que lhe dará a capacidade de solucionar problemas de consulta dinâmica em ambientes de produção.

        Tabela 3. Correções de desempenho incluídas em uma atualização cumulativa

        Revise a descrição na coluna Sintomas e aplique as atualizações necessárias na coluna Atualização necessária nos ambientes aplicáveis. Você pode revisar o artigo da Base de Dados de Conhecimento para obter mais informações sobre os respectivos problemas. Essas recomendações não exigem que você habilite sinalizadores de rastreamento adicionais como parâmetros de inicialização. Basta aplicar a atualização cumulativa ou o Service Pack mais recente que inclui essas correções para obter o benefício.

        Observação

        O nome da na coluna Atualização necessária fornece a primeira atualização cumulativa do SQL Server que resolve esse problema. Uma atualização cumulativa contém todos os hotfixes e todas as atualizações que foram incluídas na versão anterior da atualização do SQL Server. Portanto, recomendamos que você instale a atualização cumulativa mais recente para resolver os problemas.

        Sintomas Atualização necessária Artigo da Base de Conhecimento
        Gravações ansiosas durante Select-into para tabelas temporárias causam problemas de desempenho. SQL Server 2012 SP2 CU1
        SQL Server 2012 SP1 CU10
        CORREÇÃO: Baixo desempenho na E/S quando você executa a operação de seleção em tabela temporária no SQL Server 2012
        Você encontra PWAIT_MD_RELATION_CACHE ou MD_LAZYCACHE_RWLOCK aguarda depois que uma operação de ALTER INDEX ... ONLINE consulta é anulada. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU9
        CORREÇÃO: O desempenho diminui após um ALTER INDEX... A operação ONLINE é anulada no SQL Server 2012 ou no SQL Server 2014
        De repente, as consultas têm um desempenho ruim na edição padrão do produto. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU7
        CORREÇÃO: Os threads não são agendados uniformemente no SQL Server 2012 ou SQL Server 2014 Standard Edition
        Desempenho lento devido a uma queda repentina na expectativa de vida da página. SQL Server 2012 SP1 CU4 CORREÇÃO: Você pode ter problemas de desempenho no SQL Server 2012
        Alto uso da CPU pelo monitor de recursos em sistemas com configuração NUMA, memória grande e "memória máxima do servidor" definida como um valor baixo. SQL Server 2012 SP1 CU3 CORREÇÃO: pico de CPU quando não há carga em um servidor depois de instalar o SQL Server 2012 no servidor
        O agendador sem rendimento enquanto a memória de alocação para classificação executa grandes concessões de memória associadas em sistemas com grande quantidade de memória instalada. SQL Server 2012 SP1 CU2 CORREÇÃO: Erro 17883 quando você executa uma consulta em um servidor que tem muitas CPUs e uma grande quantidade de memória no SQL Server 2012 ou no SQL Server 2008 R2
        Programador sem rendimento quando o operador de classificação percorre muitos buckets no pool de buffers em sistemas com memória grande. SQL Server 2012 SP1 CU1 CORREÇÃO: Mensagem de erro "O processo parece não estar funcionando no Agendador" quando você executa uma consulta no SQL Server 2012
        Alto uso da CPU quando você executa consultas simultâneas que levam muito tempo para compilar em sistemas com vários nós NUMA e muitos núcleos. SQL Server 2012 SP2 CU1
        SQL Server 2014 RTM CU2
        CORREÇÃO: A carga de trabalho intensa de compilação de consultas não é dimensionada com o número crescente de núcleos no hardware NUMA e resulta na saturação da CPU no SQL Server
        As alocações de memória para operadores de classificação levam muito tempo para serem concluídas em sistemas NUMA com grande memória devido às alocações de nós remotos. SQL Server 2012 SP1 CU3 CORREÇÃO: Problemas de desempenho do SQL Server em ambientes NUMA
        Erros de falta de memória quando o SQL Server é instalado em uma máquina NUMA com grande quantidade de RAM e o SQL Server tem muitas páginas externas. SQL Server 2012 RTM CU1 CORREÇÃO: Erro de falta de memória ao executar uma instância do SQL Server 2012 em um computador que usa NUMA
        Contenção de spinlock em SOS_CACHESTORE e SOS_SELIST_SIZED_SLOCK quando você cria um índice no tipo de dados espaciais em uma tabela grande. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU7
        CORREÇÃO: Desempenho lento no SQL Server 2012 ou SQL Server 2014 quando você cria um índice em um tipo de dados espaciais de uma tabela grande
        Tipo de espera CMEMTHREAD alto quando você cria um índice em um tipo de dados espaciais em tabelas grandes. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU7
        CORREÇÃO: Desempenho lento no SQL Server quando você cria um índice em um tipo de dados espaciais de uma tabela grande em uma instância do SQL Server 2012 ou SQL Server 2014
        Problemas de desempenho devido a SOS_PHYS_PAGE_CACHE e CMEMTHREAD aguarda durante a alocação de memória em computadores de memória grande. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU9
        CORREÇÃO: Problemas de desempenho ocorrem em ambientes NUMA durante o processamento de páginas estrangeiras no SQL Server 2012 ou SQL Server 2014
        CHECKDB leva muito tempo para bancos de dados grandes. Pacote de atualização cumulativa 6 para SQL Server 2014 CORREÇÃO: O comando DBCC CHECKDB/CHECKTABLE pode demorar mais no SQL Server 2012 ou SQL Server 2014

        Observações importantes

        Referências

        Aplicável ao

        • SQL Server 2014 Enterprise
        • SQL Server 2014 Enterprise Core
        • SQL Server 2014 Business Intelligence
        • SQL Server 2014 Developer
        • SQL Server 2014 Standard
        • SQL Server 2014 Web
        • SQL Server 2014 Express
        • Inteligência de Negócios do SQL Server 2012
        • SQL Server 2012 Developer
        • SQL Server 2012 Enterprise
        • SQL Server 2012 Standard
        • SQL Server 2012 Web
        • SQL Server 2012 Enterprise Core