Configurar a opção max worker threads de configuração de servidor

  • Artigo

Este tópico descreve como configurar a opção de configuração de servidor max worker threads 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 disponíveis para processos do SQL Server. O SQL Server usa os serviços de thread nativos dos sistemas operacionais para que um ou mais threads ofereçam suporte a cada rede com suporte do SQL Server simultaneamente, outro thread lide com pontos de verificação de banco de dados e um pool de threads lide com 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.

Neste tópico

Antes de começar

Limitações e Restrições

  • Quando o número real de solicitações de consulta é menor que a quantidade definida em max worker threads, um thread controla cada solicitação de consulta. No entanto, se o número real de solicitações de consulta exceder a quantidade definida em max worker threads, o SQL Server agrupará os threads de trabalho para que o próximo thread de trabalho disponível possa lidar com a solicitação.

Recomendações

  • Essa opção é uma opção avançada e deve ser alterada somente por um administrador de banco de dados experiente ou técnico certificado do SQL Server.

  • O thread pooling ajuda a otimizar o desempenho quando são conectados grandes números de clientes 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 tabela a seguir mostra o número configurado automaticamente de max worker threads para várias combinações de CPUs e versões do SQL Server.

    Número de CPUs Computador de 32 bits Computador de 64 bits
    <= 4 processadores 256 512
    8 processadores 288 576
    16 processadores 352 704
    32 processadores 480 960
    64 processadores 736 1.472
    128 processadores 4.224 4480
    256 processadores 8.320 8.576

    Observação

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

    Aviso

    Recomendamos 1024 como o número máximo de threads de trabalho para uma instância do SQL Server que está sendo executada em um computador de 32 bits.

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

A opção de configuração do servidor max worker threads não leva em consideração os threads necessários para todas as tarefas de sistema como grupos de disponibilidade, Service Broker, Gerenciador de Bloqueio dentre outros. Se o número de threads configurados estiver sendo excedido, a seguinte consulta 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  
INNERJOIN 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;

Segurança

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 .

Como usar o SQL Server Management Studio.

Para configurar a opção max worker threads

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

  2. Clique no nó Processadores .

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

    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.

Usando o Transact-SQL

Para configurar a opção max worker threads

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

  2. Na barra Padrão, clique em Nova Consulta.

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

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'max worker threads', 900 ;  
GO  
RECONFIGURE;  
GO

Para obter mais informações, confira Opções de configuração do servidor (SQL Server).

Acompanhamento: depois de configurar a opção max worker threads

A alteração entrará em vigor imediatamente sem exigir que o Mecanismo de Banco de Dados seja reiniciado.

Confira também

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