Configure the max worker threads (opção de configuração do servidor)

  • Artigo

Aplica-se a:SQL Server

Este artigo descreve como configurar a opção máximo de threads de trabalho de configuração de servidor no SQL Server usando o SQL Server Management Studio ou o Transact-SQL. A opção max worker threads configura o número de threads de trabalho que estão disponíveis em todo o SQL Server para processar solicitações de consulta, logon, logout e solicitações de aplicativos semelhantes.

SQL Server o usa os serviços de thread nativos dos sistemas operacionais para garantir as seguintes condições:

  • Um ou mais threads dão suporte simultâneo a cada rede à qual o SQL Server dá suporte.

  • Um thread manipula pontos de verificação de banco de dados.

  • Um pool de threads manipula todos os usuários.

O valor padrão de max worker threads é 0. Isso habilita o SQL Server a configurar automaticamente o número de threads de trabalho na inicialização. A configuração padrão é a melhor para a maioria dos sistemas. No entanto, dependendo de sua configuração de sistema, a definição de max worker threads com um valor específico às vezes melhora o desempenho.

Limitações

  • O número real de solicitações de consulta pode exceder o valor definido em max worker threads, o que levará o SQL Server a fazer pool dos threads de trabalho de maneira que o próximo thread de trabalho disponível possa manipular a solicitação. Um thread de trabalho é atribuído somente a solicitações ativas e é liberado depois que a solicitação é atendida. Isso acontece mesmo quando a sessão/conexão do usuário na qual a solicitação foi feita permanece aberta.

  • A opção de configuração do servidor máximo de threads de trabalho não limita todos os threads que podem ser gerados dentro do mecanismo. Os threads do sistema necessários para tarefas, como LazyWriter, Ponto de Verificação, Gravador de Logs, Service Broker, Gerenciador de Bloqueio ou outros, são gerados fora desse limite. Os grupos de disponibilidade usam alguns dos threads de trabalho dentro do valor limite máximo de threads de trabalho, mas também usam threads do sistema (confira Uso de threads por grupos de disponibilidade). Se o número de threads configurados for excedido, a consulta a seguir fornecerá informações sobre as tarefas do sistema que geraram os threads adicionais.

    SELECT s.session_id,
    r.command,
    r.status,
    r.wait_type,
    r.scheduler_id,
    w.worker_address,
    w.is_preemptive,
    w.state,
    t.task_state,
    t.session_id,
    t.exec_context_id,
    t.request_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
    ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks AS t
    ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers AS w
    ON t.worker_address = w.worker_address
WHERE s.is_user_process = 0;

Recomendações

  • Esta é uma opção avançada e deve ser alterada somente por um administrador de banco de dados experiente ou por um profissional de SQL Server certificado. Se você suspeitar que há um problema de desempenho, é provável que não seja a disponibilidade dos threads de trabalho. A causa é mais provavelmente relacionada a atividades que ocupam os threads de trabalho e não os liberam. Os exemplos incluem consultas de execução longa ou gargalos no sistema (E/S, bloqueio, tempos de espera de trava, esperas de rede) que causam consultas de espera longa. É melhor localizar a causa raiz de um problema de desempenho antes de alterar a configuração max worker threads. Para obter mais informações sobre como avaliar o desempenho, confira Monitorar e ajustar o desempenho.

  • O thread pooling ajuda a otimizar o desempenho quando um grande número de clientes é conectado ao servidor. Normalmente, é criado um thread de sistema operacional separado para cada solicitação de consulta. Porém, com centenas de conexões para o servidor, usam um thread por solicitação de consulta pode consumir quantias grandes de recursos do sistema. A opção max worker threads habilita o SQL Server a criar um pool de threads de trabalho para atender a um número maior de solicitações de consulta, o que melhora o desempenho.

  • A seguinte tabela mostra o número máximo automaticamente configurado de threads de trabalho (quando o valor é definido para 0) com base nas várias combinações de CPUs lógicas, arquitetura de computador e versões do SQL Server, usando a fórmula: Máximo de trabalhos padrão + ((CPUs lógicas - 4) * Trabalhos por CPU).

    Número de CPUs lógicas Computador de 32 bits (até SQL Server 2014 (12.x)) Computador de 64 bits (até SQL Server 2016 (13.x) SP1) Computador de 64 bits (começando em SQL Server 2016 (13.x) SP2 e SQL Server 2017 (14.x))
    < = 4 256 512 512
    8 288 576 576
    16 352 704 704
    32 480 960 960
    64 736 1.472 1.472
    128 1248 2496 4480
    256 2272 4544 8.576

    Até SQL Server 2016 (13.x) com Service Pack 1, os Trabalhos por CPU só dependem da arquitetura (32 bits ou 64 bits):

    Número de CPUs lógicas Computador de 32 bits 1 Computador de 64 bits
    < = 4 256 512
    > 4 256 + ((CPUs lógicas - 4) * 8) 512 2 + ((CPUs lógicas - 4) * 16)

    A partir do SQL Server 2016 (13.x) SP2 e SQL Server 2017 (14.x), os Trabalhos por CPU dependem da arquitetura e do número de processadores (entre 4 e 64 ou maior que 64):

    Número de CPUs lógicas Computador de 32 bits 1 Computador de 64 bits
    < = 4 256 512
    > 4 e <= 64 256 + ((CPUs lógicas - 4) * 8) 512 2 + ((CPUs lógicas - 4) * 16)
    > 64 256 + ((CPUs lógicas - 4) * 32) 512 2 + ((CPUs lógicas - 4) * 32)

    1 A partir do SQL Server 2016 (13.x), o SQL Server não pode mais ser instalado em um sistema operacional de 32 bits. Valores de computador de 32 bits são listados para a assistência aos clientes que executam o SQL Server 2014 (12.x) e versões anteriores. É recomendável 1.024 como o número máximo de threads de trabalho para uma instância do SQL Server executado em um computador de 32 bits.

    2 A partir do SQL Server 2017 (14.x), o valor Máximo de trabalhos padrão é dividido por 2 para computadores com menos de 2 GB de memória.

    Dica

    Para obter mais informações sobre como usar mais de 64 CPUs, veja Melhores práticas para executar o SQL Server em computadores que têm mais de 64 CPUs.

  • Quando todos os threads de trabalho estiverem ativos com a execução de consultas longas, o SQL Server poderá parecer não estar respondendo até que um thread de trabalho seja concluído e fique disponível. Embora não seja um defeito, isso às vezes pode ser indesejável. Se um processo parecer ser não estar respondendo e nenhuma nova consulta possa ser processada, então conecte ao SQL Server que usa a conexão de administrador dedicada (DAC) e elimine o processo. Para evitar isto, aumente o número de máximo threads de trabalho.

Permissões

Permissões de execução sem parâmetros ou com apenas o primeiro parâmetro em sp_configure são concedidas a todos os usuários por padrão. Para executar sp_configure com ambos os parâmetros para alterar uma opção de configuração ou executar a instrução RECONFIGURE, o usuário deve ter a permissão ALTER SETTINGS no nível do servidor. A permissão ALTER SETTINGS é implicitamente mantida pelas funções de servidor fixas sysadmin e serveradmin.

Usar SSMS (SQL Server Management Studio)

  1. No Pesquisador de Objetos, clique com o botão direito do mouse em um servidor e selecione Propriedades.

  2. Selecione o nó Processadores.

  3. Na caixa Máximo de threads de trabalho, digite ou selecione um valor entre 128 e 65.535.

Dica

Use a opção max worker threads para configurar o número de threads de trabalho disponível para os processos do SQL Server . A configuração padrão para max worker threads é a melhor para a maioria dos sistemas.
No entanto, dependendo de sua configuração de sistema, definir máximo de threads de trabalho como um valor menor algumas vezes melhora o desempenho. Para obter mais informações, confira a seção Recomendações neste artigo.

Usar o Transact-SQL

  1. Conecte-se ao Mecanismo de Banco de Dados.

  2. Na barra Padrão, selecione Nova Consulta.

  3. Copie e cole o exemplo a seguir na janela de consulta e selecione Executar. Este exemplo mostra como usar o sp_configure para configurar a opção max worker threads como 900.

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'max worker threads', 900;
GO

RECONFIGURE;
GO

A alteração entrará em vigor imediatamente após a execução da opção RECONFIGURAR, sem exigir que o Mecanismo de Banco de Dados seja reiniciado.

Conteúdo relacionado