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! 