SQL Server Performance

How to find out top memory consumers in MB?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Frank.svs, Nov 27, 2013.

  1. Frank.svs New Member

    Hi,

    Checking if anybody has a tsql script to display top 10 high MEMORY consuming queries?

    Thank you.
  2. davidfarr Member

    You might want to take a look into DMV (Dynamic Management Views) and specifically into sys.dm_exec_query_memory_grants .

    The DMV will not return hours of logged data, only that which is currently in the buffer pool.
    For that reason, the script below will only show results if executed on an active production database. To log a history over several hours or days you would need to create your own procedures and tables for that.

    To get an idea of what might be possible, try this script;
    Code:
    SELECT
    convert(varchar(max),t2.[text]) as SQlquery,t1.requested_memory_kb,
    t1.granted_memory_kb,t1.required_memory_kb,t1.used_memory_kb,t1.max_used_memory_kb,t1.query_cost,t1.ideal_memory_kb
    into #temp1
    FROM sys.dm_exec_query_memory_grants t1 CROSS APPLY sys.dm_exec_sql_text(t1.sql_handle) t2
    GO
    DECLARE @counter INT
    SET @counter = 1
    WHILE @counter <= 10 -- Return data from DMV 10 times if there is data
    BEGIN
        IF (SELECT COUNT(*) FROM sys.dm_exec_query_memory_grants) > 0 --collect data if there is data
        BEGIN
                insert #temp1
                SELECT convert(varchar(max),t2.[text]) as SQlquery,t1.requested_memory_kb,
        t1.granted_memory_kb,t1.required_memory_kb,t1.used_memory_kb,t1.max_used_memory_kb,t1.query_cost,t1.ideal_memory_kb
        FROM sys.dm_exec_query_memory_grants t1 CROSS APPLY sys.dm_exec_sql_text(t1.sql_handle) t2
                WAITFOR DELAY '00:00:01' -- add a 1 second delay to avoid seeing the exact same query in results
                SET @counter = @counter + 1
    END
    END
    GO
    select distinct * from #temp1 order by query_cost desc
    Frank.svs likes this.
  3. Frank.svs New Member

    Thanks for the reply. It really looks good and whatever you have said is absolutely right.

    however , above query doesn't return any results in my case.

    Basically, this is what I am doing and what I am expecting.

    This is a TEST to identify queries which are consuming more memory and seeking for help to get those queries which are consuming or bloating up the memory.

    Step1: I have created a table where each row is 8K size and it contains 1132761 rows in it.
    Step2: stopped sql server. this is my TEST machine
    Step3: started sql
    Step4: open a new query window in ssms and started executing
    use testdb
    go
    SELECT * FROM t1; --- 1132761 rows
    go
    Step5: open another query window and executed below query
    use testdb
    go
    SELECT * INTO t2 FROM t1
    go

    Once I started above 2 sessions, sql server started consuming memory like crazy. I can clearly see the memory utilization growing and growing in Task mgr performance tab almost eating up all of the memory. I haven't set my max server purposefully. I wanted to catch these user_process = 1 queries which are consuming my memory.


    Environment details
    -------------------------
    SQL Server 2008 sp3 DEVELOPER Edition x64
    Windows 7 SP1 x64
    8GB Physical Memory(RAM)

    End Goal
    ------------
    I want to see the spids of the 2 select statements which are eating up my memory, is_user_process =1, sql text, xml plan, memory consumption by those queries in my output, which I am not able to :-(. I don't know whether I am going in the right direction to catch these high consuming queries or there is any indirect way of fetching these queries causing high memory consumption.


    --my repro statements
    use master
    go
    create database testdb
    go


    use testdb
    go
    create table t1
    (c1 int identity,
    c2 char (8000)
    );
    go
    insert into t1(c2) values(REPLICATE ('f', 8000))
    go 1000; --- I have done this multiple times and inserted total 1132761 rows


    -- My research.
    I have to below queries but it is giving wrong memory size, i.e. it is giving me , probably if i am not wrong , the size of the query or query plan in memory but not the memory it has consumed pulling all the data from disk to memory which caused high memory consumption on my box.

    test-1
    --------
    SELECT
    a.session_id,
    b.kpid,
    b.blocked,
    db_name(b.dbid) dbname,
    b.open_tran,
    b.status,
    b.cmd,
    a.memory_usage * 8192 / 1024 as [Memory Use (KB)] ,
    LEFT([sql].[text], 1000) as [text]
    FROM sys.dm_exec_sessions a with (nolock)
    INNER JOIN sys.sysprocesses b with (nolock) on a.session_id = b.spid
    OUTER APPLY sys.dm_exec_sql_text (b.sql_handle) sql
    WHERE a.is_user_process = 1
    and a.session_id <> @@SPID


    test-2
    -------
    Next, I came across this forum thread. But this is giving me the plan size.
    http://social.msdn.microsoft.com/Fo...-dbquery-is-consuming-most-memory-in-sql-2005

    select TOP 100
    objtype,
    usecounts,
    p.size_in_bytes/1024 'IN KB',
    LEFT([sql].[text], 100) as [text]
    from sys.dm_exec_cached_plans p
    outer apply sys.dm_exec_sql_text (p.plan_handle) sql
    ORDER BY 3 DESC

    test-3
    -------
    Looked at sys.dm_exec_query_stats but wasnt able to get the required output. Moreover, In book online, it states that it holds info of currently executing queries.

    From BOL :
    ------------
    Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

    Finally i tried , test-4
    ---------------------------
    select * from sys.dm_exec_query_memory_grants;

    I kept my sql statement running in one window and open a new windows and ran the above query, It didn't work for me. No rows selected.

    BOL says sys.dm_exec_query_memory_grants;
    -Queries that do not have to wait on a memory grant will not appear in this view.

    That's where I am stuck now.
    My end goal is to display all the user_process = 1 queries along with memory consumption in mb's, sqltext, sqlplan.


    Any help would be appreciated. Thank you.
  4. davidfarr Member

    My script above will not display a result (and not finish executing) until it has collected data from 10 queries.

    So open a new Query window and start running my script (my query needs to start first before yours), and then run your queries mentioned above in a separate window(s).
    You can change my script (WHILE @counter <= 10 ) to (WHILE @counter <= 5 ) and then run any 5 queries on your database and when you return to my script it should have displayed a result.
    That result will hopefully give some insight to the most memory intensive queries.

    Keep in mind that Total Memory Usage by SQL Server (The Task Manager Performance value) is a much more complex calculation and involves much more than just the impact of a single query. SQL Server caches many different things in memory and it's a tricky process to dissect all the components.

    Edit to Add: If your queries are not waiting on a memory grant and are not in the DMV then I don't have anything better to suggest, unfortunately.
  5. Frank.svs New Member

    Hi David,

    Thanks for the wonderful query and suggestions. Thank you very much for the help.
    Yes, it doesn't show up the memory grants which have already been done.
    However, I feel that I have learned something new. Thanks Again.

Share This Page