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.