SQL Server의 메모리 내 OLTP에 대한 간략한 개요

1. 메모리 내 OLTP 진화의 세부 사항
2. 메모리 내 최적화 된 테이블을 보여주는 샘플 제공
3. 템플릿을 사용하여 메모리 내 최적화 테이블 만들기
4. 메모리 내 메모리 사용 고려 사항 설명
5. 그리고 더…

SQL Database의 메모리 내 기술은 응용 프로그램의 성능을 향상시킬 수 있으며 데이터 수집, 데이터로드 및 분석 쿼리와 같은 워크로드에 적합한 선택입니다.

빠르게 추진되는 혁신에 적응하고 새로운 경쟁을 충족하는 비즈니스의 능력은 도전입니다. DBA의 까다로운 작업은 항상 변화하는 요구 사항을 채택하고 서비스 및 기업의 요구 사항을 충족하는 올바른 설계 전략을 공식화하는 경향이 있습니다. 우리 중 많은 사람들이 확장 옵션의 원활한 통합을위한 절충 요소에 대해 의문을 갖게 될 것이며 고성능 컴퓨팅 시스템을 설계하려면 더 나은 하드웨어 구성에 크게 의존합니다. 메모리 비용 감소, 멀티 코어 프로세서, CPU 클럭 속도 증가와 같은 하드웨어 추세가 급증하는 하드웨어 시대는 인 메모리 컴퓨팅의 아키텍처 설계를 촉진했습니다.

성능이 핵심이고 시스템이 거의 실시간 데이터에 대해 작동해야하는 시스템에서 In-Memory 기술 솔루션이 선택됩니다. 기술이 유행하는 방식은 이러한 새로운 기능의 진화로 이어집니다.

소개

메모리 내 OLTP는 SQL Server에 통합 된 메모리 최적화 된 관계형 데이터 관리 엔진이자 네이티브 저장 프로 시저 컴파일러입니다. Microsoft는 가장 까다로운 OLTP 워크로드를 처리하기 위해 메모리 내 OLTP를 설계했습니다. 대부분의 경우 모든 로깅 및 I / O를 방지하기 위해 DURABILITY = SCHEMA_ONLY 옵션을 사용하여 메모리 최적화 테이블을 만들 수 있습니다.

메모리 내 OLTP는 다음 개념을 도입합니다.

• 메모리 내 최적화 된 테이블 및 인덱스
• 비 내구성 테이블, 기존 임시 테이블
• 고유하게 컴파일 된 저장 프로 시저 및 UDF
• 테이블 변수에 대한 메모리 최적화 테이블 유형 – 임시 개체의 대체로 사용할 수 있습니다.
• 그리고 더…

메모리 내 OLTP 시스템의 의미

• 짧은 대기 시간, 높은 처리량, 빠른 응답 시간
• 고효율
• 고성능
• 잠금 에스컬레이션이 없거나 전혀없는 관리는 낙관적 동시성 모델, 더 나은 동시성 관리를 통해 이루어집니다.

메모리 내 OLTP 권장?

다음 조건 중 하나 이상이 "예"인 시스템이 메모리 내 OLTP로 마이그레이션 할 때 얻을 수있는 잠재적 인 이점을 심각하게 고려하십시오.

• 성능 및 확장 성 향상이 필요한 기존 SQL Server (또는 기타 관계형 데이터베이스) 애플리케이션
• 데이터베이스 병목 현상을 겪고있는 RDBMS – 주로 잠금 / 래칭 또는 코드 실행 관련
• 인지 된 성능 오버 헤드로 인해 중요한 성능 경로에서 관계형 데이터베이스를 사용하지 않는 환경

혜택

• 경합 제거
• I / O 로깅 최소화
• 효율적인 데이터 검색
• 코드 실행 시간 최소화
• CPU 효율성
• I / O 감소 / 제거

한정

• 테이블에는 하나 이상의 인덱스가 있어야합니다.
• HEAP의 개념 없음
• 응용 프로그램 잠금을 제외하고 메모리 내 OLTP는 표준 SQL Server 쿼리와 같은 레코드를 잠그는 기능을 제공하지 않습니다.
• 메모리 제한

앞서 언급했듯이 메모리 최적화 테이블을 구성하는 데이터 구조는 모두 메모리에 저장되며 기존 B- 트리 개체와 달리 내구성있는 저장소로 지원되지 않습니다. 메모리 최적화 행을 저장하기에 충분한 메모리를 사용할 수없는 시나리오는 문제가 될 수 있습니다. 마이그레이션을 평가할 때 필요한 메모리 크기를 결정하십시오. 추가 메모리 할당이 필요한 여러 버전의 행을 생성 할 수있는 워크로드를 고려하는 것도 중요합니다.

메모리 내 OLTP 설계 고려 사항

각 비즈니스 트랜잭션의 시간을 줄이는 것은 전반적인 성능 측면에서 중요한 목표가 될 수 있습니다. Transact-SQL 코드를 고유하게 컴파일 된 저장 프로 시저로 마이그레이션하고 트랜잭션 실행 대기 시간을 줄이는 것은 전반적인 사용자 환경을 개선하는 데 중요한 요소입니다.

시작하기

1. memory_optimimized_data 옵션을 사용하여 파일 그룹 만들기
2. 그룹에 논리 파일 구현
3. memory_optimization 옵션을 사용하여 테이블 생성

• 참고 : Azure SQL Database에서 메모리 내 기술은 프리미엄 서비스 계층에서만 사용할 수 있습니다. 다음 기사에서 이에 대해 더 자세히 설명하겠습니다.

파일 그룹 만들기

ALTER DATABASE AdventureWorks2014 ADD FILEGROUP InMemAdventureWorks2014FG CONTAINS MEMORY_OPTIMIZED_DATA ;      ALTER DATABASE AdventureWorks2014 ADD FILE (     NAME=InMemAdventureWorks2014File,     FILENAME='f:\PowerSQL\InMemAdventureWorks2014File') TO FILEGROUP InMemAdventureWorks2014FG;

이제 데이터베이스에 대해 MEMORY_OPTIMIZED_DATA 옵션이 활성화되었는지 확인하겠습니다.

SELECT     sfg.name Name,     sdf.name logicalFileName,     sfg.type_desc,     sdf.physical_name FROM sys.filegroups sfg JOIN sys.database_files sdf ON sfg.data_space_id = sdf.data_space_id WHERE sfg.type = 'FX' AND sdf.type = 2

또는

데이터베이스 속성을 찾아보고 파일 그룹 옵션을 선택합니다. 오른쪽 창에서 memory_optimized_data를 볼 수 있습니다.

이 섹션에서는 인라인 메모리 최적화 기술에 필요한 테이블 생성 및 다양한 매개 변수 사용에 대해 다룹니다.

1. MEMORY_OPTIMIZED = ON : 테이블이 메모리에 최적화되었습니다.
2. DURABILITY = SCHEMA_ONLY : 서버 재부팅 시까 지 사용 가능한 스키마 및 데이터입니다. 다시 시작한 후 유일한 스키마는 메모리에 있습니다.
   1. 응용 프로그램에 대한 세션 상태 관리 유지
   2. ETL 시나리오에서 스테이징 테이블로 일반적으로 사용됨
   3. 임시 테이블
3. DURABILITY = SCHEMA_AND_DATA : 메모리에서 항상 사용 가능한 스키마 및 데이터. 데이터는 메모리에 영구적이며 메모리 최적화 테이블을 만들 때 기본 설정입니다.

SCHEMA_ONLY

다음 예제에서는 내구성 옵션 SCHEMA_ONLY를 사용하여 InsertInMemDemo 라는 메모리 내 최적화 된 테이블 을 만듭니다.

CREATE TABLE InsertInMemDemo ( Id INT NOT NULL, data varchar(25) constraint pk_id_1 primary key nonclustered(id)) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

다음으로 InsertDemo 테이블의 더미 데이터를 In-Memory 최적화 테이블에 삽입하고 테이블 간의 레코드 수를 확인합니다. 이것은 단지 예입니다. 여러 가지 방법으로 레코드를 삽입 할 수 있습니다.

이제 SQL 인스턴스를 다시 시작하겠습니다. 이것은 새로 생성 된 메모리 내 테이블의 지속성을 테스트하기위한 것입니다. 아래 이미지에서 새로 생성 된 메모리 내 최적화 된 테이블 지속성은 일시적이며 메모리에 바인딩되어 있음을 알 수 있습니다. 인스턴스가 다시 시작될 때마다 데이터가 메모리에서 플러시됩니다.

SCHEMA_AND_DATA 

다음 예제는 데이터 지속성과 고성능으로 메모리 내 최적화 된 테이블을 생성했습니다.

CREATE TABLE InsertInMemDemo (Id   INT NOT NULL, data VARCHAR(25) CONSTRAINT pk_id_1 PRIMARY KEY NONCLUSTERED (id) ) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

다음 T-SQL 문은 메모리 내 최적화 된 테이블에 대한 기존 개체의 성능 영향을 보여주는 예입니다.

SET NOCOUNT ON; DECLARE @start DATETIME= GETDATE(); DECLARE @id INT= 1; WHILE @id < 10000     BEGIN         INSERT INTO InsertInMemDemo         (id,          data         )         VALUES         (@id,          'SQLShackDemo'         );         SET @id = @id + 1;     END; SELECT DATEDIFF(s, @start, GETDATE()) AS [MemInsert]; GO DECLARE @start DATETIME= GETDATE(); DECLARE @id INT= 1; WHILE @id < 10000     BEGIN         INSERT INTO InsertDemo         (id,          data         )         VALUES         (@id,          'SQLShackDemo'         );         SET @id = @id + 1;     END;      DATEDIFF(s, @start, GETDATE()) AS [Insert]; DROP TABLE InsertInMemDemo; DROP TABLE InsertDemo;

샘플 출력은 기존의 비 메모리 최적화 개체보다 37 배 빠르다는 것을 증명합니다.

메모리 크기 고려 사항

각 델타 파일의 크기는 메모리가 16GB보다 큰 컴퓨터의 경우 약 16MB, 16GB 이하의 컴퓨터의 경우 1MB입니다. SQL Server 2016부터 SQL Server는 저장소 하위 시스템이 충분히 빠르다고 판단되는 경우 대형 검사 점 모드를 사용할 수 있습니다. 대형 검사 점 모드에서 델타 파일의 크기는 128MB입니다.

인 메모리 최적화 테이블-데이터는 데이터 및 델타 파일 쌍에 저장됩니다. 체크 포인트 파일 쌍 (CFP)이라고도합니다. 데이터 파일은 DML 명령을 저장하는 데 사용되며 델타 파일은 삭제 된 행에 사용됩니다. DML 작업 중에 많은 CFP가 생성되므로 복구 시간과 디스크 공간 사용량이 늘어납니다.

다음 예에서 샘플 테이블 dbo. InMemDemoOrderTBL은 T-SQL 코드 조각을 사용하여 더미 값으로 생성되고로드 된 다음 메모리 계산이 이어집니다.

CREATE TABLE dbo.InMemDemoOrderTBL (Order_ID     INT NOT NULL, CustomerName VARCHAR(25), Order_Date   DATETIME DEFAULT GETDATE(), Description NVARCHAR(100), CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED(Order_ID) ) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

다음 T-SQL은 샘플 행을 생성하는 데 사용되는 코드입니다.

SET NOCOUNT ON; DECLARE @start DATETIME= GETDATE(); DECLARE @id INT= 1; WHILE @id < 500 BEGIN INSERT INTO InMemDemoOrderTBL(Order_ID, CustomerName, Description) VALUES(@id, 'SQLShackDemo', 'Table and Row Size Computation example'); SET @id = @id + 1; END;      SELECT * FROM InMemDemoOrderTBL;

문자열 함수 DATALENGTH는 설명 열의 크기를 생성하는 데 사용됩니다.

SELECT AVG(DATALENGTH(Description)) AS TEXTFieldSize FROM InMemDemoOrderTBL

크기는 SUM ([데이터 유형 크기])으로 계산됩니다.

• 비트 : 1
• Tinyint : 1
• Smallint : 2
• 정수 : 4
• 실제 : 4
• Smalldatetime : 4
• 스몰 머니 : 4
• Bigint : 8
• 일시 : 8
• Datetime2 : 8
• 플로트 : 8
• 돈 : 8
• 숫자 (정밀도 <= 18) : 8
• 시간 : 8
• 숫자 (정밀도> 18) : 16
• 고유 식별자 : 16

다음 표는 데이터 및 인덱스의 크기를 추정하는 데 필요한 주요 지표를 정의합니다.

헤더 유형	데이터 구조	바이트
RowHeader	32
TS 시작	8
TS 종료	8
StmtID	4
IdxLinCount	2
IndexPointerArray	8
행 데이터	페이로드 (= 4 (Order_ID) +24 (CustomerName) +8 (Order_Date) +76 (설명의 평균 길이))	106

크기 데이터 및 인덱스 크기를 추정하려면 다음 공식을 사용하십시오.

데이터 크기	[RowHeaderBytes + Index * (인덱스 당 8 바이트) + RowData] * No_Of_Rows
{[(32+ (1 * 8) +113] * 499} / 1024	74.55762
인덱스 크기	[PointerSize (idxLinCount + IndexPointArray) + sum (keyColumnDataTypes)] * No_of_Rows
((2 + 8 + 4) * 499) / 1024	6.822266
테이블 크기	DataSize + IndexSize	81.39355

• 참고 : 계산 된 데이터 및 인덱스 크기는 거의 정확한 값입니다. 행 버전의 경우 추가 75kb가 필요하며 추가 성장을 위해서는 30 % (~ 25kb)를 예약하는 것이 좋습니다. 인 메모리 최적화 테이블을 효율적으로 처리하려면 총 180kb의 메모리가 필요합니다.

SELECT * FROM sys.dm_db_xtp_table_memory_stats WHERE object_id = OBJECT_ID('dbo.InMemDemoOrderTBL');

요약:

이 기사에서는 In-Memory 최적화 테이블과 그 기능에 대한 다양한 개념을 다루었습니다. 메모리 고려 사항 및 요구 사항의 의미를 이해하기 위해 중요한 부분을 포함했습니다. 한계를 아는 것이 좋습니다. 이에 대해서는 다음 기사에서 자세히 설명하겠습니다. 오늘날 세계에서 기술은 매우 중요하며 성능 측면에서 측정됩니다. 메모리 내 OLTP는 SQL 2014에 도입 된 메모리 중심 기능입니다. SQL Server 엔진에 통합되고 최신 하드웨어 용으로 설계된 고성능 메모리 최적화 엔진입니다.

막대한 트랜잭션 오버 헤드가있는 기존 OLTP 시스템은 휘발성 공간에서 트랜잭션 관리를 평가하고 애플리케이션 실행 성능을 크게 향상시킵니다. 이 기능을 올바르게 사용하면 성능이 크게 향상 될 수 있습니다.