Guia de arquitetura de tópicos e tarefas

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Agendamento de tarefas do sistema operacional

Threads são as menores unidades de processamento executadas por um sistema operacional e permitem que a lógica do aplicativo seja separada em vários caminhos de execução simultâneos. Eles são úteis quando aplicativos complexos têm muitas tarefas que podem ser executadas ao mesmo tempo.

Quando um sistema operacional executa uma instância de um aplicativo, cria uma unidade denominada processo para gerenciar a instância. O processo tem um thread de execução. Tal processo é a série de instruções de programação executada pelo código de aplicativo. Por exemplo, se um aplicativo simples tiver um único conjunto de instruções que podem ser executadas em série, esse conjunto de instruções é manuseado como uma tarefa simples e haverá apenas um caminho de execução (ou thread) no aplicativo. Os aplicativos mais complexos podem ter várias tarefas que podem ser executadas em simultaneamente, em vez de em série. Um aplicativo pode fazer isso iniciando processos separados para cada tarefa, que é uma operação com uso de muitos recursos, ou iniciar threads separados, que relativamente utilizam menos recursos. Além disso, cada thread pode ser agendado para execução independentemente dos outros threads associados a um processo.

Os threads permitem aplicativos complexos para utilizar com mais eficácia uma CPU (processador), mesmo em computadores com uma única CPU. Com uma CPU, apenas um thread pode ser executado de cada vez. Se um thread executar uma operação de longa duração que não use a CPU, como uma leitura ou gravação em disco, outro thread poderá ser executado até que a primeira operação seja concluída. Com a possibilidade de executar threads enquanto outros threads estão esperando pela conclusão de uma operação, o aplicativo consegue maximizar o uso da CPU. Isso é especialmente verdadeiro para aplicativos intensivos multiusuário e de E/S de disco, como um servidor de banco de dados. Os computadores que têm várias CPUs podem executar um thread por CPU ao mesmo tempo. Por exemplo, se um computador tiver oito CPUs, poderá executar oito threads ao mesmo tempo.

Agendamento de tarefas SQL Server

No escopo do SQL Server, uma solicitação é a representação lógica de uma consulta ou lote. Uma solicitação também representa operações exigidas por threads do sistema, como ponto de verificação ou gravador de log. Solicitações existem em vários estados ao longo de sua vida útil e podem acumular esperas quando os recursos necessários para executar a solicitação não estão disponíveis, como bloqueios ou travas. Para saber mais sobre os estados de solicitação, confira sys.dm_exec_requests.

Tarefas

Uma tarefa representa a unidade de trabalho que precisa ser concluída para atender à solicitação. Uma ou mais tarefas podem ser atribuídas a uma única solicitação.

• As solicitações paralelas têm várias tarefas habilitas que são executadas simultaneamente em vez de em série, com uma tarefa principal (ou tarefa de coordenação) e várias tarefas secundárias. O plano de execução de uma solicitação paralela poderá ter ramificações seriais, áreas do plano com operadores que não são executados em paralelo. A tarefa pai também é responsável por executar esses operadores seriais.
• As solicitações em série têm apenas uma tarefa ativa em um determinado momento durante a execução. Existem tarefas em vários estados durante seu tempo de vida. Para saber mais sobre os estados de tarefas, confira sys.dm_os_tasks. Tarefas em um estado SUSPENSO estão aguardando os recursos necessários para executar a tarefa a ser disponibilizada. Para obter mais informações sobre as tarefas de espera, confira sys.dm_os_waiting_tasks.

Trabalhadores

Um thread de trabalho do SQL Server, também conhecido como trabalhador ou thread, é uma representação lógica de um thread do sistema operacional. Ao executar solicitações em série, o Mecanismo de Banco de Dados do SQL Server gera um trabalhador para executar a tarefa ativa (1:1). Ao executar solicitações paralelas no modo de linha, o Mecanismo de Banco de Dados do SQL Server atribui um trabalhador para coordenar os trabalhadores secundários responsáveis pela conclusão das tarefas atribuídas a eles (também 1:1), chamado de thread pai (ou thread de coordenação). O thread pai tem uma tarefa pai associada a ele. O thread pai é o ponto de entrada da solicitação e existe mesmo antes que o mecanismo analise uma consulta. As principais responsabilidades do thread pai são:

• Coordenar um exame paralelo.
• Começar a trabalhar paralelamente com os trabalhadores filho.
• Coletar linhas de threads paralelos e enviar ao cliente.
• Executar agregações locais e globais.

O número de threads de trabalho gerados para cada tarefa depende:

• Se a solicitação foi qualificada para paralelismo, conforme determinado pelo Otimizador de Consulta.

• Qual é o grau real de paralelismo disponível (DOP) no sistema, com base na carga atual. Isso pode ser diferente do DOP estimado, que se baseia na configuração do servidor para obter o MAXDOP (grau máximo de paralelismo). Por exemplo, a configuração do servidor para MAXDOP pode ser 8, mas o DOP disponível no tempo de execução pode ser apenas 2, o que afeta o desempenho da consulta. A demanda de memória e a ausência de trabalhadores são duas condições que reduzem o DOP disponível no tempo de execução.

Agendadores

Um agendador, também conhecido como Agendador SOS, gerencia threads de trabalho que exigem tempo de processamento para realizar trabalho em nome das tarefas. Cada agendador é mapeado para um CPU (processador individual). O tempo que um trabalho pode permanecer ativo em um agendador é chamado de quantum do sistema operacional, com um máximo de 4 ms. Depois que o tempo do quantum expirar, um trabalho dedica seu tempo para outros trabalhos que precisam acessar os recursos da CPU e altera seu estado. Essa cooperação entre os trabalhos para maximizar o acesso aos recursos da CPU é chamada de agendamento cooperativo, também conhecido como agendamento não preemptivo. Por sua vez, a alteração no estado de trabalho é propagada para a tarefa associada a esse trabalho e para a solicitação associada à tarefa. Para sabe mais sobre os estados de trabalho, confira sys.dm_os_workers. Para obter mais informações sobre agendadores, consulte sys.dm_os_schedulers.

Em resumo, uma solicitação poderá gerar uma ou mais tarefas para concluir unidades de trabalho. Cada tarefa será atribuída a um thread de trabalho responsável pela conclusão da tarefa. Cada thread de trabalho deverá ter um agendamento (feito em um agendador) para obter uma execução ativa da tarefa.

Considere o cenário a seguir.

• O Trabalho 1 é uma tarefa de execução longa, por exemplo, uma consulta de leitura usando leitura antecipada em tabelas baseadas em disco. O Trabalho 1 descobre que suas páginas de dados necessárias já estão no Pool de Buffers, portanto, ele não precisa esperar por operações de E/S e pode consumir seu quantum completo antes de ser suspenso.
• O Trabalho 2 está executando tarefas mais curtas, de menos de milissegundos, e precisa ser suspenso antes que seu quantum completo seja esgotado.

Nesse cenário e até o SQL Server 2014 (12.x), o Worker 1 pode basicamente monopolizar o agendador por ter mais tempo de quantum geral.

A partir do SQL Server 2016 (13.x), o agendamento cooperativo inclui o agendamento LDF (Large Deficit First). Com o agendamento LDF, os padrões de uso do quantum são monitorados e um thread de trabalho não monopoliza um agendador. No mesmo cenário, o Trabalhador 2 tem permissão para consumir quantums repetidos antes que o Trabalhador 1 tenha permissão para mais quantum, evitando assim que o Trabalhador 1 monopolize o agendador em um padrão hostil.

Agendamento de tarefas paralelas

Imagine um SQL Server configurado com MaxDOP 8, e a CPU Affinity está configurada para 24 CPUs (agendadores) entre os nós NUMA 0 e 1. Os agendadores de 0 a 11 pertencerão ao nó NUMA 0, os agendadores de 12 a 23 pertencerão ao nó NUMA 1. Um aplicativo envia a seguinte consulta (solicitação) ao Mecanismo de Banco de Dados:

SELECT h.SalesOrderID,
    h.OrderDate,
    h.DueDate,
    h.ShipDate
FROM Sales.SalesOrderHeaderBulk AS h
INNER JOIN Sales.SalesOrderDetailBulk AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE (h.OrderDate >= '2014-3-28 00:00:00');

O plano de execução mostra uma junção hash entre duas tabelas e cada um dos operadores executados em paralelo, conforme indicado pelo círculo amarelo com duas setas. Cada operador do paralelismo será uma ramificação diferente no plano. Portanto, há três ramificações no plano de execução a seguir.

Embora haja três ramificações no plano de execução, somente duas poderão ser executadas simultaneamente e a qualquer momento durante este plano de execução:

1. A ramificação em que um Exame de índice clusterizado é usado no Sales.SalesOrderHeaderBulk (entrada de build da junção) é executada sozinha.
2. A ramificação em que um Exame de Índice Clusterizado é usado no Sales.SalesOrderDetailBulk (entrada de investigação da junção) executa simultaneamente com a ramificação em que o Bitmap foi criado e, atualmente, a Correspondência de Hash está sendo executada.

O plano de execução XML mostra que 16 threads de trabalho foram reservados e usados no nó NUMA 0:

<ThreadStat Branches="2" UsedThreads="16">
  <ThreadReservation NodeId="0" ReservedThreads="16" />
</ThreadStat>

A reserva de threads garante que o Mecanismo de Banco de Dados tenha threads de trabalho suficientes para executar todas as tarefas necessárias para a solicitação. Os threads podem ser reservados em vários nós NUMA ou em apenas um nó NUMA. A reserva de threads é feita no runtime antes do início da execução e depende do carregamento do agendador. O número de threads de trabalho reservados é derivado genericamente da fórmula concurrent branches * runtime DOP e exclui o thread de trabalho pai. Cada ramificação é limitada a um número de threads de trabalho igual ao MaxDOP. Neste exemplo, há duas ramificações simultâneas, e MaxDOP está definido como 8, portanto, 2 * 8 = 16.

Para referência, observe o plano de execução dinâmico das Estatísticas de Consultas Dinâmicas, em que uma ramificação foi concluída e duas ramificações estão sendo executadas simultaneamente.

O Mecanismo de Banco de Dados do SQL Server atribui um thread de trabalho para executar uma tarefa ativa (1:1), que pode ser observada durante a execução da consulta, consultando a DMV sys.dm_os_tasks, conforme o exemplo a seguir:

SELECT parent_task_address, task_address,
       task_state, scheduler_id, worker_address
FROM sys.dm_os_tasks
WHERE session_id = <insert_session_id>
ORDER BY parent_task_address, scheduler_id;

Veja a seguir o conjunto de resultados. Observe que há 17 tarefas ativas para as ramificações que estão sendo executadas no momento: 16 tarefas secundárias correspondentes aos threads reservados, mais a tarefa principal ou tarefa de coordenação.

Observe que cada uma das 16 tarefas filho tem um thread de trabalho diferente atribuído (visto na coluna worker_address), porém todos os trabalhos serão atribuídos ao mesmo pool de oito agendadores (0, 5, 6, 7, 8, 9, 10 e 11) e a tarefa pai será atribuída a um agendador fora desse pool (3).

Um thread de trabalho só pode permanecer ativo no escalonador durante seu quantum (4 ms) e deve ceder seu escalonador após o término desse quantum para que um thread de trabalho atribuído a outra tarefa possa se tornar ativo. Quando o quantum de um trabalhador expira e não está mais ativo, a respectiva tarefa é colocada em uma fila FIFO em um estado RUNNABLE (executável) até que seja movida para um estado RUNNING (em execução) novamente, supondo que a tarefa não exija acesso a recursos que não estejam disponíveis no momento, como uma trava ou um bloqueio, caso em que a tarefa seria colocada em um estado SUSPENDED (suspenso) em vez de RUNNABLE (executável) até que esses recursos estejam disponíveis.

Em resumo, uma solicitação paralela gera várias tarefas. Cada tarefa deve ser atribuída a um só thread de trabalho. Cada thread de trabalho deve ser atribuído a um só agendador. Portanto, o número de agendadores em uso não pode exceder o número de tarefas paralelas por ramificação, que é definido pela configuração do MaxDOP ou pela dica de consulta. A linha de coordenação não contribui para o limite de MaxDOP.

Alocação de threads para CPUs

Por padrão, cada instância do SQL Server inicia cada thread, e o sistema operacional distribui os threads das instâncias do SQL Server entre os processadores (CPUs) em um computador, com base na carga. Se a afinidade do processo tiver sido habilitada no nível do sistema operacional, este atribuirá cada thread a uma CPU específica. Por outro lado, o Mecanismo de Banco de Dados do SQL Server atribui os threads de trabalho do SQL Server a agendadores que distribuem os threads uniformemente entre as CPUs em um formato circular.

Para multitarefa, por exemplo quando vários aplicativos acessam o mesmo conjunto de CPUs, às vezes, o sistema operacional move threads de trabalho entre diferentes CPUs. Embora seja eficiente de um ponto de vista de sistema operacional, essa atividade pode reduzir o desempenho do SQL Server sob cargas de sistema pesadas, à medida que cada cache do processador é recarregado repetidamente com os dados. Atribuir CPUs a threads específicos poderá melhorar o desempenho nessas condições eliminando recargas de processador e reduzindo a migração de thread entre CPUs (reduzindo portanto a alternância de contexto); tal associação entre um thread e um processador é chamada de afinidade do processador. Se a afinidade foi habilitada, o sistema operacional atribuirá cada thread a uma CPU específica.

A opção de máscara de afinidade é definida usando ALTER SERVER CONFIGURATION. Quando a máscara de afinidade não é definida, a instância do SQL Server aloca os threads de trabalho uniformemente entre os agendadores que não foram mascarados.

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.

Uso da opção de agrupamento leve

A sobrecarga envolvida na troca de contextos de thread pode não ser muito grande. A maioria das instâncias do SQL Server não vê nenhuma diferença de desempenho entre definir a opção de agrupamento leve como 0 ou 1. As únicas instâncias do SQL Server que podem obter benefícios de lightweight pooling são aquelas executadas em um computador que tem as seguintes características:

• Um servidor grande com várias CPUs
• Todas as CPUs executadas em capacidade próxima à máxima
• Há um nível alto de troca de contexto

Esses sistemas poderão observar um pequeno aumento no desempenho se o valor lightweight pooling for definido como 1.

Execução de linhas e fibras

O Microsoft Windows usa um sistema de prioridade numérica que varia de 1 a 31 para agendar threads para execução. Zero é reservado para uso do sistema operacional. Quando vários threads estão esperando para serem executados, o Windows despacha o thread com a prioridade mais alta.

Por padrão, cada instância do SQL Server tem prioridade 7, conhecida como a prioridade normal. Esse padrão fornece aos threads do SQL Server uma prioridade alta o bastante para obter recursos de CPU suficientes sem prejudicar outros aplicativos.

A opção de configuração aumento de prioridade pode ser usada para aumentar a prioridade dos threads de uma instância do SQL Server para 13. Isso é conhecido como prioridade alta. Essa configuração fornece aos threads do SQL Server uma prioridade mais alta do que a maioria dos outros aplicativos. Assim, os threads do SQL Server geralmente serão despachados sempre que estiverem prontos para serem executados e não forem antecipados por threads de outros aplicativos. Isso pode melhorar o desempenho quando um servidor estiver executando apenas instâncias do SQL Server e nenhum outro aplicativo. No entanto, se uma operação com uso intensivo de memória ocorrer no SQL Server, é provável que outros aplicativos não tenham uma prioridade alta o suficiente para antecipar o thread do SQL Server.

Se você estiver executando várias instâncias do SQL Server em um computador, e aumentar a prioridade apenas para algumas das instâncias, o desempenho de qualquer instância que estiver sendo executada na prioridade normal poderá ser prejudicado. Além disso, o desempenho de outros aplicativos e componentes no servidor poderá piorar se o aumento de prioridade for ativado. Portanto, ele só deveria ser usado em condições estritamente controladas.

Inclusão de CPU a quente

Inclusão de CPU a quente é a capacidade de adicionar dinamicamente CPUs a um sistema em execução. A inclusão de CPUs pode ocorrer fisicamente, pela adição de um novo hardware; logicamente, pelo particionamento do hardware online; ou virtualmente, através de uma camada de virtualização. O SQL Server oferece suporte para CPU de adição a quente.

Requisitos para a inclusão de CPU a quente:

• Requer hardware que ofereça suporte à inclusão de CPU a quente.
• Requer uma versão compatível do Windows Server Datacenter ou Enterprise Edition. A partir do Windows Server 2012, a adição a quente é compatível com a edição Standard.
• Requer a edição Enterprise do SQL Server.
• O SQL Server não pode ser configurado para usar soft NUMA. Para saber mais sobre soft-NUMA, veja Soft-NUMA (SQL Server).

O SQL Server não usa automaticamente as CPUs depois que elas são adicionadas. Isso impede o SQL Server de usar CPUs que possam ser incluídas para algum outro propósito. Depois de adicionar CPUs, execute a instrução RECONFIGURE de forma que o SQL Server reconheça as novas CPUs como recursos disponíveis.

Práticas recomendadas para executar o SQL Server em computadores com mais de 64 CPUs

Atribuição de threads de hardware a CPUs

Não use as opções de configuração do servidor affinity mask e affinity64 mask para vincular processadores a threads específicos. Essas opções são limitadas a 64 CPUs. Em vez disso, use a opção SET PROCESS AFFINITY de ALTER SERVER CONFIGURATION.

Gerenciamento do tamanho do arquivo de log de transações

Não confie no aumento automático para aumentar o tamanho do arquivo de log de transações. O aumento do log de transação deve ser um processo serial. A extensão do log pode impedir a continuação de operações de gravação de transação até que a extensão de log seja concluída. Em vez disso, pré-aloque espaço para os arquivos de log definindo o tamanho de arquivo para um valor grande o bastante para oferecer suporte à carga de trabalho comum no ambiente.

Definir o grau máximo de paralelismo para operações de índice

O desempenho de operações de índice, como criar ou recompilar índices, pode ser melhorado em computadores com muitas CPUs definindo-se temporariamente o modelo de recuperação do banco de dados como bulk-logged ou simples. Essas operações de índice podem gerar atividade de log significativa, e a contenção de log pode afetar a melhor opção de DOP (grau de paralelismo) feita pelo SQL Server.

Além de ajustar a opção de configuração de servidor de MAXDOP (grau máximo de paralelismo), considere ajustar o paralelismo para operações de índice usando a opção MAXDOP. Para obter mais informações, consulte Configurar operações de índice paralelo. Para obter mais informações e diretrizes sobre como ajustar a opção de configuração do servidor de grau máximo de paralelismo, consulte Definir a opção de configuração do servidor de grau máximo de paralelismo.

Opção de número máximo de threads de trabalho

O SQL Server configura dinamicamente a opção de configuração do servidor threads de trabalho máximos na inicialização. O SQL Server usa o número de CPUs disponíveis e a arquitetura do sistema para determinar essa configuração de servidor durante a inicialização, usando uma fórmula documentada.

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 provável é que algo, como a E/S, está fazendo com que os threads de trabalho aguardem. É melhor localizar a causa raiz de um problema de desempenho antes de alterar a configuração max worker threads. No entanto, se você precisar definir manualmente o número máximo de threads de trabalho, esse valor de configuração sempre deve ser definido como no mínimo sete vezes o número de CPUs presentes no sistema. Para saber mais, confira Configurar o máximo de threads de trabalho.

Evite o uso do Rastreamento do SQL e do SQL Server Profiler

Recomendamos que você não use o Rastreamento do SQL e o SQL Profiler em um ambiente de produção. A sobrecarga para a execução dessas ferramentas também aumenta à medida que o número de CPUs cresce. Se você usar o Rastreamento do SQL em um ambiente de produção, limite o número de eventos de rastreamento a um nível mínimo. Crie perfis e teste cuidadosamente cada evento de rastreamento sob carga e evite usar combinações de eventos que afetem o desempenho de modo significativo.

Definir o número de arquivos de dados tempdb

O número de arquivos depende do número de processadores (lógicos) do computador. Como regra geral, se o número de processadores lógicos for menor ou igual a oito, use o mesmo número de processadores lógicos para os arquivos de dados. Se o número de processadores lógicos for maior que oito, use oito arquivos de dados e, se a contenção persistir, aumente o número de arquivos de dados em múltiplos de quatro até que a contenção seja reduzida a níveis aceitáveis ou faça alterações no código/carga de trabalho. Lembre-se também de outras recomendações para tempdb, disponíveis em Otimização do desempenho de tempdb no SQL Server.

Entretanto, ao considerar cuidadosamente as necessidades de simultaneidade de tempdb, você pode reduzir a sobrecarga do gerenciamento do banco de dados. Por exemplo, se um sistema tiver 64 CPUs e, normalmente, apenas 32 consultas usarem tempdb, aumentar o número de arquivos tempdb para 64 não melhorará o desempenho.

Componentes do SQL Server que podem usar mais de 64 CPUs

A tabela a seguir lista os componentes do SQL Server e indica se eles podem usar mais de 64 CPUs.