SQL Server Memory Pressure


  1. 說明
  2. TSQL

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

SQL Server Logo

說明

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;