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

Chaitanya Kiran 696 Reputation points
2023-10-14T07:11:00.3833333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,904 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-14T10:11:33.2133333+00:00

    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
    

  2. Olaf Helper 41,411 Reputation points
    2023-10-14T17:44:48.9966667+00:00

    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.

    0 comments No comments

  3. Rahul Randive 8,751 Reputation points Microsoft Employee
    2023-10-14T18:28:03.82+00:00

    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!

    0 comments No comments

  4. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-10-16T07:18:29.9733333+00:00
    0 comments No comments