查询sqlserver内存分配情况的SQL

最新推荐文章于 2024-09-13 11:27:34 发布

原创最新推荐文章于 2024-09-13 11:27:34 发布 · 583 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #sqlserver #数据库

数据库专栏收录该内容

19 篇文章

订阅专栏

博客展示了两条SQL查询语句。一条用于查询内存授予信息，涉及sys.dm_exec_query_memory_grants等系统视图；另一条使用CTE查询资源池相关信息，包括内存百分比、已用内存等，并根据条件计算内存对象的最大值。

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