Visão geral do processamento de consultas no SQL Server
Neste artigo apresento uma visão geral do processamento de consultas no SQL Server o que nos ajuda a entender, mesmo que de uma maneira superficial, como o SQL Server gerencia memória.
Quando uma consulta é enviada, o SQL Server primeiro verifica se há um plano armazenado em cache. Em caso afirmativo, esse plano será selecionado. Caso contrário, a instrução de consulta é primeiro analisada para gerar uma árvore de consulta, que descreve as etapas lógicas necessárias para transformar os dados de origem no formato solicitado pelo conjunto de resultados. A árvore de consulta é então vinculada, normalizada e convertida em uma árvore de álgebras. A árvore de álgebras é otimizada para gerar um plano de execução.
Quando um plano de execução otimizado é gerado (ou selecionado se estiver em cache), ele será executado se o requisito de memória puder ser atendido imediatamente. Caso contrário, o que acontece com frequência, a consulta é colocada em uma fila e fica aguardando a memória.
Como timeouts de execução de uma consulta acorre?
Antes de executar uma consulta, o SQL Server estima a quantidade de memória necessária para a execução e tenta reservar essa quantidade de memória do buffer pool. Se a reserva for bem-sucedida, a consulta será executada imediatamente. Se não houver memória suficiente prontamente disponível no buffer pool, a consulta será colocada em uma fila com um valor de tempo limite, em que o valor de tempo limite é orientado pelo custo da consulta. A regra básica é: quanto maior for o custo estimado, maior será o valor do tempo limite. Quando o tempo de espera dessa consulta excede o valor de tempo limite, um erro de tempo limite - timeout - é lançado e a consulta é removida da fila. A seguir, um exemplo de erro de tempo limite:
[State:42000 Error:8645] [Microsoft][SQL Native Client][SQL Server]Ocorreu um tempo limite enquanto se aguardavam recursos de memória para executar a consulta. Execute novamente a consulta.
Se a memória for suficiente para uma consulta recém-enviada, mas houver consultas em filas de espera, essa consulta será colocada em uma fila. As consultas nas filas de espera são "classificadas" com base em seu custo e tempo de espera. Quanto menor o custo ou maior o tempo de espera de uma consulta, mais alta será a classificação. Observe que a classificação é dinâmica e muda com frequência. A consulta com a classificação mais alta será executada se houver memória livre suficiente. Se a memória for insuficiente, nenhuma outra consulta será executada. O SQL Server NÃO se preocupará em verificar se a memória livre é suficiente para executar outras consultas. Você pode verificar qual consulta é a próxima a ser selecionada executando a seguinte consulta. Se ela não retornar nenhuma linha, significa que não há consultas em espera. Observação: os resultados dessa consulta mudam com o tempo.
select * from sys.dm_exec_query_memory_grants where is_next_candidate is not null
Você pode usar o valor na coluna plan_handle para recuperar o showplan de sys.dm_exec_query_plan e a coluna sql_handle para recuperar o texto SQL de sys.dm_exec_sql_text.
Você também pode obter essa informação usando a o procedimento armazenado sp_who3.
Observe que nem toda consulta precisa de uma reserva de memória. Normalmente, uma consulta precisa de uma reserva de memória se seu plano de execução tiver operadores de sort, hash ou bitmap. Como a criação de um índice requer sorting, ela sempre precisa de uma reserva de memória. Se uma consulta não precisar de reserva de memória, ela será executada imediatamente.