SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링

1. SMSS 표준 보고서를 사용하여 사용 가능한 리소스를 빠르게 확인
2. T-SQL을 사용하여 SQL Server 메모리 내 데이터베이스를 모니터링하는 방법
3. 메모리 소비자의 내부 구조를 자세히 설명합니다.
4. DMF를 사용하여 SQL Server 내에서 공간 관리 추진
5. SQL Server 내에서 메모리 내 컨테이너 모니터링
6. 메모리 내 체크 포인트 모드의 세부 정보를 가져 오는 방법
7. In-Memory 데이터베이스 내 사용 / 여유 공간 결정
8. 그리고 더…

소개

기술이 발전하고 환경이 관계형 데이터베이스 트랜잭션을 비 휘발성 공간에서 휘발성 공간으로 전환함에 따라 데이터베이스 모니터링 및 유지 관리의 복잡성이 더욱 심해집니다 (때로는 지루함). 따라서 데이터베이스 모니터링 및 데이터베이스 메트릭 평가가 중요합니다. 모니터링 및 평가의 목적은 구현 및 출력을 체계적으로 추적하고 표준의 효과를 측정하여 변경이 필요한시기와시기를 정확히 결정하는 것입니다.

시작하기

SSMS 표준 보고서를 사용하여 SQL Server 메모리 내 개체에 대한 높은 수준의 세부 정보를 얻는 방법을 살펴 보겠습니다. 표준 SSMS 보고서 인 “메모리 최적화 개체 별 메모리 사용량”은 SQL Server 메모리 내 최적화 개체의 메모리 사용량 세부 정보를 보여줍니다.

메모리 최적화 객체 별 메모리 사용량 보고서를 보려면 다음과 같이하십시오.

1. 에서  개체 탐색기 에서 SQL Server 데이터베이스 엔진의 인스턴스에 연결 한 다음 해당 인스턴스를 확장합니다.
2. 데이터베이스를 마우스 오른쪽 단추로 클릭  한 다음 보고서 를 클릭합니다. 
3. 메모리 최적화 개체 별 메모리 사용량 선택
   
   

   

메모리 최적화 개체 별 메모리 사용량의 세 섹션은 다음과 같습니다.

1. 메모리 최적화 개체에 할당 된 총 메모리

   DMV sys.dm_db_xtp_table_memory_stats를 사용하여 메모리 최적화 개체에 할당 된 총 메모리를 파생 할 수 있습니다.

   아래 SQL은 SSMS 보고서에서도 생성 할 수있는 출력을 생성합니다.

   IF (     SELECT COUNT(1)     FROM sys.data_spaces     WHERE type = 'FX' ) > 0     BEGIN         SELECT SUM([Total used Memory MB] + [Total Unused Memory MB] + [Index used Memory MB] + [Index Unused Memory MB]) [Total memory allocated to memory optimized objects MB]         FROM         (             SELECT OBJECT_NAME(object_id) AS tblName,                    CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],                    CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],                    CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],                    CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]             FROM sys.dm_db_xtp_table_memory_stats         ) T;     END;

    

   

2. 메모리 최적화 개체 별 총 메모리 사용량, 테이블, 인덱스 및 시스템의 메모리 사용량을 보여주는 원형 차트.
   
   

   

3. SQL Server 메모리 내 테이블의 메모리 사용량 세부 정보

   관련 DMV sys.dm_db_xtp_table_memory_stats 를 사용하여 메모리 최적화 테이블의 크기 조정 세부 정보를 그릴 수도 있습니다 .

   IF (     SELECT COUNT(1)     FROM sys.data_spaces     WHERE type = 'FX' ) > 0     BEGIN         SELECT OBJECT_NAME(object_id) AS tblName,                CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],                CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],                CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],                CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]         FROM sys.dm_db_xtp_table_memory_stats;     END;

    

   

내부 시스템 구조 구성 요소에 의한 메모리 소비

시스템 할당자가 사용한 총 메모리를 보려면 다음 쿼리를 사용하십시오.

SELECT SUM(allocated_bytes) / (1024 * 1024) AS total_allocated_MB,        SUM(used_bytes) / (1024 * 1024) AS total_used_MB FROM sys.dm_xtp_system_memory_consumers;

 

다음으로 다양한 메모리 소비자 유형을 살펴 보겠습니다.

SELECT memory_consumer_type,        memory_consumer_type_desc,        allocated_bytes / 1024 [allocated_bytes_kb],        used_bytes / 1024 [used_bytes_kb],        allocation_count FROM sys.dm_xtp_system_memory_consumers;

다음 출력에서 ​​몇 가지 메모리 LOOKASIDE 소비자가 있음을 알 수 있습니다.

참고 : lookaside 소비자는 MMU (메모리 관리 장치)의 일부이며 각 트랜잭션의 메모리 위치에 액세스하는 데 걸리는 시간을 줄이는 데 사용되는 버퍼 캐시 유형입니다.

SQL Server 메모리 내 최적화 된 테이블에 액세스 할 때 런타임에 메모리 사용량을 확인하려면 다음 쿼리를 사용하십시오.

SELECT type clerk_type,        name,        memory_node_id,        pages_kb / 1024 pages_mb FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%';

 

특성	데이터 형식	기술
이름	nvarchar (256)	이 메모리 담당자의 내부적으로 할당 된 이름을 지정합니다. 구성 요소에는 특정 유형의 여러 메모리 담당자가있을 수 있습니다. 구성 요소는 특정 이름을 사용하여 동일한 유형의 메모리 담당자를 식별하도록 선택할 수 있습니다. nullable이 아닌 열입니다.
memory_node_id	smallint	메모리 노드의 ID를 반환합니다. 또한 nullable이 아닌 필드입니다.
pages_kb	Bigint	적용 대상 : SQL Server 2012 ~ SQL Server 2017.
		이 메모리 클러 크에 할당 된 페이지 메모리의 양 (KB)을 지정합니다. nullable 열이 아닙니다.

출력은 데이터베이스 ID 13이 메모리에서 173KB를 예약 함을 보여줍니다.

이제 sys.dm_os_memory_clerks를 사용하여 내부적으로 메모리를 사용하는 SQL Server의 다른 구성 요소가 무엇인지 살펴 보겠습니다. 그러면 SQL Server 내에서 메모리 클러 크의 활성 구성 요소에 대한 개요가 제공됩니다.

SELECT TOP (10) type MemoryClerkType,                 SUM(pages_kb) / 1024 AS SizeMB FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 2 DESC;

 

SQL Server 메모리 내 최적화 된 검사 점 모드

오류 로그에서 SQL 인스턴스의 SQL Server 메모리 내 검사 점 모드에 대한 세부 정보를 얻으려면 SQL 인스턴스의 메모리 최적화 검사 점 모드를 나열하는 다음 쿼리를 실행합니다.

EXEC sys.xp_readerrorlog 0, 1, N'In-Memory OLTP initialized on';

출력은 세 가지 가능한 값 중 하나를 나열 할 수 있습니다.

• 저가형 컴퓨터에서 초기화 된 메모리 내 OLTP
• 표준 컴퓨터에서 초기화 된 메모리 내 OLTP
• 고급 시스템에서 초기화 된 메모리 내 OLTP (대형 체크 포인트 모드)

앞서 언급 한 xp_readerrorlog 쿼리 는 SQL Server 오류 로그 파일을 읽습니다. 시작하는 동안 SQL Server는 시스템이 대형 체크 포인트를 사용하고 있는지 여부를 파악합니다. 다시 시작할 때마다 SQL Server가 시작될 때마다 시스템 구성 및 사양을 확인하고 SQL Server 메모리 내 OLTP 검사 점 파일을 읽을 수있는 방법도 빠르게 측정합니다. 따라서 쿼리를 실행하면 저가형 컴퓨터에서 초기화되었다는 텍스트 메시지가 표시됩니다. 그 이유는 시스템이 매우 적은 리소스로 구성되어 있기 때문입니다.

참고 : 대형 체크 포인트 모드를 사용하도록 시스템을 구성하기 위해 추적 플래그 9912가 활성화되었습니다. 어떤 경우 든 워크로드에 더 적합한 성능을 측정하는 것이 좋습니다.

출력에 고급 컴퓨터에서 초기화 된 SQL Server In-Memory OLTP가 표시되면 다음 필수 사항을 충족해야합니다.

1. 서버에는 16 개 이상의 논리 프로세서가 있어야합니다.
2. 128GB 이상의 메모리로 구성해야합니다.
3. > 200MB / 초 IO 인 높은 트랜잭션 속도 시스템.

노트 :

1. SQL Server 2016 CU1 이상에서는 대규모 검사 점 모드를 사용하려면 전역 추적 지연 9912를 사용하도록 설정해야합니다.
2. 대형 체크 포인트는 높은 처리량과 높은 트랜잭션 속도 시스템을 위해 설계되었습니다.
3. 앞서 언급 한 문 중 하나 또는 두 개가 참이면 출력에 "표준 컴퓨터에서 초기화 된 메모리 내 OLTP"가 표시됩니다.
4. 세 가지 문이 모두 참이면 SQL 인스턴스는 "고급 컴퓨터에서 초기화 된 메모리 내 OLTP"로 구성됩니다.
5. 세 문 중 어느 것도 양호한 상태가 아니면 "저사양 시스템에서 초기화 된 메모리 내 OLTP"출력을 반환합니다.

인스턴스 수준에서 XTPSupported를 확인하는 방법

다음 쿼리를 실행하여 인스턴스에 대한 유용한 정보를 볼 수 있습니다. 속성을 나열하는 내장 함수 인 SERVERPROPERTY를 사용합니다.

SELECT SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported]

 

SQL Server 메모리 내 컨테이너 모니터링

DMV sys.dm_db_xtp_checkpoint_files 는 데이터 파일과 델타 파일이라는 두 가지 유형의 파일을 사용하여 데이터베이스의 체크 포인트 파일 쌍에 대한 정보를 제공합니다. 데이터 파일은 삽입 된 레코드를 저장하고 델타는 제거 된 레코드를 저장합니다. 이러한 레코드는 가비지 수집 원칙을 사용하여 시간이 지남에 따라 병합됩니다. 세부 사항이있는 각 파일을 보려면 sys.dm_db_xtp_checkpoint_files 확장 저장소 프로 시저를 사용하십시오 .

sp_spaceused  – sp_spaceused는 이제 SQL Server 메모리 내 OLTP와 관련된 저장소 공간을 이해하는 데 사용할 수 있습니다. 관련 데이터를 가져 오기 위해 새 매개 변수 '@include_total_xtp_storage'가 저장 프로 시저에 추가되었습니다.

USE SQLShackInMemDB; GO EXEC sp_spaceused      @updateusage = 'FALSE',      @mode = 'ALL',      @oneresultset = '1',      @include_total_xtp_storage = '1'; GO

 

xtp_precreated, xtp_used 및 xtp_pending_truncation 은 체크 포인트 파일 쌍의 상태를 기반으로합니다. 이 포인터는 데이터베이스 백업의 시작 여부에 대한 정보를 제공합니다. 이것이 sp_spaceused 출력에서 ​​XTP 열이 파생되는 방식입니다.