How to find top 10 queries using high memory along with its spid

Question

How to find top 10 queries using high memory along with its spid? Could you please share the script

Answer 1

This is not an entirely trivial question, because it depends on what you mean.

When a query runs, there are, at least, two ways to consume memory:

• One is the memory grant, which is an amount granted to the execution and which is to be used for Sort and Hash operators. No matter whether the grant is used or not, the memory is reserved for the execution.
• The other is how much data the query needs to read. This is data that already is in the buffer pool, or which needs to be dragged into the buffer pool. The buffer pool is a common resource, so many queries could read the same data. This makes it more difficult to account the memory to a specific query.

Furthermore, do you mean the top 10 queries right now, or the top 10 queries through the day? If you mean the latter, it is of course difficult to account them to a specific spid, as many processes may be running the same query.

All that said, here is a query that gives you the top 10 queries in terms of requested memory grant that are currently running or waiting to be running. That is, if there is not enough memory available to satisfy a grant, that query will have to wait.

SELECT TOP 10 est.text, qp.query_plan, mg.*
FROM   sys.dm_exec_query_memory_grants mg
CROSS  APPLY sys.dm_exec_sql_text(mg.sql_handle) est
CROSS  APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
ORDER BY mg.requested_memory_kb DESC

Answer 2

How to find top 10 queries using high memory

Queries don't use memory, SQL >Server engine do to serve queries.

In SSMS right click on server or database = Reports = Standard Reports.

There you will find reports with informations in your mind.

Answer 3

Hi @Chaitanya Kiran

Here is Microsoft document on "Monitor and Troubleshoot Memory Usage"

It also contains scripts and various methods for monitoring memory usage.

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/monitor-and-troubleshoot-memory-usage?view=sql-server-ver16

And the blog- https://www.mssqltips.com/sqlservertip/6833/sql-server-memory-usage-query/

Thank you!

Answer 4

Hi @Chaitanya Kiran

As a supplement, you can refer to this thread.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d846c03d-91d0-4bbc-be3b-93ed0bdf32a0/find-out-queries-taking-up-more-memory?forum=sqldatabaseengine

Best regards,

Percy Tang