1. 查询内存配置

# 开启高级选项
EXEC sys.sp_configure N'show advanced options', N'1' ;

# 查看最大最小内存配置
EXEC sys.sp_configure N'max server memory (MB)' ;
EXEC sys.sp_configure N'min server memory (MB)' ;

# 修改内存配置(按需执行)
EXEC sys.sp_configure N'max server memory (MB)', N'10240' ;
EXEC sys.sp_configure N'min server memory (MB)', N'0' ;
RECONFIGURE WITH OVERRIDE;

2. 查询总内存使用情况

SELECT 
    (physical_memory_in_use_kb / 1024) AS Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb / 1024) AS Locked_pages_used_Sqlserver_MB,
    (virtual_address_space_committed_kb / 1024) AS Total_memory_allocated_for_SQL_Server_MB,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;

# 查询所有内存分配情况
SELECT 
    type,
    SUM(pages_kb)/1024 AS [Memory_Usage_MB]
FROM sys.dm_os_memory_clerks
GROUP BY type
order by Memory_Usage_MB desc

3. 查询每个会话连接使用内存

要查询每个会话连接使用的内存，针对不同版本的 SQL Server，使用不同的动态管理视图 (DMVs) 和功能。以下是针对 SQL Server 2014、2016、2019、2022 的 T-SQL 查询示例：

SQL Server 2014 和 2016

SQL Server 2014 和 2016 没有太多变化，可以使用相同的查询。

WITH memory_usage AS (
    SELECT 
        session_id,
        SUM(internal_objects_alloc_page_count) * 8 AS internal_objects_alloc_kb,
        SUM(user_objects_alloc_page_count) * 8 AS user_objects_alloc_kb
    FROM sys.dm_db_session_space_usage
    GROUP BY session_id
),
requests_memory AS (
    SELECT 
        session_id,
        SUM(granted_memory_kb) AS request_alloc_memory_kb
    FROM sys.dm_exec_query_memory_grants
    GROUP BY session_id
)
SELECT 
    s.session_id,
    s.host_name,
    s.login_name,
    ISNULL(mu.internal_objects_alloc_kb, 0) AS internal_objects_alloc_kb,
    ISNULL(mu.user_objects_alloc_kb, 0) AS user_objects_alloc_kb,
    ISNULL(rm.request_alloc_memory_kb, 0) AS request_alloc_memory_kb,
    (ISNULL(mu.internal_objects_alloc_kb, 0) +
     ISNULL(mu.user_objects_alloc_kb, 0) +
     ISNULL(rm.request_alloc_memory_kb, 0)) AS total_memory_kb
FROM sys.dm_exec_sessions s
LEFT JOIN memory_usage mu ON s.session_id = mu.session_id
LEFT JOIN requests_memory rm ON s.session_id = rm.session_id
WHERE s.session_id <> @@SPID
ORDER BY total_memory_kb DESC;

SQL Server 2019 和 2022

从 SQL Server 2019 开始，DMV 的内容和功能有一些增强，可以使用相同的查询。

WITH memory_usage AS (
    SELECT 
        session_id,
        SUM(internal_objects_alloc_page_count) * 8 AS internal_objects_alloc_kb,
        SUM(user_objects_alloc_page_count) * 8 AS user_objects_alloc_kb
    FROM sys.dm_db_session_space_usage
    GROUP BY session_id
),
requests_memory AS (
    SELECT 
        session_id,
        SUM(granted_memory_kb) AS request_alloc_memory_kb
    FROM sys.dm_exec_query_memory_grants
    GROUP BY session_id
)
SELECT 
    s.session_id,
    s.host_name,
    s.login_name,
    ISNULL(mu.internal_objects_alloc_kb, 0) AS internal_objects_alloc_kb,
    ISNULL(mu.user_objects_alloc_kb, 0) AS user_objects_alloc_kb,
    ISNULL(rm.request_alloc_memory_kb, 0) AS request_alloc_memory_kb,
    (ISNULL(mu.internal_objects_alloc_kb, 0) +
     ISNULL(mu.user_objects_alloc_kb, 0) +
     ISNULL(rm.request_alloc_memory_kb, 0)) AS total_memory_kb
FROM sys.dm_exec_sessions s
LEFT JOIN memory_usage mu ON s.session_id = mu.session_id
LEFT JOIN requests_memory rm ON s.session_id = rm.session_id
WHERE s.session_id <> @@SPID
ORDER BY total_memory_kb DESC;

解释

• memory_usage CTE: 计算每个会话的内部对象和用户对象分配的页数，并转换为 KB（每页 8 KB）。
• requests_memory CTE: 计算每个会话的请求分配的内存（KB）。
• 主查询: 联接 sys.dm_exec_sessions、memory_usage 和 requests_memory 视图，获取每个会话的主机名、登录名、内部对象分配的内存、用户对象分配的内存和请求分配的内存，并按总内存使用量排序。

通过这种方式，你可以获取每个会话的内存使用情况。根据 SQL Server 版本的不同，这些查询会有所不同，但主要区别在于数据来源的视图和方法。在较新的版本中可能会有更多的内存使用信息和新的视图可以使用，但上述查询已涵盖主要的内存使用统计。

4. 查询缓冲池（Buffer Pool）

缓冲池 (Buffer Pool): 存储数据页和索引页，减少磁盘 I/O。

SELECT
    object_name,
    counter_name,
    cntr_value/1024 AS [Memory_Usage_MB]
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name IN ('Database pages', 'Total pages');

5. 查询计划缓存 (Plan Cache)

计划缓存 (Plan Cache)： 存储查询计划以提高执行速度。

SELECT 
    objtype AS [Cache_Type], 
    COUNT(*) AS [Total Plans], 
    SUM(size_in_bytes) / 1024 / 1024 AS [Total Memory (MB)]
FROM 
    sys.dm_exec_cached_plans
GROUP BY 
    objtype
ORDER BY 
    [Total Memory (MB)] DESC;

6. 查询锁内存 (Lock Memory)

锁内存 (Lock Memory): 存储锁信息。

SELECT
    pages_kb AS [Memory_Usage_KB]
FROM sys.dm_os_memory_clerks
WHERE type = 'LOCK_MANAGER';

7. 查询缓冲区描述符 (Buffer Descriptors)

缓冲区描述符 (Buffer Descriptors): 描述缓冲池中的数据页。

SELECT
    COUNT(*) * 8 / 1024 AS [Buffer_Pool_MB]
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767;

8. 查询列存储池 (Columnstore Pool)

列存储池 (Columnstore Pool): 存储列存储索引的数据。

SELECT
    object_name,
    counter_name,
    cntr_value AS [Memory_Usage_KB]
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Columnstore';

9. 查询日志缓冲区 (Log Buffer)

日志缓冲区 (Log Buffer): 用于日志写入。

SELECT
    object_name,
    counter_name,
    cntr_value AS [Memory_Usage_KB]
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Log Buffer';

10. 查询内存授予 (Memory Grants)

内存授予 (Memory Grants): 临时内存分配给查询执行。

SELECT
    session_id,
    grant_time,
    requested_memory_kb /1024 as requested_memory_mb ,
    granted_memory_kb/1024 as granted_memory_mb
FROM sys.dm_exec_query_memory_grants
ORDER BY grant_time DESC;

11. CLR 内存 (CLR Memory)

CLR 内存 (CLR Memory): 用于 CLR 代码执行。

SELECT
    type,
    SUM(pages_kb)/1024 AS [Memory_Usage_MB]
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLCLR'

12. 扩展存储过程内存 (Extended Stored Procedures Memory)

Extended Stored Procedures 内存 (Extended Stored Procedures Memory): 用于扩展存储过程。

SELECT
    type,
    SUM(pages_kb) AS [Memory_Usage_KB]
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_XPROC'
GROUP BY type