The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory grants & waits by session

Posted by Sudarshan Narasimhan on December 14, 2012


I often use a multitude of T-SQL queries and scripts when I am troubleshooting memory issues and even for monitoring SQL Server Memory usage. I am posting these scripts here in the spirit of sharing, as I’m sure there are other variations of these same scripts out there already. For this purpose of this blog post, these scripts are only useful in troubleshooting out-of-memory (OOM) and other issues in the Buffer Pool. This blog post does not cover Virtual Address/Virtual Memory or memory tracking outside of the Buffer Pool. That will be covered in a later post.

In case you have other scripts for memory monitoring, please leave behind a comment


— Query to find the Buffer Pool usage per each Database
— Each of these pages are present in the Buffer Cache, meaning they are IN_RAM pages.

DECLARE @total_buffer BIGINT;
SELECT @total_buffer = cntr_value
FROM sys.
dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE
‘%Buffer Manager’
AND counter_name = ‘Total Pages’
/AND counter_name = ‘Database pages’ — Uncomment this line & comment the above line, if you’re SQL Server version is 2012 or above/

;WITH BufCount AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG
()
FROM sys.
dm_os_buffer_descriptors
WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[Database_Name] = CASE [database_id] WHEN 32767
THEN 
‘MSSQL System Resource DB’
ELSE DB_NAME([database_id]) END
,
[Database_ID]
,
db_buffer_pages as [Buffer Count (8KB Pages)]
,
[Buffer Size (MB)] = db_buffer_pages / 128
,
[Buffer Size (%)] = CONVERT(DECIMAL(10,2
), db_buffer_pages 100.0 / @total_buffer)
FROM BufCount
ORDER BY [Buffer Size (MB)] DESC
;

Output

image


-- Query to identify objects that are taking up most of that memory in Buffer Pool.
-- This is only for the current database context. Please prefix <USE DBNAME> as per your requirement

SELECT TOP 25 
 DB_NAME(bd.database_id) as DBNAME,
 obj.[name] as [Object Name],
 sysobj.type_desc as [Object Type],
 i.[name]   as [Index Name],
 i.[type_desc] as [Index Type],
 COUNT_BIG(*) AS Buffered_Page_Count ,
 COUNT_BIG(*) * 8192 / (1024 * 1024) as Buffer_MB,
 bd.page_type as [Page Type] -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
LEFT JOIN sys.objects sysobj on i.object_id = sysobj.object_id
WHERE database_id = DB_ID()
and sysobj.type not in ('S','IT')
GROUP BY DB_NAME(bd.database_id), obj.name, obj.index_id , i.[name],i.[type_desc],bd.page_type,sysobj.type_desc
ORDER BY Buffered_Page_Count DESC

Output

image


-- Query to show current memory requests, grants and execution plan for each active session
-- This shows memory granted & requested for currently active sessions on the instance level
-- This can be used in a script to capture information over a period of time.

SELECT mg.session_id, mg.requested_memory_kb, mg.granted_memory_kb, mg.used_memory_kb, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Output

image

Note: When you click on the query_plan, it opens up the XML Showplan in SSMS.


-- Query to search plan cache for queries with memory grants completed

SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes as [Bytes Used in Cache]
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
order by cp.size_in_bytes desc
OPTION (MAXDOP 1)

Output

image


-- Queries that have requested memory or waiting for memory to be granted
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.ideal_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;

Note: This query can be used when there are active sessions waiting on memory to be granted and they are waiting. These sessions will have a wait_type of RESOURCE_SEMAPHORE. You can calculate the wait time for memory grant, by subtracting request_time and grant_time


-- SQL Server 2005/2008/R2 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(single_pages_kb) AS [SPA Memory (KB)],
SUM(single_pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;
-- SQL Server 2012 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(pages_kb) AS [SPA Memory (KB)],
SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

Output

image
That’s all for now folks!

  • TheSQLDude (Sudarshan)

 

Advertisements

13 Responses to “Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory grants & waits by session”

  1. Hey Sudarshan – This post is very useful, Thanks for sharing the info.

  2. […] Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory gran…. […]

  3. […] or missing indexes or bad cardinality estimates). Leveraging some of the useful queries from this blog we found there was one poor query which was alone taking 9 Gigs of memory for query execution. […]

  4. Anandan K said

    Sudarshan,

    Thanks for the useful scripts. We have our Win 2008 64-bit box with SQL Server 2008 64-bit.

    As per Process Explorer tool, the total memory usage is showing around 10 Gigs.

    Result of DBCC MEMORYSTATUS : the “VM Committed” Tab of “Memory Manager” is also showing the total SQL server memory usage is 10 Gigs.

    Using your query to identify the “Query to find the Buffer Pool usage per each Database”, it is showing as around 7 Gigs only.

    I guess the remaining memory usage could be of Non-bpool memory usage. Can you please provide a query to list out the non-bpool usage in Megs ? Thanks in advance.

    When I google for this I find the script to get maximum available non-bpool memory only, But I don’t get the query to list of the non-bpool memory usage details.

    • Ulrik Pedersen said

      Hi Anandan. I have exactly the same problem. 12Gb consumed in total but the buffer pool total only says about 6Gb. Did you find a solution?

  5. ananda said

    Hi Sudarshan,

    Thanks for this usefull script for find out buffer pool usages on each database.

    In SQL2008 R2 64 bit, i have setting cape memory 20 GB,

    –Query to find the Buffer Pool usage per each Database, its showing 14GB,
    But remaining 9 GB where it is using? Is it non-buffer pool using for 9GB?

    How to resolve this issues? how to find out which are swmming at Non-buffer pool Query?

    • Hi Ananda – “Query to find the Buffer Pool usage per each Database” only shows the pages that are current used by DB Buffer pages, i.e. data pages belonging to each DB currently in the Buffer Pool. In your case it is 14GB. The remaining 6 GB could be free pages or cached plans etc. To confirm this, you can the query “Top clerks ordered by memory used” and find out more info.

  6. sand said

    Hi Sudarshan,

    The post is very useful.

    I want to check the memory requested, granted, used ,explain plan for all sessions active or inactive in order by last executed. was trying to use third query. could you please help. Or if we can check for last 1 hour for all sessions

    SELECT mg.session_id, mg.requested_memory_kb, mg.granted_memory_kb, mg.used_memory_kb, t.text, qp.query_plan
    FROM sys.dm_exec_query_memory_grants AS mg
    CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
    CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
    ORDER BY 1 DESC OPTION (MAXDOP 1)

  7. FK said

    Sudarshan,

    Your shared queries are brilliant…Is there a way to see what objects were available in memory at a specific time in past?

    Also, I encountered below error while trying to execute the query to find objects in memory:

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int.

    please advise !
    Regards,
    FK

    • Thanks FK.

      You can’t trace objects in memory using a DMV based query as DMV’s are “current system state” only. You can write up a script/job to dump these into a table on a scheduled basis, using the query provided if you want historical data for investigation purposes.

      Also, I’ve modified the query provided for “Query to find the Buffer Pool usage per each Database”. Can you re-run the updated version & check if you are still getting the previous error. Let me know how this goes…

      • Foqia Khan said

        Thanks Sudarshan,

        I tried said query again but got same error:

        Msg 8115, Level 16, State 2, Line 1
        Arithmetic overflow error converting expression to data type int.

      • @FK – Not sure which query in this blog post you’re running, I modified “Query to identify objects that are taking up most of that memory in Buffer Pool” as well. Give it a try now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: