MSSQL 메모리 사용량 관련 조회 쿼리들

SQL Server 인스턴스의 전체 메모리 사용량

-- SQL Server 인스턴스의 전체 메모리 사용량
SELECT
    (physical_memory_in_use_kb / 1024) AS SQLServerMemoryUsed_MB,
    (locked_page_allocations_kb / 1024) AS SQLServerLockedPages_MB,
    (total_virtual_address_space_kb / 1024) AS SQLServerVASpace_MB,
    process_physical_memory_low AS MemoryLowIndicator,
    *
FROM
    sys.dm_os_process_memory;

메모리 클럭별 메모리 사용량

-- 메모리 클럭별 메모리 사용량
SELECT
    type AS MemoryClerkType,
    name AS MemoryClerkName,
    pages_kb / 1024 AS MemoryUsage_MB
FROM
    sys.dm_os_memory_clerks
ORDER BY
    pages_kb DESC;

버퍼 풀 상태 확인

-- 버퍼 풀 상태 확인
SELECT
    database_id,
    file_id,
    page_id,
    page_level,
    allocation_unit_id,
    page_type,
    row_count,
    free_space_in_bytes,
    is_modified,
    read_microsec
FROM
    sys.dm_os_buffer_descriptors;

현재 실행 중인 쿼리와 그 메모리 사용량

-- 현재 실행 중인 쿼리와 그 메모리 사용량
SELECT
    r.session_id,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.writes,
    r.reads,
    s.memory_usage * 8 AS MemoryUsage_KB,
    SUBSTRING(qt.text, (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1) AS query_text
FROM
    sys.dm_exec_requests r
JOIN
    sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) AS qt
ORDER BY
    s.memory_usage DESC;

인덱스 캐시 메모리 사용량

-- 인덱스 캐시 메모리 사용량
SELECT
    obj.name AS ObjectName,
    ind.name AS IndexName,
    p.partition_number,
    au.type_desc AS AllocationType,
    p.rows AS RowCounts,
    au.total_pages AS TotalPages,
    au.used_pages AS UsedPages,
    au.data_pages AS DataPages,
    (au.data_pages * 8) / 1024 AS DataSpace_MB
FROM
    sys.partitions p
INNER JOIN
    sys.allocation_units au ON p.hobt_id = au.container_id
INNER JOIN
    sys.indexes ind ON p.object_id = ind.object_id AND p.index_id = ind.index_id
INNER JOIN
    sys.objects obj ON p.object_id = obj.object_id
ORDER BY
    DataSpace_MB DESC;

프로시저 캐시 메모리 사용량

-- 프로시저 캐시 메모리 사용량
SELECT
    objtype AS ObjectType,
    usecounts AS ExecutionCount,
    size_in_bytes / 1024 AS SizeInKB,
    text AS SQLText
FROM
    sys.dm_exec_cached_plans
CROSS APPLY
    sys.dm_exec_sql_text(plan_handle)
ORDER BY
    size_in_bytes DESC;