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文章来源：https://www.toymoban.com/news/detail-700057.html
;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文章来源地址https://www.toymoban.com/news/detail-700057.html
到了这里，关于查询sqlserver内存分配情况的SQL的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
