1

I am trying to understand the memory management of SQL Server. I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 MB. By using the following query I found the list of all memory clerks:

SELECT  [type] AS [ClerkType], SUM(pages_kb) / 1024 AS [SizeMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC

Total sum of all memory clerks was equal to 23523 MB.

Value for MEMORYCLERK_SQLBUFFERPOOL memory clerk is 18387 MB.

I also checked some performance counters:

Total Server Memory (KB) = 32262 MB.
Database Cache Memory (KB) = 18387 MB.

Please correct me if any of the below statements are wrong,

Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory.

Total Server Memory (KB) - The committed memory from the Buffer Pool.

Data Cache Memory (KB) performance counter is equivalent to MEMORYCLERK_SQLBUFFERPOOL memory clerk and both represents data cache used size.

Above query shows all memory clerks for both Buffer Pool Memory and Non-Buffer Pool Memory.

My question related to this subject:

If Total Server Memeory (KB) is related to Buffer Pool memory, then why its size (32262 MB) is bigger than Total sum memory clerks altogether (23523 MB) which represents both Buffer Poll Memory and Non-Buffer Pool Memory?

EDIT: Result of select * from sys.dm_os_process_memory enter image description here enter image description here

1

Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory.

No, some part of memory allocation for SQL Server 2019 can be done outside of max server memory like ( I would quote from Memory Management Architecture Guide)

Memory for thread stacks1, CLR2, extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in Transact-SQL statements, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.

So you can see some part of memory is allocated outside max server memory so your equation does not always holds true.

Total Server Memory (KB) - The committed memory from the Buffer Pool.

Yes that is the memory committed using memory manager.

Data Cache Memory (KB) performance counter is equivalent to MEMORYCLERK_SQLBUFFERPOOL memory clerk and both represents data cache used size.

No, buffer pool is much bigger, Data cache is subset of it.

If Total Server Memeory (KB) is related to Buffer Pool memory, then why its size (32262 MB) is bigger than Total sum memory clerks altogether (23523 MB) which represents both Buffer Poll Memory and Non-Buffer Pool Memory?

Total server memory includes basically memory used by SQL Server which is almost 31 GB and this will be bigger than sum of the clerks, which is basically 22 GB, because memory clerks do not capture all memory allocations within SQL Server ( They do most of it however).

Let me know if you have further questions.

| improve this answer | |
  • Thank you for your answer. I've read the doc provided by you. Ok, now I understand that not everything is dependent on max server memory, and that memory clerks do not capture all memory allocations. The main question which arises here is how to find out how much memory was allocated by ALL memory allocations, mainly memory allocated for Linked Servers and thread stack? – RaufDBA Jun 16 at 19:27
  • For total memory used you need to see the column physical_memory_in_use_kb in DMV sys.dm_os_process_memory. This column gives total physical memory used by SQL Server. For memory used by Linked server and thread stack since the memory is allocated directly by windows you need to use RAMMAP tool. Its long time I have used it. – Shanky Jun 17 at 10:04
  • I appreciate your help. Thanks – RaufDBA Jun 17 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.