O que acontece quando não há memória física disponível para o SQL Server?

quando não há memória física para os dados, o SQL Server move os dados já existentes para o TEMPDB

O artigo ao qual você vinculou é enganoso na melhor das hipóteses e incorreto em alguns lugares. Acho que o autor estava tentando simplificar demais algumas coisas complicadas e, ao fazê-lo, foi um pouco longe demais.

O SQL Server não move dados da memória (o pool de buffers) para o tempdb dessa maneira. Ele usa uma estratégia de cache "menos usada recentemente" (em geral), portanto, se houver pressão de memória e novos dados precisarem ser inseridos na memória, o SQL Server expulsará os dados LRU do pool de buffers para acomodar os novos dados. Esse comportamento é frequentemente monitorado por um contador perfmon chamado "Page Life Expectancy" (PLE) :

A definição de PLE é o tempo esperado, em segundos, que uma página de arquivo de dados lida no buffer pool (o cache na memória das páginas de arquivos de dados) permanecerá na memória antes de ser empurrada para fora da memória para dar espaço para dados diferentes página do arquivo. Outra maneira de pensar no PLE é uma medida instantânea da pressão no buffer pool para liberar espaço para as páginas que estão sendo lidas do disco. Para ambas as definições, um número maior é melhor.

Durante a execução da consulta, o SQL Server pode usar tempdb para determinadas operações. Isso geralmente é feito se as estimativas forem ruins, mas a pouca memória disponível pode influenciar esse comportamento.

Algumas das operações que podem "derramar" para o tempdb dessa maneira são linhas de hash (para junções ou agregações, etc.), classificação de linhas na memória e linhas de buffer durante a execução de consultas paralelas.

As consultas do usuário também podem usar explicitamente tempdb (com tabelas temporárias globais ou locais) e usar implicitamente tempdb (com níveis de isolamento de instantâneo ou instantâneo confirmado de leitura).

Nenhuma dessas situações realmente parece se encaixar na afirmação que você citou.

quando não há memória física suficiente, o sistema operacional pode usar o PAGE FILE e mover dados da memória física para ele

Isso definitivamente pode acontecer e está fora do controle do SQL Server na maior parte. Há um botão que você pode girar para tentar evitar alguns tipos de paginação no nível do sistema operacional, ou seja, ativar "Bloquear páginas na memória" (LPIM) :

Essa política do Windows determina quais contas podem usar um processo para manter os dados na memória física, evitando que o sistema pagine os dados para a memória virtual no disco.

Então, o que podemos impedir de ser paginado para o disco?

Antes do SQL Server 2012, as páginas alocadas por meio de um componente chamado "Alocador de página única" eram bloqueadas na memória (não podiam ser paginadas). Isso incluiu o conjunto de buffers (páginas do banco de dados), cache de procedimento e algumas outras áreas de memória.

Consulte Diversão com páginas bloqueadas, AWE, Gerenciador de tarefas e o conjunto de trabalho… para obter detalhes, especialmente a seção "4. Agora eu sei que o SQL Server em x64 pode usar “Páginas bloqueadas”, o que exatamente está bloqueado?" Leitura adicional relacionada pode ser encontrada aqui: Great SQL Server Debates: Lock Pages in Memory

No SQL Server 2012 e posterior, não há "Single Page Allocator" (os alocadores de página única e de várias páginas foram mesclados, de acordo com uma análise detalhada da memória – SQL Server 2012/2014 ). Os detalhes do que exatamente pode e não pode ser paginado não estão documentados em detalhes em nenhum lugar que eu tenha visto. Você pode usar uma consulta como esta para ver o que está bloqueado:

select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

De acordo com o mesmo artigo de suporte da MS, você também pode usar DBCC MEMORYSTATUSpara ver quanta memória está "bloqueada".

Como uma observação lateral, você pode ver evidências do conjunto de trabalho do SQL Server sendo paginado pelo sistema operacional no log de erros. Haverá mensagens que se parecem com isso:

2019-09-02 10:19:27.29 spid11s Uma parte significativa da memória do processo do sql server foi paginada. Isso pode resultar em uma degradação do desempenho. Duração: 329 segundos. Conjunto de trabalho (KB): 68780, confirmado (KB): 244052, utilização de memória: 28%.

As versões modernas do SQL Server têm uma chance muito pequena de desligar completamente. O SQL Server carrega o .NET Framework em seu espaço de endereço e o usa em operação normal. Se a memória física e o arquivo de paginação acabarem, o Windows tentará aumentar o arquivo de paginação; no entanto, mesmo que ele possa aumentar o arquivo de paginação, esta não é uma operação instantânea e as alocações de memória falham enquanto o arquivo de paginação está crescendo. Há um bug no manipulador de E/S assíncrona do .NET onde ele aloca memória em resposta a uma notificação da APC. Se a chamada para newfalhar, ela lançaráOutOfMemoryException. Essa exceção é capturada no código nativo dentro do agendador de tarefas; no entanto, a E/S assíncrona parecerá nunca terminar. O encadeamento do finalizador para FileStream bloqueará a espera da conclusão da E/S para que ele possa desafixar o buffer, desligando o encadeamento do finalizador para sempre. Isso faz com que o .NET Framework use gradualmente mais e mais memória até que não seja possível alocar mais memória, ponto em que o SQL Server não responderá porque o winsock não pode alocar mais buffers, portanto, mesmo a conexão de acesso do administrador é inútil.

Na verdade, atingi um desligamento total do agendador de tarefas em um aplicativo .NET devido à falta de memória. Felizmente, o processo finalmente morreu devido ao lançamento OutOfMemoryExceptionde algum thread que não o pegou após várias falhas, para que pudéssemos descobrir o que estava realmente desligando o servidor.

Uma vez que eu sabia o que estava procurando, era fácil encontrar o bug na análise estática.