Na arquitetura do SQL Server, um dos grandes componentes é o SQLOS (Sistema Operacional do SQL Server), que tem por objetivo lidar com os recursos que podem ser considerados de responsabilidade do sistema operacional, como o gerenciamento de memória, detecção de Deadlock, sincronização de objetos, entre outros. O SQLOS está presente em uma camada entre o SQL Server e o Windows, tendo como seu serviço principal o Scheduling (Agendamento).
Modelo de Execução
Para entendermos conceitualmente o Scheduling dentro do SQLOS, será necessário iniciarmos pelo modelo de execução utilizado pelo SQL Server.
Quando uma aplicação faz uma autenticação no SQL Server, é estabelecida uma conexão no contexto de uma sessão, na qual podemos identificar pelo Session_id ou pelo SPID. Um SPID é uma conexão ou canal pelo qual as requisições podem ser enviadas.
Uma vez que é feito uma requisição para a sessão criada, o SQL Server divide seu trabalho em uma ou mais tarefas (Tasks) e, em seguida, associa um Worker Thread para cada tarefa ao longo de sua duração. O Worker Thread é uma representação lógica do SQL Server para uma Thread.
Cada Thread tem um Scheduler associado, que tem a função de agendar um tempo no processador para cada uma das Threads. O número Schedulers disponíveis no SQL Server é igual ao número de processadores lógicos que o SQL Server pode utilizar adicionado a mais um, para o DAC (Dedicated Administrator Connection). Veja exemplo na Figura 1.
Figura 1. Relacionamento entre a sessão, tarefa, Thread, Scheduler e CPU Lógico.
Scheduling
Antes do SQL Server 7.0, o Scheduling dependia totalmente do sistema operacional Windows. Embora isso signifique que o SQL Server irá tirar vantagem do Windows para melhorar a escalabilidade e uso eficiente do processador, existem limites definidos. Isso porque o Scheduler do Windows não sabe nada sobre as necessidades de um sistema de banco de dados relacional, ele trata as Worker Threads do SQL Server da mesma maneira que outro processo qualquer que roda no sistema operacional. No entanto, um sistema de alto desempenho como o SQL Server funciona melhor quando um Scheduler pode atender suas necessidades especiais. SQL Server 7.0 e todas as versões subsequentes foram projetados para lidar com seu próprio Scheduler e ganhar diversas vantagens.
Nas versões do SQL Server 7.0 e 2000 os Schedulers eram chamados de User Mode Scheduler (UMS) para refletir o que ocorreu primeiramente no modo de usuário, ao contrário do modo Kernel. Da versão do SQL Server 2005 em frente, foi chamado de SOS Scheduler e melhorou o UMS ainda mais.
No SQL Server 2012 cada CPU tem um Scheduler criado para ele quando o SQL Server é iniciado. Esse processo ocorre mesmo se a configuração de Affinity Mask estiver em uso para determinar quais CPUs físicos o SQL Server utilizará. Baseando-se na configuração do Affinity Mask, cada Scheduler pode ter os status de Online e Offline, por padrão todos os Schedulers estão Online e as exceções são poucas.
Cada Scheduler é identificado por seu único Scheduler_Id. Valores entre 0 e 254 são reservados para os Schedulers que estão rodando requisições de usuários. Schedulers com o Scheduler_Id maiores 255 são reservados para o uso do Sistema e também para o Dedicated Administrator Connection, tipicamente são atribuídos a mesma tarefa.
A consulta da Listagem 1 mostra algumas informações importantes da DMV Sys.dm_os_schedulers. Veja na Figura 2 o resultado.
Listagem 1. Código da consulta na DVM sys.dm_os_schedulers.
select parent_node_id, scheduler_id, cpu_id, status, scheduler_address
from sys.dm_os_schedulers
order by scheduler_id
Figura 2. Retorno da consulta na DVM sys.dm_os_schedulers.
Os resultados acima mostram quatro linhas com o Scheduler_Id menor que 255, ou seja, os resultados são de uma máquina com quatro processadores lógicos (Cores). Também é possível de ser visto um Scheduler_Id com valor de 1048576 que tem o Status de Visible Online (DAC), indicando que é utilizado pelo Dedicated Administrator Connection. Os Schedulers_Id maiores que 255 são reservados para o uso do sistema.
Windows Scheduler
Windows é um sistema operacional de propósito geral e não é otimizado para aplicações baseadas em servidor, em particular o SQL Server. Em vez disso, o objetivo da equipe de desenvolvimento do Windows é garantir que todas as aplicações irão funcionar corretamente e ter um bom desempenho. Isso por que o Windows precisa trabalhar bem um com uma vasta gama de cenários e a equipe de desenvolvimento não irá fazer nada de especial que possa apenas ser utilizada por menos de 1% das aplicações. Por exemplo, o Scheduler no Windows é muito básico para assegurar que é adequado para uma causa comum.
Otimizando o caminho que as Threads são escolhidas para execução sempre vai ser limitado por causa desse objetivo amplo de desempenho, mas se uma aplicação faz seu próprio Scheduler, então não há mais inteligência sobre a próxima escolha, como a atribuição de mais Threads com prioridades maiores ou decidindo uma Thread para execução que irá evitar que as outras Threads sejam bloqueadas posteriormente.
O Scheduler básico do Windows é conhecido como Scheduler Preemptivo e ele atribui fatias de tempo conhecidos como Quantums para cada tarefa a ser executada. A vantagem disso é que os desenvolvedores não tem que se preocupar com o Scheduler quando criam as aplicações, a desvantagem é que a execução pode ser interrompida a qualquer momento enquanto o Windows balanceia as requisições de execução de múltiplos processos.
Todas as versões do SQL Server usam o Windows Scheduler para tirar vantagem do trabalho que o time de Windows tem feito ao longo da história para otimização do uso do processador.
Task
Uma Task é uma requisição para fazer alguma unidade de trabalho. A Task por si só não faz nada, é apenas um contêiner para que uma unidade de trabalho seja concluída. Para realmente fazer algo, a Task precisa ser agendada em um dos Schedulers e associada a um Worker.
A consulta da Listagem 2 mostra algumas informações importantes da DMV Sys.dm_os_task. Veja na Figura 3 mostra o retorno da consulta.
Listagem 2. Código da consulta na DVM sys.dm_os_tasks.
Select * from sys.dm_os_tasks
Figura 3. Retorno da consulta na DVM sys.dm_os_tasks.
Uma Task é um contêiner de trabalho que está sendo feito, mas ao observar a DMV acima, não existe indicação de qual trabalho exatamente é feito. Para descobrir o que cada Task realmente está fazendo requer uma consulta mais elaborada, como mostra a Listagem 3. Veja o resultado na Figura 4.
Listagem 3. Código da consulta elaborada.
Select t.task_address, s.text
From sys.dm_os_tasks as t inner join sys.dm_exec_requests as r
on t.task_address = r.task_address
Cross apply sys.dm_exec_sql_text (r.plan_handle) as s
where r.plan_handle is not null
Figura 4. Retorno da consulta elaborada.
Workers
É possível pensarmos no SQL Server Scheduler como um CPU lógico usado pelo SQL Server Workers. Um Worker pode ser uma Thread ou Fibra ligados a um Scheduler lógico. Se o Affinity Mask estiver configurado, cada Scheduler é mapeado para um determinado CPU, sendo assim, cada Worker também é associado a um único CPU. Cada Scheduler é associado a um Worker que tem seu limite configurado com base na opção de Max Worker Threads e número de Schedulers. Os Schedulers são responsáveis por criarem ou destruírem os Workers conforme necessário. Um Worker não pode se mover de um Scheduler para outro, mas como podem ser destruídos e criados, é possível que eles apareçam como se estivessem se movendo entre os Schedulers.
Os Workers são criados quando o Scheduler recebe uma requisição e quando não existem Workers ociosos, sendo que, podem ser destruídos caso esteja ocioso por pelo menos 15 minutos ou se o SQL Server estiver sob pressão de memória. Cada Worker pode utilizar pelo menos meio megabyte de memória em um ambiente 32-bit ou até 2Mb em 64 Bit, sendo assim, destruindo os Workers irá liberar memória e imediatamente melhorar o desempenho do ambiente.
O SQL Server lida com os Workers de maneira muito eficiente e mesmo em ambientes muito grandes com centenas de usuários, o atual número de Workers pode ser muito mais baixo do que o valor configurado para a configuração de Max Worker Threads.
A consulta da Listagem 4 mostra algumas informações importantes da DMV Sys.dm_os_workers. Veja o resultado na Figura 5.
Listagem 4. Código da consulta na DVM sys.dm_os_workers.
Select Task_address, State, Last_wait_type, Scheduler_address From sys.dm_os_workers
Figura 5. Retorno da consulta na DVM sys.dm_os_workers.
As colunas mais interessantes para observamos são as seguintes:
- Task_address – Possibilita a ligação com a Task;
- State – Mostra o estado atual do Worker;
- Last_wait_type – Mostra o último Wait Type que aquele Worker está esperando;
- Scheduler_address – Possibilita a ligação com os Schedulers.
Thread
Complementando o modelo de execução mostrado inicialmente, o SQLOS também contém objetos para as Threads do sistema operacional que está usando.
A consulta da Listagem 5 mostra algumas informações importantes da DMV Sys.dm_os_threads e podemos ver o resultado da Figura 6.
Listagem 5. Código da consulta na DVM sys.dm_os_threads.
Select Scheduler_address, Worker_address, Kernel_time, Usermode_time From sys.dm_os_threads
Figura 6. Retorno da consulta na DVM sys.dm_os_threads.
As colunas mais interessantes para observamos são as seguintes:
- Scheduler_address – Endereço do Scheduler com que a Thread está associada;
- Worker_address – Endereço do atual Worker associado a Thread;
- Kernel_time – Quantidade de tempo de Kernel que uma Thread usa desde que foi iniciada;
- Usermode_time – Quantidade de tempo de usuário que uma Thread tem usado desde que foi iniciada.
NUMA
Non-Uniform Memory Architecture (NUMA) é um modelo de hardware que aumenta a escalabilidade do servidor, pois elimina os gargalos na placa-mãe. Em um sistema NUMA, cada processador tem sua controladora de memória e conexão direta a um grupo dedicado de RAM, a que se refere como memória local e juntas são representadas como um nó (Node) NUMA.
Com a configuração NUMA, cada nó tem um subgrupo de processadores e o mesmo número de Schedulers. Se a máquina está configurada para utilizar hardware NUMA, o número de processadores em cada nó é pré-definido, mas para Soft-NUMA que foi configurado a si mesmo, você mesmo pode decidir quantos processadores são atribuídos para cada nó. Você ainda tem o mesmo número de Schedulers quanto processadores, entretanto, quando os SPIDs são inicialmente criados, eles atribuem Round-Robin (Algoritmo de agendamento de processos no sistema operacional) aos nós.
Dynamic Affinity
No SQL Server 2012, o Processor Affinity pode ser controlado dinamicamente. Quando o SQL Server inicia, todos os Schedulers Tasks são iniciados, de modo que cada CPU tem seu Scheduler. Se o Process Affinity foi configurado, alguns Schedulers são marcados como Offline e nenhuma outra tarefa é atribuída a eles.
Quando o Process Affinity é alterado para incluir CPUs adicionais, a nova CPU é colocada Online. O Scheduler Monitor então percebe que ocorreu um desequilíbrio na carga de trabalho e começa a escolher os Workers para atribuir à nova CPU. Quando uma CPU é colocada Offline pela mudança do Process Affinity, o Scheduler daquela CPU continua trabalhando com os Workers ativos, mas o próprio Scheduler é movido para um dos outros CPUs que estão Online. Nenhum novo Worker é atribuído ao Scheduler que está Offline e quando todos os Workers ativos terminarem terminar suas tarefas, o Scheduler para.
Atribuindo Schedulers a CPU
Normalmente os Schedulers não são atribuídos a CPUs em uma rígida relação de um para um, mesmo quando o número de Schedulers é igual ao de CPUs. O Scheduler é atribuído ao CPU apenas quando o Process Afinity está configurado, mesmo que seja especificado que o Process Afinity utilize todos os CPUs, o que é a configuração padrão. Por exemplo, o valor padrão para o Process Afinity é Auto, que significa que serão utilizados todos os CPUs. Em alguns casos quando o servidor está sofrendo com cargas altas, o Windows pode trabalhar com dois Schedulers em um CPU.
Em algumas situações pode ser preferível poder limitar o número de CPUs disponíveis, mas não atribuir um Scheduler particular a um único CPU. Supondo que tenhamos uma máquina com 64 processadores que está rodando oito instâncias do SQL Server e você quer que cada instância use os oito processadores. Cada instância tem um Process Affinity diferente que especifica um grupo distinto de 64 processadores, isso por que após o Process Affinity ser configurado, cada instância tem uma ligação do Scheduler com o CPU. Caso deseje limitar o número de CPUs, mas ainda não querendo que um determinado Scheduler rode em um especifico CPU, é possível iniciar o SQL Server com a Trace Flag 8002. Essa Trace Flag permite que tenhamos CPUs mapeados para uma instância, mas dentro das instâncias os Schedulers não são atribuídos a CPUS.
Identificando Gargalos
Através do conhecimento obtido a respeito dos Schedulers, podemos nos basear nas informações da DMV sys.dm_os_schedulers para identificar pressão de CPU.
Provavelmente o Wait Type chamado Sos_Scheduler_Yield já foi visto em alguma análise de desempenho já feita no seu ambiente do SQL Server. O Books Online da Microsoft o descreve como evento que ocorre quando uma Task cede o Scheduler para a execução de outras Tasks.Durante essa espera, a Task está esperando que seu Quantum seja renovado.
De forma mais simples, o Sos_Scheduler_Yield é um comum Wait Type e ocorre quando existe uma pressão de CPU. O SQL Server executa múltiplas Threads e tenta permitir que todas as Threads executem sem problemas. Entretanto, se todas as Threads estão ocupadas em cada Scheduler e não podem deixar outras Threads executarem, ela própria irá se ceder para outra Thread, dessa forma criando o Wait Type Sos_Scheduler_Yield.
Para fazer a análise dos maiores Waits Stats no ambiente utilizaremos a consulta criada pelo Tim Ford e Glenn Berry, como mostra a Listagem 6. O resultado pode ser visto na Figura 7.
Listagem 6. Código da consulta com os maiores Wait Stats do ambiente.
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
NOT IN
('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
) – Filtro para elimitar Waits irrelevantes para essa analise
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn,
W1.wait_type,
W1.wait_time_s,
W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- Limite de porcentagem
Figura 7. Retorno da consulta com os maiores Wait Stats do ambiente.
Após executar essa consulta podemos observar que apenas 2,2% dos Waits presentes no ambiente são devido a pressão de CPU, podendo concluir que esta é uma das causas para um desempenho ruim.
Uma das maneiras para se resolver essa pressão de CPU é utilizando a DVM Sys.dm_os_schedulers. Como essa DMV retorna informações como, número de Workers, Tasks ativas, status de cada Scheduler e outros, podem nos ajudar a identificar certos problemas, mas a informação mais importante está na coluna que conta a fila de Tasks, a Runnable_task_count. Essa coluna exibe o número de Tasks, com tarefas atribuídas a eles, que estão esperando por outras Tasks, resultando no Sos_Scheduler_Yield Wait Type. Caso o valor para o Runnable_task_count frequentemente seja maior que zero, então pode existir uma pressão de CPU e bloqueios podem ocorrer.
Para ver a média das Tasks atuais e suas esperas, podemos utilizar a consulta da Listagem 7, com resultado apresentado na Figura 8.
Listagem 7. Código da consulta com as Tasks existentes.
SELECT AVG(current_tasks_count) AS [Avg Current Task],
AVG(runnable_tasks_count) AS [Avg Wait Task]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
AND status = 'VISIBLE ONLINE'
Figura 8. Retorno da consulta com as Tasks existentes.
Outra consulta útil (Listagem 8) nos retorna as Queries em execução e seu Scheduler associado no qual estão rodando. Podemos ver o resultado na Figura 9.
Listagem 8. Código da consulta com os Schedulers para cada Query em execução.
SELECT
a.scheduler_id ,
b.session_id,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement
FROM sys.dm_os_schedulers a
INNER JOIN sys.dm_os_tasks b on a.active_worker_address = b.worker_address
INNER JOIN sys.dm_exec_requests c on b.task_address = c.task_address
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS s2 
Figura 9. Retorno da consulta com os Schedulers para cada Query em execução.
A solução mais fácil a ser pensada para problemas com pressão de CPU provavelmente será adicionar mais CPU ao servidor, mas essa pode não ser a melhor solução. Otimizar consultas pesadas também pode ajudar muito a reduzir o número de Runnable_task_count. Por exemplo, se você tem um desempenho ruim com seus índices nas consultas, adicionar mais CPU não vai resolver nada.
Uma vez que você já otimizou o seu ambiente e mesmo assim continua encontrando o valor maior que zero para o Runnable_task_count, agora sim terá um bom argumento para adicionar mais CPU. Lembre-se que a escolha da quantidade de CPU é uma decisão que o DBA deve fazer baseado em uma Baseline e os resultados capturados desde então.
Existem outras maneiras de identificar pressão de CPU, como a utilização do Performance Monitor, SQL Profiler e outros, mas utilizar DVMs pode ser a escolha mais rápida e leve dependendo da situação.
