SQL Server Memory Pressure

2024-07-29

筆記 SQL Server Memory Pressures 的相關知識與解決方法。

說明

We can check wait types to see if there are memory pressure issues.

SELECT 
    wait_type,
    wait_time_ms / 1000.0 AS wait_time_sec,
    wait_time_ms / (1000.0 * 60) AS wait_time_min
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%MEMORY%';

If there are memory pressure issues, MEMORY_ALLOCATION_EXT and RESERVED_MEMORY_ALLOCATION_EXT will be high.

TSQL

SQL Query to Retrieve Active Sessions with Execution Times and Resource Usage

SELECT 
    session_id,
    status,
    start_time,
    total_elapsed_time / 1000 AS total_elapsed_time_seconds,
    cpu_time / 1000 AS cpu_time_seconds,
    logical_reads,
    reads,
    writes,
    text AS query_text
FROM 
    sys.dm_exec_requests
CROSS APPLY 
    sys.dm_exec_sql_text(sql_handle)
WHERE 
    total_elapsed_time > 0
ORDER BY 
    total_elapsed_time DESC;

SQL Query to Identify and Analyze Blocking Sessions and Their Wait Times

SELECT 
    blocked.blocking_session_id AS BlockingSessionID,
    blocked.session_id AS BlockedSessionID,
    blocked.wait_type,
    blocked.wait_time / 1000 AS wait_time_seconds,
    blocked.wait_resource,
    blocking_sql.text AS BlockingSQLText,
    blocked_sql.text AS BlockedSQLText
FROM 
    sys.dm_exec_requests AS blocked
OUTER APPLY 
    sys.dm_exec_sql_text(blocked.sql_handle) AS blocked_sql
LEFT JOIN 
    sys.dm_exec_requests AS blocking
    ON blocked.blocking_session_id = blocking.session_id
OUTER APPLY 
    sys.dm_exec_sql_text(blocking.sql_handle) AS blocking_sql
WHERE 
    blocked.blocking_session_id <> 0
ORDER BY 
    blocked.wait_time DESC;