Versão curta

O SQL Server está usando 34 GB de RAM. Mas ao consultar o Relatório de Consumo de Memória , o tamanho do buffer pool e o tamanho da consulta ad-hoc, ele soma apenas cerca de 2 GB. O que os outros 32 GB de RAM estão fazendo?

Preemptivo: "Você deve limitar a quantidade de RAM que o SQL Server pode usar." Vamos dizer que é limitado a x. Isso simplesmente muda minha pergunta para: "O que o outro xGB de RAM está fazendo?"

Versão longa

Eu tenho uma instância do SQL Server que está consumindo 32 GB de RAM:

Isso não é 32 GB de memória virtual ; na verdade está consumindo 32 GB de memória física (nos chips de RAM) - conhecido como "conjunto de trabalho" .

E não é como se fosse compartilhado com algum outro processo. Essencialmente, tudo isso é privado para o SQL Server:

• Conjunto de trabalho privado : 33.896.700 bytes

O que ele está fazendo com toda essa RAM?!

Uso de memória do bufferpool por banco de dados

Portanto, consultamos o uso de memória por banco de dados - pois o pool de buffers armazena em cache as páginas dos bancos de dados:

--Memory usage server wide
;WITH src AS
(
    SELECT
        database_id,
        COUNT_BIG(*) AS db_buffer_pages
    FROM sys.dm_os_buffer_descriptors
    --WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
)
SELECT
    CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS [Database Name],
    db_buffer_pages AS BufferPages,
    db_buffer_pages /128.0 AS BufferMB
FROM src
ORDER BY db_buffer_pages DESC
OPTION(RECOMPILE, MAXDOP 1);

Para um total geral de 4,5 MB dos 32 GB.

O TempDB usa mais (1,4 MB), e o resto desce a partir daí:

5 MB de 32 GB - não conta muito

Sim, isso pode parecer baixo - mas provavelmente porque eu liguei DBCC DROPCLEANBUFFERSprimeiro.

Cache do plano de consulta

Em seguida, consultamos o Cache do Plano de Consulta . Todas essas instruções T-SQL precisam ser compiladas em um plano enorme, e esses planos são armazenados em cache na RAM.

--Server-wide memory usage of plan cache
SELECT
    [cacheobjtype], ObjType,
    COUNT(1) AS Plans,
    SUM(UseCounts) AS UseCounts,
    SUM(CAST(size_in_bytes AS real)) / 1024.0 / 1024 AS [SizeMB]
FROM sys.dm_exec_cached_plans
--where [cacheobjtype] = 'Compiled Plan' and [objtype] in ('Adhoc', 'Prepared')
GROUP BY CacheObjType, ObjType
ORDER BY SizeMB DESC
OPTION(RECOMPILE, MAXDOP 1)

Agora podemos ver quanta memória é usada para armazenar vários planos de consulta:

Para um total geral de 250 KB - muito aquém dos 32 GB ausentes.

Nota: Sim, isso pode parecer baixo - mas provavelmente porque eu liguei DBCC FREEPROCCACHEprimeiro.

Relatório de consumo de memória

As consultas acima me mostram a RAM usada por:

• o pool de buffers (para armazenar em cache nas páginas do banco de dados de memória do disco)
• o cache do plano de consulta

E isso é realmente tudo o que existe. Mas o SQL Server fornece um relatório de consumo de memória :

Este relatório fornece dados detalhados sobre o consumo de memória dos componentes na instância

Narrador : "Não"

O relatório é um pouco difícil de ler:

Mas no final a divisão é:

• MEMORYCLERK_SOSNODE : 131.832 KB
• MEMORYCLERK_SOSMEMMANAGER : 71.464 KB
• USERSTORE_DBMETADATA : 67.432 KB
• USERSTORE_SCHEMAMGR : 55.784 KB
• MEMORYCLERK_SQLSTORENG : 54.280 KB
• MEMORYCLERK_SQLBUFFERPOOL : 30.576 KB
• Outros : 145.056 KB

Isso dá um total geral de: 556.424 KB → 544 MB

Mesmo se arredondarmos para 1 GB: ainda está muito longe de 32 GB.

Então, para onde vai a memória?

Sim, posso limitar o SQL Server a 25 GB de RAM. Mas isso só mudaria minha pergunta para:

O que é o SQL Server está usando 25 GB de RAM; para onde vai a memória?

Porque isso soa muito como um vazamento de memória para mim.

• Servidor : SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

Tempo de funcionamento do servidor

Pode consultar o tempo de atividade do servidor (criação de tempdb):

--Use creation date of tempdb as server start time

SELECT SERVERPROPERTY('SERVERNAME') AS ServerName, create_date AS ServerStartedDate FROM sys.databases WHERE NAME='tempdb';

• Data de início do servidor :2021-12-21 15:46:26.730

Montagens CLR

SELECT * FROM sys.assemblies

Servidores vinculados

select provider, provider_string from sys.servers