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
-- 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
-- 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
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
-- 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
That’s all for now folks!
- TheSQLDude (Sudarshan)
The SQL Dude! 
In Depth SQL said
Hey Sudarshan – This post is very useful, Thanks for sharing the info.
Sudarshan Narasimhan said
Thanks Gaurav.
Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory grants & waits by session | SQL DBA learning curve said
[…] Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory gran…. […]
DBCC MEMORYSTATUS : How is Stolen Potential calculated « SQLactions.com said
[…] 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. […]
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?
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?
Sudarshan Narasimhan said
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.
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)
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
Sudarshan Narasimhan said
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.
Sudarshan Narasimhan said
@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.