SELECT mg.granted_memory_kb, mg.session_id, 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)
USE master
GO
;WITH cte
AS ( SELECT RP.pool_id ,
RP.Name ,
RP.min_memory_percent ,
RP.max_memory_percent ,
CAST (RP.max_memory_kb / 1024. / 1024.
AS NUMERIC(12, 2)) AS max_memory_gb ,
CAST (RP.used_memory_kb / 1024. / 1024.
AS NUMERIC(12, 2)) AS used_memory_gb ,
CAST (RP.target_memory_kb / 1024. / 1024.
AS NUMERIC(12,2)) AS target_memory_gb,
CAST (SI.committed_target_kb / 1024. / 1024.
AS NUMERIC(12, 2)) AS committed_target_kb
FROM sys.dm_resource_governor_resource_pools RP
CROSS JOIN sys.dm_os_sys_info SI
)
SELECT c.pool_id ,
c.Name ,
c.min_memory_percent ,
c.max_memory_percent ,
c.max_memory_gb ,
c.used_memory_gb ,
c.target_memory_gb ,
CAST(c.committed_target_kb *
CASE WHEN c.committed_target_kb <= 8 THEN 0.7
WHEN c.committed_target_kb < 16 THEN 0.75
WHEN c.committed_target_kb < 32 THEN 0.8
WHEN c.committed_target_kb <= 96 THEN 0.85
WHEN c.committed_target_kb > 96 THEN 0.9
END * c.max_memory_percent /100 AS NUMERIC(12,2))
AS [Max_for_InMemory_Objects_gb]
FROM cte c