How to tell what sql stmts are taking up more MEMORY in SQL Server

Question

Hi,

Recently, we got high memory incidents from monitoring tool. I have lowered the max server memory for time being.
Using SQL Server 2016 Enterprise Edition.

From task mgr I see sqlservr.exe is the one taking up 85% memory (i.e. workingset value).

Now I want to know withing sql server , what are the sql queries are taking up more memory.

I used sp_whoisactive but I am seeing "used_memory" column values as 2,3,9, 79,243 , 4,221. I am not sure what is the value indicate, is it number of 8K pages?
Is there are proper way to tell which statements taking most memory in MB/GB and I wanted to store them in a table. This information will be share to developers.

Thank You!

Answer 1

This is a useful query that I got from Dmitri Korotkevich's book SQL Server Advanced Troubleshooting:

SELECT TOP 15
    [type] AS [Memory Clerk]
    ,CONVERT(DECIMAL(16,3),SUM(pages_kb) / 1024.0) AS [Memory Usage(MB)]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY sum(pages_kb) DESC;

Normally you will see MEMORYCLERK_SQLBUFFERPOOL to be lot bigger than anything else. Exactly how much bigger depends on how much RAM you have available. But let me put it this way: if any other clerk has more than 10 GB of RAM, I find that noticeable.

Answer 2

That is completely normal and expected behavior.

Once the memory is acquired, it will not be released unless memory pressure is detected. This is by design and does not indicate a memory leak in the SQL Server process.

Please see:
https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver15

Answer 3

You cannot really say that a query takes up memory. What takes up memory in SQL Server is mainly the buffer cache. True, it is queries that drag data into the cache. If there is a query like:

SELECT SUM(nonindexedcolun) FROM VeryBigTable

this will result in all data pages for VeryBigTable to be read into memory.

However, what is in the buffer cache may also be the result of very many queries asking for different things. Once a page has been read into the cache, it will stay there, as long as the space is not needed for something else.

So in the end it is not a meaningful question. SQL Server is designed to grab as much memory as it can, but it is also designed to play well with the operating system and yield memory, if the OS needs it for something else.