<i id='bDEVr'></i>

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