다음을 통해 공유


메모리 내 OLTP의 메모리 사용량 모니터링 및 문제 해결

적용 대상: SQL Server

메모리 내 OLTP에서는 디스크 기반 테이블과 다른 패턴으로 메모리를 사용합니다. 메모리 및 가비지 수집 하위 시스템에 제공된 DMV 또는 성능 카운터를 사용하여 데이터베이스의 메모리 최적화 테이블 및 인덱스에서 할당하고 사용하는 메모리 양을 모니터링할 수 있습니다. 이렇게 하면 시스템 및 데이터베이스 수준에서 가시성을 확보하고 메모리 소모로 인한 문제를 방지할 수 있습니다.

이 문서에서는 SQL Server에 대한 메모리 내 OLTP 메모리 사용량 모니터링을 설명합니다.

참고 항목

이 자습서는 Azure SQL Managed Instance 또는 Azure SQL 데이터베이스에는 적용되지 않습니다. 대신 Azure SQL에서 메모리 내 OLTP의 데모를 보려면 다음을 참조하세요.

메모리 내 OLTP 사용량 모니터링에 대한 자세한 내용은 다음을 참조하세요.

1. 메모리 최적화 테이블이 포함된 샘플 데이터베이스 만들기

다음 단계에서는 연습에 사용할 데이터베이스를 만듭니다.

  1. SQL Server Management Studio를 시작합니다.

  2. 새 쿼리를 선택합니다.

    참고 항목

    메모리 최적화 테이블이 포함된 데이터베이스가 이미 있는 경우 이 다음 단계를 건너뛸 수 있습니다.

  3. 이 코드를 새 쿼리 창에 붙여넣고 각 섹션을 실행하여 이 연습 IMOLTP_DB에 대한 테스트 데이터베이스를 만듭니다.

    -- create a database to be used  
    CREATE DATABASE IMOLTP_DB  
    GO
    
  4. 아래의 샘플 스크립트는 C:\Data를 사용하지만 인스턴스는 데이터베이스 데이터 파일에 다른 폴더 위치를 사용할 가능성이 높습니다. 메모리 내 파일 위치에 적절한 위치를 사용하도록 다음 스크립트를 업데이트하고 실행합니다.

    ALTER DATABASE IMOLTP_DB ADD FILEGROUP IMOLTP_DB_xtp_fg CONTAINS MEMORY_OPTIMIZED_DATA  
    ALTER DATABASE IMOLTP_DB ADD FILE( NAME = 'IMOLTP_DB_xtp' , FILENAME = 'C:\Data\IMOLTP_DB_xtp') TO FILEGROUP IMOLTP_DB_xtp_fg;  
    GO
    
  5. 다음 스크립트는 이 항목의 나머지 부분에서 사용할 수 있는 세 개의 메모리 최적화 테이블을 만듭니다. 이 예제에서는 데이터베이스를 리소스 풀에 매핑하여 메모리 최적화 테이블에서 가져올 수 있는 메모리의 양을 제어할 수 있습니다. IMOLTP_DB 데이터베이스에서 다음 스크립트를 실행합니다.

    -- create some tables  
    USE IMOLTP_DB  
    GO  
    
    -- create the resoure pool  
    CREATE RESOURCE POOL PoolIMOLTP WITH (MAX_MEMORY_PERCENT = 60);  
    ALTER RESOURCE GOVERNOR RECONFIGURE;  
    GO  
    
    -- bind the database to a resource pool  
    EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'PoolIMOLTP'  
    
    -- you can query the binding using the catalog view as described here  
    SELECT d.database_id  
         , d.name  
         , d.resource_pool_id  
    FROM sys.databases d  
    GO  
    
    -- take database offline/online to finalize the binding to the resource pool  
    USE master  
    GO  
    
    ALTER DATABASE IMOLTP_DB SET OFFLINE  
    GO  
    ALTER DATABASE IMOLTP_DB SET ONLINE  
    GO  
    
    -- create some tables  
    USE IMOLTP_DB  
    GO  
    
    -- create table t1  
    CREATE TABLE dbo.t1 (  
           c1 int NOT NULL CONSTRAINT [pk_t1_c1] PRIMARY KEY NONCLUSTERED  
         , c2 char(40) NOT NULL  
         , c3 char(8000) NOT NULL  
         ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
    GO  
    
    -- load t1 150K rows  
    DECLARE @i int = 0  
    BEGIN TRAN  
    WHILE (@i <= 150000)  
       BEGIN  
          INSERT t1 VALUES (@i, 'a', replicate ('b', 8000))  
          SET @i += 1;  
       END  
    Commit  
    GO  
    
    -- Create another table, t2  
    CREATE TABLE dbo.t2 (  
           c1 int NOT NULL CONSTRAINT [pk_t2_c1] PRIMARY KEY NONCLUSTERED  
         , c2 char(40) NOT NULL  
         , c3 char(8000) NOT NULL  
         ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
    GO  
    
    -- Create another table, t3   
    CREATE TABLE dbo.t3 (  
           c1 int NOT NULL CONSTRAINT [pk_t3_c1] PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 1000000)  
         , c2 char(40) NOT NULL  
         , c3 char(8000) NOT NULL  
         ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
    GO  
    

2. 메모리 사용량 모니터링

SQL Server Management Studio를 사용하여 메모리 사용량 모니터링

SQL Server 2014(12.x)부터 SQL Server Management Studio에는 메모리 내 테이블에서 사용하는 메모리를 모니터링하는 기본 제공 표준 보고서가 있습니다. 이러한 보고서는 개체 탐색기를 사용하여 액세스할 수 있습니다. 개체 탐색기를 사용하여 개별 메모리 최적화 테이블에서 사용하는 메모리를 모니터링할 수도 있습니다.

데이터베이스 수준의 사용량

다음과 같이 데이터베이스 수준에서 메모리 사용을 모니터링할 수 있습니다.

  1. SQL Server Management Studio를 시작하고 SQL Server 또는 SQL Managed Instance에 연결합니다.

  2. 개체 탐색기에서 원하는 보고서의 데이터베이스를 마우스 오른쪽 단추로 클릭합니다.

  3. 컨텍스트 메뉴에서 보고서 ->표준 보고서 ->메모리 최적화 개체의 메모리 사용량을 선택합니다.

보고서 > 표준 보고서 > 선택한 메모리 최적화 개체의 메모리 사용량을 보여주는 스크린샷.

이 보고서는 위에서 만든 데이터베이스의 메모리 사용량을 보여줍니다.

메모리 최적화 개체별 총 메모리 사용량 보고서의 스크린샷.

DMV를 사용하여 메모리 사용량 모니터링

메모리 최적화 테이블, 인덱스, 시스템 개체, 런타임 구조에서 사용하는 메모리를 모니터링하는 데 사용할 수 있는 다양한 DMV가 있습니다.

메모리 최적화 테이블 및 인덱스의 메모리 사용량

여기에 표시된 것과 같이 sys.dm_db_xtp_table_memory_stats 를 쿼리하여 모든 사용자 테이블, 인덱스 및 시스템 개체의 메모리 사용량을 확인할 수 있습니다.

SELECT object_name(object_id) AS [Name]
     , *  
   FROM sys.dm_db_xtp_table_memory_stats;

샘플 출력

Name       object_id   memory_allocated_for_table_kb memory_used_by_table_kb memory_allocated_for_indexes_kb memory_used_by_indexes_kb  
---------- ----------- ----------------------------- ----------------------- ------------------------------- -------------------------  
t3         629577281   0                             0                       128                             0  
t1         565577053   1372928                       1200008                 7872                            1942  
t2         597577167   0                             0                       128                             0  
NULL       -6          0                             0                       2                               2  
NULL       -5          0                             0                       24                              24  
NULL       -4          0                             0                       2                               2  
NULL       -3          0                             0                       2                               2  
NULL       -2          192                           25                      16                              16  

자세한 내용은 sys.dm_db_xtp_table_memory_stats를 참조하세요.

내부 시스템 구조별 메모리 사용량

메모리는 트랜잭션 구조, 데이터 및 델타 파일 버퍼, 가비지 수집 구조 등의 시스템 개체에서도 사용합니다. 이러한 시스템 개체에서 사용하는 메모리는 여기 표시된 대로 sys.dm_xtp_system_memory_consumers 를 쿼리하여 확인할 수 있습니다.

SELECT memory_consumer_desc  
     , allocated_bytes/1024 AS allocated_bytes_kb  
     , used_bytes/1024 AS used_bytes_kb  
     , allocation_count  
   FROM sys.dm_xtp_system_memory_consumers  

샘플 출력

memory_consumer_ desc allocated_bytes_kb   used_bytes_kb        allocation_count  
------------------------- -------------------- -------------------- ----------------  
VARHEAP                   0                    0                    0  
VARHEAP                   384                  0                    0  
DBG_GC_OUTSTANDING_T      64                   64                   910  
ACTIVE_TX_MAP_LOOKAS      0                    0                    0  
RECOVERY_TABLE_CACHE      0                    0                    0  
RECENTLY_USED_ROWS_L      192                  192                  261  
RANGE_CURSOR_LOOKSID      0                    0                    0  
HASH_CURSOR_LOOKASID      128                  128                  455  
SAVEPOINT_LOOKASIDE       0                    0                    0  
PARTIAL_INSERT_SET_L      192                  192                  351  
CONSTRAINT_SET_LOOKA      192                  192                  646  
SAVEPOINT_SET_LOOKAS      0                    0                    0  
WRITE_SET_LOOKASIDE       192                  192                  183  
SCAN_SET_LOOKASIDE        64                   64                   31  
READ_SET_LOOKASIDE        0                    0                    0  
TRANSACTION_LOOKASID      448                  448                  156  
PGPOOL:256K               768                  768                  3  
PGPOOL: 64K               0                    0                    0  
PGPOOL:  4K               0                    0                    0  

자세한 내용은 sys.dm_xtp_system_memory_consumers)를 참조하세요.

메모리 최적화 테이블에 액세스할 때 런타임의 메모리 사용량

다음 쿼리를 사용하는 프로시저 캐시와 같은 런타임 구조에서 사용되는 메모리를 확인할 수 있습니다. 이 쿼리를 실행하여 프로시저 캐시와 같은 런타임 구조에서 사용되는 메모리를 가져옵니다. 모든 런타임 구조에는 XTP로 태그가 지정됩니다.

SELECT memory_object_address  
     , pages_in_bytes  
     , bytes_used  
     , type  
   FROM sys.dm_os_memory_objects WHERE type LIKE '%xtp%'  

샘플 출력

memory_object_address pages_ in_bytes bytes_used type  
--------------------- ------------------- ---------- ----  
0x00000001F1EA8040    507904              NULL       MEMOBJ_XTPDB  
0x00000001F1EAA040    68337664            NULL       MEMOBJ_XTPDB  
0x00000001FD67A040    16384               NULL       MEMOBJ_XTPPROCCACHE  
0x00000001FD68C040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD284040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD302040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD382040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD402040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD482040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD502040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD67E040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001F813C040    8192                NULL       MEMOBJ_XTPBLOCKALLOC  
0x00000001F813E040    16842752            NULL       MEMOBJ_XTPBLOCKALLOC  

자세한 내용은 sys.dm_os_memory_objects(Transact-SQL)를 참조하세요.

인스턴스 전체에서 메모리 내 OLTP 엔진에서 사용하는 메모리

메모리 내 OLTP 엔진 및 메모리 최적화 개체에 할당된 메모리는 SQL Server 인스턴스 내 다른 메모리 소비자와 동일한 방식으로 관리됩니다. MEMORYCLERK_XTP 유형의 클럭은 메모리 내 OLTP 엔진에 할당된 모든 메모리를 고려합니다. 다음 쿼리를 사용하여 메모리 내 OLTP 엔진에서 사용한 모든 메모리를 찾습니다.

-- This DMV accounts for all memory used by the in-memory engine  
SELECT type  
   , name  
   , memory_node_id  
   , pages_kb/1024 AS pages_MB   
   FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'  

다음 샘플 출력에서는 할당된 메모리가 18MB 시스템 수준 메모리이고 database_id = 5에 1358MB가 할당되었음을 나타냅니다. 이 데이터베이스는 전용 리소스 풀에 매핑되므로 이 메모리는 해당 리소스 풀에서 고려됩니다.

type                 name       memory_node_id pages_MB  
-------------------- ---------- -------------- --------------------  
MEMORYCLERK_XTP      Default    0              18  
MEMORYCLERK_XTP      DB_ID_5    0              1358  
MEMORYCLERK_XTP      Default    64             0  

자세한 내용은 sys.dm_os_memory_clerks를 참조하세요.

3. 메모리 최적화 개체에서 사용하는 메모리 관리

명명된 리소스 풀에 바인딩하여 메모리 최적화 테이블에서 사용하는 총 메모리를 제어할 수 있습니다. 자세한 내용은 메모리 최적화 테이블이 있는 데이터베이스를 리소스 풀에 바인딩을 참조하세요.

메모리 문제 해결

다음 3단계 프로세스를 통해 메모리 문제를 해결합니다.

  1. 데이터베이스 또는 인스턴스의 개체에서 사용 중인 메모리 크기를 식별합니다. 앞에서 설명한 대로 메모리 최적화 테이블에 사용 가능한 다양한 모니터링 도구 집합을 사용할 수 있습니다. 예를 들어 DMV sys.dm_db_xtp_table_memory_stats 또는 sys.dm_os_memory_clerks의 샘플 쿼리를 참조하세요.

  2. 메모리 사용량이 증가하는 방식과 남은 헤드룸의 양을 결정합니다. 메모리 사용량을 주기적으로 모니터링하면 메모리 사용량이 증가하는 방식을 알 수 있습니다. 예를 들어 데이터베이스를 명명된 리소스 풀에 매핑한 경우 성능 카운터가 사용한 메모리(KB)를 모니터링하여 메모리 사용량이 증가하는 방식을 확인할 수 있습니다.

  3. 잠재적 메모리 문제를 완화하기 위한 조치를 취합니다. 자세한 내용은 OOM(메모리 부족) 문제 해결을 참조하세요.