SQL Server 2012 Standard Edition usando mais de 64 GB de memória, por que e como?

O limite de 64 GB (e 128 GB em 2014) se aplica apenas ao buffer pool. O artigo da KB " Configuração de memória e considerações de dimensionamento no SQL Server 2012 " diz:

A partir do SQL Server 2012, esses limites de memória são aplicados apenas para o cache do banco de dados (buffer pool).

E como todos sabemos, max server memorytambém não controla toda a memória do SQL Server. A documentação oficial está em processo de atualização para fornecer as seguintes orientações sobre isso (essa redação exata foi fornecida por Bob Ward):

A configuração no nível do servidor max server memorycontrola a alocação de memória do SQL Server, incluindo buffer pool, memória de compilação, todos os caches, concessões de memória qe, memória do gerenciador de bloqueio e memória clr (basicamente qualquer “funcionário” encontrado em sys.dm_os_memory_clerks). A memória para pilhas de threads, heaps de memória, provedores de servidor vinculados diferentes do SQL Server ou qualquer memória alocada por uma DLL “não SQL Server” não é controlada pela memória máxima do servidor.

Portanto, é bastante provável que você veja sqlservr.exeusar mais de 64 GB de memória se houver alocação significativa para qualquer coisa que não seja o buffer pool, mesmo na Standard Edition (e mais de 1 GB, mesmo na Express Edition).

Se você quiser impedir que o SQL Server use mais de 64 GB de memória, precisará definir max server memoryalgo menor que 64 GB (talvez seja necessário experimentar aqui para encontrar a combinação certa, mas entenda que a memória do pool sem buffer pode flutuar , então você ainda pode ver coisas que se aventuram além do limite de licenciamento e do limite configurado).

Ou , você pode simplesmente deixá-lo como está e agradecer por poder usar até 64 GB para buffer pool e muito mais para outras coisas que poderiam reduzir o uso do buffer pool se o limite de 64 GB fosse um limite rígido imposto.

Você pode ver como a memória é distribuída de várias maneiras. Entre os secretários de memória, você pode usar:

SELECT [type], mem_MB = pages_kb/1024
 FROM sys.dm_os_memory_clerks
 ORDER BY pages_kb DESC;

Na maioria dos casos, o principal consumidor será MEMORYCLERK_SQLBUFFERPOOL, mas você também deve ver um volume significativo de CACHESTORE_*entradas. Você pode agrupá-lo em um nível superior usando algo como:

SELECT [type] = COALESCE(SUBSTRING([type],1,CHARINDEX('_',[type])-1),'-- total'),
  SUM(pages_kb/1024)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((),SUBSTRING([type],1,CHARINDEX('_',[type])-1));

Para ver como a memória do cache/armazenamento do usuário é distribuída:

SELECT [type], name, 
 mem_MB = CONVERT(DECIMAL(19,2),pages_kb/1024.0) 
 FROM sys.dm_os_memory_cache_counters
 ORDER BY pages_kb DESC;

Atualmente, em minha humilde VM, a consulta em sua pergunta (fornecida por @Shanky) diz que o SQL Server está usando 2.085 MB de memória (com 2.004 MB bloqueados na memória). No entanto, minha consulta acima contra os funcionários de memória DMV diz que a memória total usada é de apenas 1.793 MB.

A consulta na resposta de Chris diz que o total é 2.054 MB, com uma meta de 3.072 MB (isso ocorre porque defini explicitamente max server memorycomo 3.072 MB).

Então, onde estão os ~ 200 MB extras? Quem sabe. (Também posso ver essa diferença se comparar total server memorycom Process > sqlservr > Private Byteso Monitor de desempenho.) Eu poderia começar a analisar a saída de DBCC MEMORYSTATUSpara tentar juntá-la (e Tim Chapman escreveu alguns PowerShell para tornar isso mais fácil ), mas vale a pena? Toda a memória é relatada lá? Não tenho 100% de certeza de que seja (ou que você deva usar a saída em um sistema NUMA), ou que qualquer DMV possa fornecer uma imagem totalmente precisa de toda a memória usada pelo SQL Server. Eu também não acredito que todos os tipos de secretários de memória estejam documentados em qualquer lugar - então, mesmo que isso forneça uma imagem completa, você ainda pode estar coçando a cabeça sobre o que os resultados realmente significam.

O Gerenciador de Tarefas, entretanto, diz que sqlservr.exeestá usando apenas 42 MB. Faça o que fizer, pare de usar o Gerenciador de Tarefas. Para qualquer coisa, realmente. Sei que você removeu esse detalhe de sua pergunta em sua edição mais recente, mas não posso enfatizar o suficiente como é inútil para mostrar o uso de memória do SQL Server.

De acordo com a resposta de Aaron Bertrand ( veja rapidamente quanta RAM está alocada para o SQL Server? ). O Gerenciador de Tarefas não deve ser usado para verificar quanta memória o SQL está realmente usando.

Prefiro usar a seguinte consulta (novamente, obrigado Aaron):

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

Como alternativa, usar DBCC MEMORYSTATUSfornecerá essas informações (assim como uma série de informações adicionais).

Acho que esta consulta me dá uma memória real total mais precisa em uso -

set transaction isolation level read uncommitted
select 
    @@Servername,
    osn.node_state_desc, 
(
select physical_memory_in_use_kb/(1024) as SQLMEMORYINUSE_MB
from sys.dm_os_process_memory
) + (omn.locked_page_allocations_kb / 1024.0 ) as Tot
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'
and osn.node_id = 1

Eu aprecio o post é antigo, mas isso pode ser útil.

Eu tenho uma instância de 2012 std, que está usando 120 Gb de RAM no total