Então você possui um servidor SQL Server com aplicativos instalados e funcionando e não entende por que ele se comporta lentamente. Como resolver esse problema? O que pode estar acontecendo? Espero que este artigo possa auxiliá-lo a responder algumas perguntas para começar a identificar os problemas você mesmo ou então indicar um caminho para iniciar a busca por uma resposta e aumentar o seu arsenal de conhecimentos.

Como o SQL Server funciona?

Para estar estar apto a resolver problemas de desempenho do SQL Server, você precisa entender como ele funciona. Em uma explicação mais grosseira, o SQL Server executa suas consultas da seguinte forma:

• O aplicativo envia uma requisição ao servidor, contendo o nome de uma stored procedure, ou alguma instrução SQL.
• A requisição é armazenada em uma fila na memória do SQL Server
• Uma thread livre do SQL Server é utilizada em um pool de requisições, compilando-a e executando-a.
• A requisição é executada instrução por instrução, sequencialmente. Uma instrução na requisição sempre deve terminar antes que a próxima inicie. As stored procedures são executadas da mesma forma, instrução por instrução.
• As instruções podem ler ou modificar dados. Todos os dados são lidos em cache de memória no banco de dados (em um pool de buffer). Se o dado não estiver em cache, deverá ser lido a partir do disco para o cache de memória. Todas as atualizações serão gravadas no log do banco de dados dentro do cache de memória (no pool do buffer), de acordo com o Protocolo de Log de Escrita e Gravação de Dados.
• Travas de edição de dados não devem ser concorrentes.
• Quando todas as instruções em uma requisição forem executadas, a thread torna-se livre para ser utilizada por outra requisição.

Para uma explicação mais detalhada, leia o documento Entendendo como o SQL Server executa uma consulta.

Isso pode soar trivial, mas entender quais requisições estão sendo executadas e quais estão em espera por algum motivo (suspensas) são a chave para a resolução de problemas de desempenho do SQL Server.

Se uma requisição enviada para o SQL Server está levando um tempo maior do que o esperado para retornar resultados, então está levando um longo tempo para ser executada ou está em espera. Saber qual desses é o problema é crucial para entender problemas de desempenho do servidor. Adicionalmente, se alguma requisição está demorando um longo tempo em estado suspenso, podemos procurar mais a fundo para verificar o que esses processos estão esperando e por quanto tempo isso tem acontecido.

Compreender um pedido de espera (suspensão)

Sempre que um pedido é suspenso, por qualquer motivo, o SQL Server irá coletar informações sobre os motivos pelos quais esse pedido foi foi suspenso e por quanto tempo. No código interno SQL Server, simplesmente não há maneira de chamar manualmente a função que suspende uma tarefa sem fornecer os dados necessários pelo pedido de espera. Esses dados são então recolhidos e disponibilizados para uso posterior de diversas maneiras. Essa informação acerca da espera de dados é crucial para determinar problemas de desempenho:

• Um pedido atualmente em execução e que está suspenso pode informar o que é que esse pedido espera e por quanto tempo ele tem permanecido em espera.
• Nos pedidos atualmente em execução, é possível ver qual é a última coisa que foi esperada.
• É possível entender quando as solicitações estão à espera de outras solicitações.
• Obter dados agregados para os pedidos que são os mais esperados por recursos (ocupados) em todo o servidor.
• Entender quais recursos físicos (hardware) estão saturados e causando problemas de desempenho.

Espere informações adicionais para pedidos atualmente em execução

Para cada pedido de execução feito ao servidor SQL Server existe uma linha registrada em sys.dm_exec_requests. Consultar esse documento a qualquer momento irá lhe fornecer uma visão rápida de tudo o que for executado logo em seguida. As colunas wait_type, wait_time e last_wait_type darão uma visão geral imediata dos processos que estão em execução (runnig) versus o que está esperando (wait), além do que está sendo esperado:

select session_id, 
	status, 
	command,
	blocking_session_id,
	wait_type, 
	wait_time,
	last_wait_type,
	wait_resource
from sys.dm_exec_requests
where r.session_id >= 50
and r.session_id <> @@spid;

• O pedido na session_id 53 é um SELECT que está no estado: suspenso. Está à espera de um bloqueio.
• Na session_id 54 existe um INSERT que está em execução, mas está esperando em uma trava de página.
• Na session_id 55 existe um INSERT que está atualmente em execução. Espera o destravamento de página anterior.
• A session_id 56 é um INSERT que está aguardando. Ele está esperando para registrar o banco de dados para ser liberado (está executando uma transação).
• A session_id 57 é um INSERT que está aguardando. Ele está esperando pelo log de banco de dados para continuar.

Note que o pedido da session_id 54 tem um status em execução, mas na verdade está aguardando. Isso se deve ao fato de que o tempo que o pedido deverá esperar será muito curto. O blocking_session_id também nos diz, para algumas das tarefas de espera, o motivo pelo qual outro pedido está segurando o recurso que atualmente está sendo aguardado. A session_id 53 está esperando pela session_id 56 por um recurso de bloqueio, o que significa que o SELECT está tentando ler um registro bloqueado pelo INSERT. O SELECT não será retomado até que o INSERT seja processado. A session_id 54 está esperando pela session_id 55 por uma trava de página, o que significa que a session_id 55 que possui o INSERT irá modificar essa página de dados no mesmo momento, e os dados na página serão instáveis e, dessa forma, não terão permissão para serem lidos. As session_id 56 e 57 estão aguardando, mas não há outra sessão a bloqueá-las. Elas estão esperando pelo registro do flush, o que significa que o servidor deverá garantir que seja registrado o log para que a operação seja gravada no disco. Os processos não vão continuar até que o controlador de disco reconheça que o log foi escrito.

Aqui está outro exemplo:

• O pedido na session_id 53 é um COMMIT que está aguardando. Ele está esperando pelo log do banco de dados para continuar.
• A session_id 54 é um SELECT que está aguardando. Ele está à espera de um bloqueio.
• A session_id 55 é um SELECT que está aguardando. Ele está à espera de um bloqueio.
• A session_id 56 é um SELECT que está aguardando. Ele está à espera de um bloqueio.
• A session_id 57 é um SELECT que está aguardando. Ele está à espera de um bloqueio.

Note que, nesse exemplo, cada pedido está realmente em espera. Nesse momento, o servidor não irá fazer basicamente nada. Temos 4 session_id (54, 55, 56 e 57) esperando no mesmo bloqueio (a linha com a chave de recurso de bloqueio: 5: 72057594039369728 (ac11a2bc89a9) é a mesma). A session_id 54 está esperando pela session_id 55 e a session_id 55 está esperando pela session_id 53, o que significa que a session_id 54 está esperando pela session_id 53. Então todas as session_id na verdade estão aguardando a session_id 53, que está aguardando o controlador de disco para gravar um registro de log. Também podemos ver, a partir da coluna wait_time, quanto tempo cada session_id esperou: cerca de 160 milissegundos. Observe, como no exemplo anterior, que tivemos apenas um pedido, mas, na verdade, estamos executando 5 pedidos. Estamos executando essas consultas em uma estação de trabalho com 8 núcleos de processamento, muita memória RAM e um disco decente, então há uma abundância de recursos de hardware para realizar esses pedidos, mas em vez disso eles são, na maioria das vezes, processos em espera, que ficam presos em vez de serem executados.

Ter a maioria (ou mesmo todos) os pedidos em espera, em vez de serem executados, é algo incomum? Não, geralmente isso é normal! Toda vez que você olhar para um servidor SQL Server executando uma carga, mesmo que moderada de dados, verá que a maioria dos pedidos está à espera, e apenas alguns estão em execução. O que você precisa é descobrir processos que demoram um longo tempo em espera ou que então aguardam períodos curtos, mas são repetidos e se somam. Longa espera muitas vezes indica algum recurso que é mantido por longos períodos e, normalmente, isso ocorre quando existem travas. Esperas curtas mas repetidas indicam um recurso que está saturado, possivelmente um ponto importante para a descoberta de problemas de desempenho.

Antes de ir mais longe, quero mostrar as tarefas de espera do sys.dm, que é um servidor SQL DMV especificamente projetado para mostrar tarefas atualmente à espera:

select * from sys.dm_os_waiting_tasks
where r.session_id >= 50
and r.session_id <> @@spid;

• A session_id 53 está esperando o registro para continuar.
• A session_id 57 está esperando 40 ms por um bloqueio em uma linha e também está bloqueada pela session_id 53 (que, portanto, deve ser a responsável pelo bloqueio).
• A session_id 54 está esperando 37 ms por um bloqueio em uma linha e está sendo bloqueada pela session_id 57 (mas a session_id 57, por sua vez está bloqueada pela session_id 53).

A situação que temos aqui é muito semelhante à anterior, na qual tivemos 4 session_id contendo uma instrução SELECT bloqueadas por um INSERT. É possível ver aqui duas solicitações bloqueadas por tentarem ler uma mesma linha (por isso elas são provavelmente um SELECT) e elas bloqueando a sessão que está à espera de que a transação anterior seja confirmada. As informações contidas nesse DMV são muito semelhantes às informações de sys.dm_exec_requests, já que aquele executado depois tem mais informações. Mas há um caso importante em que a informação contida em sys.dm_exec_requests é enganadora: as consultas paralelas.

Quando uma instrução pode se beneficiar da execução paralela, o motor do servidor vai criar várias tarefas para um mesmo pedido, cada um sendo subconjunto do processamento dos dados. Cada uma dessas tarefas pode ser executada em uma CPU/core separada. Para se comunicar com essas tarefas, o pedido usa basicamente uma fila de pedido-consumo. O operador de consulta a ser implementado nessa fila de processos é chamado de operador exchange (estou realmente simplificando as coisas aqui, leia O Paralelismo dos Operadores (Exchange) para obter informações mais precisas). Se essa fila de pedido-consumo está vazia (ou seja, os produtores não enviarão dados para ele), o consumidor deve suspender a ação e esperar o tipo de espera correspondente, que é o tipo CXPACKET. Pedidos que mostram esse tipo de espera estão realmente mostrando que as tarefas que deveriam ter produzido dados de consumo não estão produzindo quaisquer dados (ou dados suficientes). Essas tarefas de produtores, por sua vez, podem ser suspensas, esperando outro tipo de trava/espera, e é isso que está bloqueando seu pedido, não o operador exchange. Aqui está um exemplo:

select r.session_id, 
	status, 
	command,
	r.blocking_session_id,
	r.wait_type as [request_wait_type], 
	r.wait_time as [request_wait_time],
	t.wait_type as [task_wait_type],
	t.wait_duration_ms as [task_wait_time],
	t.blocking_session_id,
	t.resource_description
from sys.dm_exec_requests r
left join sys.dm_os_waiting_tasks t
	on r.session_id = t.session_id
where r.session_id >= 50
and r.session_id <> @@spid;

Aqui podemos ver que o pedido presente na session_id 54 tem três tarefas em espera, enquanto o wait_type mostra o CXPACKET, uma das tarefas paralelas filhas que, na verdade, estão à espera de um bloqueio de página.

No próximo artigo, vou abordar os recursos agregados de requisições em espera, seus tipos e o funcionamento dos logs e entradas e saídas de dados referentes a transações em espera e desempenho do banco de dados. Até lá!

***

Artigo traduzido pela Redação iMasters com autorização do autor. Publicado originalmente em http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/