메모리 내 OLTP를 사용하여 메모리 사용량 모니터링 및 문제 해결
적용 대상:
SQL Server
메모리 내 OLTP는 디스크 기반 테이블과 다른 패턴으로 메모리를 사용합니다. 메모리 및 가비지 수집 하위 시스템에 제공된 DMV 또는 성능 카운터를 사용하여 데이터베이스의 메모리 최적화 테이블 및 인덱스에서 할당하고 사용하는 메모리 양을 모니터링할 수 있습니다. 이렇게 하면 시스템 및 데이터베이스 수준에서 가시성을 제공하고 메모리 소모로 인한 문제를 방지할 수 있습니다.
이 문서에서는 SQL Server에 대한 메모리 내 OLTP 메모리 사용량 모니터링에 대해 설명합니다.
참고 항목
이 자습서는 Azure SQL Managed Instance 또는 Azure SQL Database에는 적용되지 않습니다. 대신 Azure SQL에서 메모리 내 OLTP의 데모를 보려면 다음을 참조하세요.
메모리 내 OLTP 사용량 모니터링에 대한 자세한 내용은 다음을 참조하세요.
1. 메모리 최적화 테이블을 사용하여 샘플 데이터베이스 만들기
다음 단계에서는 연습에 사용할 데이터베이스를 만듭니다.
SQL Server Management Studio를 시작합니다.
새 쿼리를 선택합니다.
참고 항목
메모리 최적화 테이블이 있는 데이터베이스가 이미 있는 경우 이 다음 단계를 건너뛸 수 있습니다.
이 코드를 새 쿼리 창에 붙여넣고 각 섹션을 실행하여 이 연습
IMOLTP_DB에 대한 테스트 데이터베이스를 만듭니다.-- create a database to be used CREATE DATABASE IMOLTP_DB GO아래 샘플 스크립트는 사용
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다음 스크립트는 이 항목의 re기본der에서 사용할 수 있는 세 개의 메모리 최적화 테이블을 만듭니다. 이 예제에서는 데이터베이스를 리소스 풀에 매핑하여 메모리 최적화 테이블에서 가져올 수 있는 메모리의 양을 제어할 수 있습니다. 데이터베이스에서 다음 스크립트를 실행합니다
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에는 메모리 내 테이블에서 사용하는 메모리를 모니터링하는 기본 제공 표준 보고서가 있습니다. 개체 탐색기 사용하여 이러한 보고서에 액세스할 수 있습니다. 개체 탐색기를 사용하여 개별 메모리 최적화 테이블에서 사용하는 메모리를 모니터링할 수도 있습니다.
데이터베이스 수준의 사용량
다음과 같이 데이터베이스 수준에서 메모리 사용을 모니터링할 수 있습니다.
SQL Server Management Studio를 시작하고 SQL Server 또는 SQL 관리형 인스턴스에 연결합니다.
개체 탐색기 보고서를 표시할 데이터베이스를 마우스 오른쪽 단추로 클릭합니다.
상황에 맞는 메뉴에서 보고서 ->표준 보고서 ->메모리 최적화 개체별 메모리 사용량 선택
이 보고서는 위에서 만든 데이터베이스의 메모리 사용량을 보여줍니다.
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 시스템 수준 메모리이고 5에 할당된 1358MB임을 database_id 보여줍니다. 이 데이터베이스는 전용 리소스 풀에 매핑되므로 이 메모리는 해당 리소스 풀에서 고려됩니다.
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단계 프로세스를 통해 메모리 문제를 해결합니다.
데이터베이스 또는 인스턴스의 개체에서 사용 중인 메모리 양을 식별합니다. 앞에서 설명한 대로 메모리 최적화 테이블에 사용할 수 있는 다양한 모니터링 도구 집합을 사용할 수 있습니다. 예를 들어 DMV 또는
sys.dm_os_memory_clerks.의 샘플 쿼리를 참조하세요sys.dm_db_xtp_table_memory_stats.메모리 사용량이 증가하는 방법과 남은 헤드룸의 양을 결정합니다. 메모리 사용량을 주기적으로 모니터링하면 메모리 사용량이 증가하는 방식을 알 수 있습니다. 예를 들어 데이터베이스를 명명된 리소스 풀에 매핑한 경우 성능 카운터 KB(사용된 메모리)를 모니터링하여 메모리 사용량이 증가하는 방식을 확인할 수 있습니다.
잠재적인 메모리 문제를 완화하기 위한 조치를 취합니다. 자세한 내용은 OOM(메모리 부족) 문제 해결을 참조하세요.
관련 콘텐츠
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기