Wait Type RESOURCE_SEMAPHORE_QUERY_COMPILE - que se refere a um wait de compilação
Após uma boa sequência de artigos falando sobre o AlwaysOn hoje decidi falar um pouco sobre um outro assunto que me deparei a alguns dias em um dos clientes que trabalho neste artigo quero falar sobre o wait type RESOURCE_SEMAPHORE_QUERY_COMPILE que se refere a um wait de compilação.
Para acessar os artigos de AlwaysOn acesse: www.guiadba.com.br
Antes de iniciar quero dar uma breve introdução no assunto referente a fase de compilação de uma consulta na qual é a causa geradora do nosso wait type RESOURCE_SEMAPHORE_QUERY_COMPILE.
Quando realizamos a execução de uma consulta no banco de dados SQL Server nossa query passa por algumas fases e uma delas é a fase de compilação que determinar o melhor plano de execução para a consulta e guardar está informação para que em uma nova execução o SQL Server aproveite as informações em cache e não ter a necessidade de realizar uma nova compilação da sua consulta.
Imagem 1. Fase de compilação de uma consulta.
Um outro fator importante das fases de uma consulta é verificar o quanto de recurso (CPU, Memória e I/O) a nossa consulta necessita para ser executada está é uma estimativa realizada pelo SQL Server baseada em algumas informações dos objetos envolvidos.
Realizada está estimativa de recursos necessários para a execução da consulta o SQL Server antes mesmo de realizar a execução ele verifica se os recursos necessários estão disponíveis e aloca os recursos para sua execução.
O wait type RESOURCE_SEMAPHORE_QUERY_COMPILE é uma espera por recursos de memória para realizar a compilação da nossa query, ou seja, existe uma query na "fila de processamento" do SQL Server necessita de recurso de memória e naquele momento não está disponível.
Este processo é conhecido como memória de compilação e basicamente é a retirada de memória de buffer para realizar a compilação o wait type RESOURCE_SEMAPHORE_QUERY_COMPILE é encontrado no momento em o SQL Server necessita de mais memória do que o fornecido pelo GATEWAY que por padrão no SQL Server temos os três níveis de GATEWAY pequeno, GATEWAY médio e GATEWAY grande, para analisar o GATEWAY em seu servidor você poderá estar utilizando DBCC MEMORYSTATUS.
Na imagem 2 podemos observar o retorno do DBCC MEMORYSTATUS e analisar os recursos disponíveis em cada nível de GATEWAY, cada nível de GATEWAY suporta um número de consultas e este limite varia nos níveis SMALL e MEDIUM para o nível BIG GATEWAY somente uma consulta é permitida.
http://guiadba.com.br/wp-content/uploads/2016/07/Capturar7.png
Imagem 2. Retorno DBCC MEMORYSTATUS
Os GATEWAYS tem algumas finalidades importantes como gerenciar / limitar recursos de memória para compilação já que o SQL Server não tem um número determinado de quantas consultas podem ser compiladas em um mesmo momento e gerenciar o consumo de utilização de memória por consultas maiores e quando me refiro a maiores não é a quantidade de linha de defini a quantidade de recursos necessários.
O numero de consultas por GATEWAY é determinado pelos seus recursos de CPU lógicas quando se trata de GATEWAY SMALL e MEDIUM a conta é feita da seguinte forma:
SMALL GATEWAY: 4 X o número de cores disponíveis
MEDIUM GATEWAY: O mesmo número de cores disponíveis
http://guiadba.com.br/wp-content/uploads/2016/07/8284-image_0adcf146.png
Imagem 3. Número de consultas por GATEWAY
Analisando a imagem 4 podemos observar algumas consultas que estão aguardando recurso de memória, isso em tempo real e se observarmos a consulta de session_id 241 podemos verificar que seu consumo de memória em torno de 2.8GB, a session_id 51 está aguardando a liberação de 0,121576GB e as demais session_id também estão aguardando a liberação de memória, com isso neste momento podemos encontrar o wait type RESOURCE_SEMAPHORE_QUERY_COMPILE.
http://guiadba.com.br/wp-content/uploads/2016/07/query_memory-1024x113.png
Imagem 4. Retorno da DMV sys.dm_exec_query_memory_grants
Analisando o cenário em ambiente de produção utilizando a SP_WHOISACTIVE mas também conseguiríamos utilizar outros recursos encontramos o wait RESOURCE_SEMAPHORE_QUERY_COMPILE do qual tanto falamos neste artigo, na imagem 5 podemos ver o wait em ambiente de produção.
http://guiadba.com.br/wp-content/uploads/2016/07/resource_semaphore_query_compile-1024x444.png
Imagem 5. Wait type RESOURCE_SEMAPHORE_QUERY_COMPILE
Neste caso que ocorreu neste cliente a quantidade de memória disponível para o SQL Server resolveu o problema, aumentei de 70GB para 130GB (o servidor tinha 145GB de RAM) mas podemos encontrar outras soluções para este wait como ajustar a consulta, em 32 bit este wait é muito encontrado devido a alguns limites de endereço virtual.