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.

Comments

  • Anonymous
    July 13, 2010
    Olá Fabricio, Segue algumas duvidas:
  1. Então RESOURCE_SEMAPHORE indica pressão sobre qualquer recurso ou só memoria mesmo?
  2. Quando temos a presença da espera "RESOURCE_SEMAPHORE" em algum processo podemos esperar algum aviso de OOM no Rings buffer?

  • Anonymous
    July 14, 2010
    Oi Leivio! 1 - Resource semaphore está relacionado com a falta de memória. Entretanto, isso pode ser causado indiretamente por CPU, disco, falta de índice ou estatística. Portanto, o problema sempre pode ser resolvido (ou aliviado) adicionando memória RAM... mas nem sempre ela é a culpada. 2 - Durante a presença do waittype = "resource semaphore", não aparecerá nenhum indicativo no Errorlog ou na DMV Ring Buffers. A partir do SQL 2008, há um XEvent Default rodando e que registra quando essa espera ultrapassa 30 segundos. Abraços, Fabricio

  • Anonymous
    July 25, 2010
    Olá Fabrício, Como identificar essa query que está causando a falta de memória ou mesmo fazendo que ela entre nessa fila? Obrigado.

  • Anonymous
    July 25, 2010
    Oi Patricio! No blog, ficou faltando responder exatamente a essa pergunta: como identificar a query. Já respondendo: rode a query abaixo e veja o resultado da coluna sql_handle. select * from sys.dm_exec_query_memory_grants Fiquei devendo explicar isso com mais calma. Preciso escrever mais um post com um exemplo. Obrigado pelo comentário! Fabricio

  • Anonymous
    September 19, 2013
    Se o server tem 54gb e entraram somente 4 querys(28gb), tem mais 4 esperando (28gb). Por que  não entrou mais nenhuma query, já que ainda tinha 26 gb sobrando no servidor?

  • Anonymous
    September 20, 2013
    Oi Fernando, Precisaria validar com mais detalhes. A primeira coisa que validaria é o resource governor que limita em 25% da memória visível. Existem alguns detalhes que não falei como, por exemplo, que há vários "gateways" (portões de entrada) de acordo com o custo das queries. Abraços, Fabricio