O que é um Resource Semaphore?

Semana passada consegui um ótimo exemplo de um problema relacionado com a falta de memória. No caso, a lentidão do sistema estava relacionada com espera por recursos denominados “Resource Semaphore” – um problema comum para muitos sistemas.

Afinal, o que é um Resource Semaphore e como resolvê-lo?

Resposta Curta:

Resource Semaphore é um controle normal e interno do SQL Server, que é usado para evitar a sobrecarga de queries dentro do servidor. A resolução deste tipo de espera deve ser feita otimizando a query ou aumentando a quantidade de memória do servidor.

Resposta Longa:

A fim de explicar melhor o significado do Resource Semaphore, gostaria de dar um exemplo de uma situação real que enfrentei na semana passada.

Imagine o cenário de um servidor com 64GB de memória que recebe simultaneamente 8 queries para serem processadas. Após o processo de compilação, determina-se que será utilizado HASH JOIN para fazer o relacionamento entre tabelas.

As operações de HASH JOIN são muito custosas e dependem do tamanho da tabela. No nosso caso real, as tabelas apresentam milhões de registros (curiosidade: uma das tabelas tem 30 bilhões de linhas!)

O otimizador SQL apresenta a estimativa de que essa query utilizará 7GB de memória de espaço temporário para processar toda essa massa de dados.

Embora o servidor tenha 64GB de memória disponível, o Sistema Operacional utiliza por volta de 10GB. Assim, resta 54GB de memória para o SQL Server.

Pergunta: O que ocorre com o servidor SQL quando as 8 queries rodam simultaneamente? Considere que o servidor apresenta 54GB disponível e cada query utiliza 7GB.

Uma matemática simples (8 queries x 7GB = 56GB) diz que faltará memória para o SQL Server.

Resposta: Há um controle de concorrência dentro do SQL Server que evita múltiplos comandos executarem ao mesmo tempo, caso a soma de todos os recursos ultrapasse a memória disponível do SQL Server.

 

Monitorando o Servidor

Utilizaremos uma DMV chamada sys.dm_exec_query_memory_grants para acompanhar a utilização de memória.

 select * from sys.dm_exec_query_memory_grants

O resultado está apresentado (resumidamente) abaixo:

image

Interpretação:

  1. Somente as sessões que utilizam grande quantidade de memória estão listadas nessa DMV. Uma query bem otimizada não apareceria nessa view.
  2. Todas as requisições começaram por volta das 6:57 – 6:59, conforme mostra a coluna request_time.
  3. Todas as queries estão solicitando 7814936kb de memória (coluna requested_memory_kb) para uso temporário, memória denominada de Workspace = “Espaço de trabalho”.  Isso não corresponde a Buffer Cache ou Procedure Cache.
  4. Somente 4 queries conseguiram obter a memória necessária para iniciar o processamento, que são as sessões 74, 80, 89 e 75. Observe que a coluna grant_time corresponde ao “horário que o servidor concedeu memória”.
  5. As demais sessões (104, 111, 97, 90) estão esperando por memória. Enquanto isso, elas ficam suspensas até que haja recursos suficientes para rodá-las. Por isso, observamos que grant_time = NULL.

O passo 5 é exatamente o controle de concorrência do SQL Server: se não há recursos suficientes para rodar todas as queries simultaneamente, então algumas ficam enfileiradas esperando até que a carga no servidor diminua.

Durante esse momento, a sessão do usuário fica bloqueada por um RESOURCE_SEMAPHORE.

 

Como resolver?

A presença de RESOURCE_SEMAPHORE indica a falta de recurso no servidor para processar as requisições. Portanto, a resolução desse tipo de problema envolve:

  • Otimizar a query com o objetivo de diminuir o tamanho do Workspace a ser utilizado. Ex: evitar operações de HASH e SORT envolvendo tabelas grandes.
  • Aumentar a quantidade de memória RAM no servidor.