De acordo com meus livros de leitura sobre SQL Server 2008 Internals e Troubleshooting (emprestados da biblioteca local em Illinois) por Christian Bolton, Brent Ozar etc. pode confirmar ou corrigir meu entendimento.
Cada consulta ou operação que requer concessão de memória de consulta precisará de memória de espaço de trabalho. Em consulta geral usando Sort, Hash Match Join, Parallelism (não tenho certeza sobre isso), Bulk Insert (não tenho certeza), Index Rebuild etc.
A memória do espaço de trabalho faz parte do buffer pool do SQL Server (é alocada como parte do buffer pool) e a memória máxima do espaço de trabalho é 75% da memória alocada para o buffer pool. Por padrão, uma única consulta não pode obter mais de 25% da memória do espaço de trabalho (no SQL 2008/SQL 2012 -- controlado pelo grupo de carga de trabalho padrão do Resource Governor pronto para uso).
Buscando uma confirmação do meu entendimento
1) Considerando o sistema com 48 GB de RAM e memória máxima do servidor configurada para 40 GB, isso significa que a memória máxima do espaço de trabalho é limitada a 30 GB e uma única consulta não pode obter memória do espaço de trabalho (memória de consulta) superior a 10 GB. Portanto, se você tiver uma consulta incorreta trabalhando com um bilhão de linhas que está fazendo uma junção de hash massiva e precisar de mais de 10 GB de memória (memória do espaço de trabalho), seria necessário passar por essa fila de concessão de memória ou derramar imediatamente no disco?
2) Se uma consulta que faz uma operação de classificação massiva tiver sido atribuída a uma memória de espaço de trabalho de 5 MB e durante a execução da consulta, se o otimizador de consulta perceber que, devido a estatísticas ruins ou índices ausentes, essa consulta realmente precisará de 30 MB de memória de espaço de trabalho. irá derramar imediatamente para tempdb. Mesmo que o sistema tenha bastante memória de espaço de trabalho disponível durante a execução, uma vez que a consulta excedeu a memória de espaço de trabalho concedida durante a execução, ela terá que ser transferida para o disco. Meu entendimento está correto?
Não funciona assim. Uma vez escolhido um plano que requer uma concessão de memória, a consulta deve obter a concessão para que ela passe pela fila. O derramamento, se houver, ocorre muito mais tarde no ciclo de execução. Uma consulta não pode decidir prosseguir sem a concessão e 'derramar' em vez disso. Deve obter a outorga e, a partir dela, iniciar a execução. Se a outorga se revelar insuficiente (devido a estimativa errada ou por obtenção de uma outorga muito inferior à solicitada) então a consulta será forçada a derramar.
Estritamente falando, não é o otimizador, é a execução da consulta. O Query Optimizer tem apenas uma palavra a dizer na decisão do plano, mas uma vez que o plano é escolhido e colocado em execução, o otimizador fica fora de cogitação. Além disso, 'índices ausentes' não desempenham nenhum papel aqui. Um índice ausente pode forçar um plano ruim, mas não pode influenciar como esse plano 'ruim' é executado, pois esse plano foi construído considerando exatamente quais índices realmente existem, para que ele saiba exatamente o que pode e o que não pode fazer. Os derramamentos ocorrem quase sempre devido a estimativas ruins, ou seja. estatísticas ruins ou algoritmos de estimativa de cardinalidade ruins no otimizador e na execução (fica muito complexo se você se aprofundar nos detalhes, então vou parar por aqui).
Infelizmente sim. No entanto, o otimizador deve apresentar um plano que aproveite a RAM disponível.
Eu recomendo ler Understanding SQL server memory grant , que é a melhor informação sobre o assunto por uma ampla margem.