Configuração do servidor: máximo de threads de trabalho

Aplica-se: SQL Server

Este artigo descreve como configurar a opção de configuração do servidor no SQL Server usando o max worker threads SQL Server Management Studio ou o Transact-SQL. A max worker threads opção configura o número de threads de trabalho 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 da configuração do sistema, a configuração max worker threads para 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 e, nesse max worker threads caso, o SQL Server agrupa os threads de trabalho para que o próximo thread de trabalho disponível possa lidar com 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 max worker threads opção de configuração do servidor 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 de dentro do max worker thread limit mas também usam threads do sistema (consulte Uso de thread por grupos de disponibilidade). Se o número de threads configurados estiver sendo 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, provavelmente não é a disponibilidade de 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 encontrar a causa raiz de um problema de desempenho antes de alterar a configuração de máximo de threads de trabalho. Para obter mais informações sobre como avaliar o desempenho, consulte 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 max worker threads opção permite que o SQL Server crie 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).

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

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):

¹ 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.

² 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.

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 esse comportamento não seja um defeito, à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.

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.

   USE master;
   GO
   
   EXECUTE sp_configure 'show advanced options', 1;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'max worker threads', 900;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'show advanced options', 0;
   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

• Opções de configuração do servidor
• RECONFIGURE (Transact-SQL)
• sp_configure (Transact-SQL)
• Conexão de diagnóstico para administradores de banco de dados