Azure SQL Database의 메모리 내 샘플

적용 대상: Azure SQL 데이터베이스

Azure SQL 데이터베이스에서 메모리 내 기술을 사용하면 애플리케이션의 성능을 향상시키고, 데이터베이스의 비용을 줄일 수 있습니다. Azure SQL Database에서 메모리 내 기술을 사용하여 다양한 워크로드에서 성능을 개선할 수 있습니다.

이 문서의 두 가지 샘플은 Azure SQL 데이터베이스에서 메모리 내 OLTP와 columnstore 인덱스의 사용을 보여 줍니다.

자세한 내용은 다음을 참조하세요.

• 메모리 내 OLTP 개요 및 사용 시나리오(시작하기 위한 고객 사례 연구 및 정보에 대한 참조 포함)
• 메모리 내 OLTP에 대한 설명서
• Columnstore 인덱스 가이드
• HTAP(하이브리드 트랜잭션/분석 처리) 즉, 실시간 운영 분석

메모리 내 OLTP의 소개 데모는 다음을 참조하세요.

• 릴리스: in-memory-oltp-demo-v1.0
• 소스 코드: in-memory-oltp-demo-source-code

1. 메모리 내 OLTP 샘플 설치

Azure Portal에서 몇 단계만 거치면 AdventureWorksLT 샘플 데이터베이스를 만들 수 있습니다. 그런 다음, 메모리 내 OLTP 개체를 AdventureWorksLT 데이터베이스에 추가하고 성능 혜택을 보여 주는 이 섹션의 단계를 사용합니다.

설치 단계

1. Azure Portal에서 논리 서버에 프리미엄(DTU) 또는 중요 비즈니스용(vCore) 데이터베이스를 생성합니다. 원본을 AdventureWorksLT 샘플 데이터베이스로 설정합니다. 자세한 지침은 Azure SQL Database에서 첫 번째 데이터베이스 만들기를 참조하세요.

2. SQL Server Management Studio (SSMS)를 사용하여 데이터베이스에 연결합니다.

3. 메모리 내 OLTP Transact-SQL 스크립트 를 클립보드에 복사합니다. T-SQL 스크립트는 1단계에서 생성한 AdventureWorksLT 샘플 데이터베이스에서 필요한 메모리 내 개체를 생성합니다.

4. T-SQL 스크립트를 SSMS에 붙여넣은 다음, 스크립트를 실행합니다. CREATE TABLE 문의 MEMORY_OPTIMIZED = ON 절은 매우 중요합니다. 예시:

   CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
       [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
       ...
   ) WITH (MEMORY_OPTIMIZED = ON);
   

오류 40536

T-SQL 스크립트를 실행할 때 오류 40536이 발생하면 다음 T-SQL 스크립트를 실행하여 데이터베이스가 메모리 내 개체를 지원하는지 확인하세요:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

0의 결과는 메모리 내 OLTP는 지원되지 않음을 의미하며 1은 지원됨을 의미합니다. 메모리 내 OLTP는 Azure SQL Database Premium(DTU) 및 중요 비즈니스용(vCore) 계층에서 제공됩니다.

생성된 메모리 최적화된 항목에 대한 정보

테이블: 샘플은 다음과 같은 메모리 최적화 테이블을 포함합니다.

• SalesLT.Product_inmem
• SalesLT.SalesOrderHeader_inmem
• SalesLT.SalesOrderDetail_inmem
• Demo.DemoSalesOrderHeaderSeed
• Demo.DemoSalesOrderDetailSeed

SSMS의 개체 탐색기를 통해 메모리 최적화 테이블만 표시하도록 필터링할 수 있습니다. 테이블을 마우스 오른쪽 단추로 클릭한 다음, >필터>필터 설정>메모리 최적화됨으로 이동합니다. 값은 1입니다.

또는 다음과 같은 카탈로그 뷰를 쿼리할 수 있습니다.

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

네이티브 컴파일된 저장 프로시저: 카탈로그 뷰 쿼리를 통해 SalesLT.usp_InsertSalesOrder_inmem을 검사할 수 있습니다.

SELECT uses_native_compilation, OBJECT_NAME(object_id) AS module_name, definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. 샘플 OLTP 워크로드 실행

다음 두 저장 프로시저의 유일한 차이점은 첫 번째 프로시저가 메모리 최적화 테이블을 사용하는 반면 두 번째 프로시저는 일반 디스크 내 테이블을 사용한다는 점입니다.

• SalesLT.usp_InsertSalesOrder_inmem
• SalesLT.usp_InsertSalesOrder_ondisk

이 섹션에서는 ostress.exe 유틸리티를 사용하여 두 저장 프로시저를 실행하는 방법을 살펴봅니다. 두 스트레스 실행을 완료하는 데 걸리는 시간을 비교할 수 있습니다.

RML 유틸리티 및 ostress 설치

바람직하게는 Azure VM(가상 머신)에서 ostress.exe를 실행해야 합니다. AdventureWorksLT 데이터베이스가 있는 동일한 Azure 지역에 Azure VM을 생성합니다. Azure SQL 데이터베이스에 연결할 수 있는 경우 대신 로컬 컴퓨터에서 ostress.exe를 실행할 수도 있습니다. 그러나 컴퓨터와 Azure의 데이터베이스 간의 네트워크 대기 시간은 메모리 내 OLTP의 성능 이점을 줄일 수 있습니다.

VM 또는 선택한 호스트에서 RML(Replay Markup Language) 유틸리티를 설치합니다. 유틸리티는 ostress.exe를 포함합니다.

자세한 내용은 다음을 참조하세요.

• 메모리 내 OLTP에 대한 샘플 데이터베이스에서 ostress.exe 논의.
• 메모리 내 OLTP에 대한 샘플 데이터베이스.

ostress.exe 스크립트

이 섹션에서는 ostress.exe 명령줄에 포함된 T-SQL 스크립트를 표시합니다. 스크립트는 이전에 설치한 T-SQL 스크립트에서 생성한 항목을 사용합니다.

ostress.exe를 실행할 때 다음 두 가지 전략을 모두 사용하여 워크로드에 스트레스를 부과하도록 설계된 매개 변수 값을 전달하는 것이 좋습니다.

• -n100을 사용하여 많은 수의 동시 연결을 실행합니다.
• -r500을 사용하여 각 연결을 수백 번 반복합니다.

그러나 훨씬 더 작은 값(예 :-n10 및 -r50)으로 시작하고 모든 기능이 작동하는지 확인할 수 있습니다.

다음 스크립트는 다음과 같은 메모리 최적화 테이블에 다섯 줄 항목의 샘플 판매 주문을 삽입합니다.

• SalesLT.SalesOrderHeader_inmem
• SalesLT.SalesOrderDetail_inmem

DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
BEGIN;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
END

ostress.exe에 대한 이전 T-SQL 스크립트의 _ondisk 버전을 만들려면 _inmem 하위 문자열의 두 항목을 _ondisk로 대체합니다. 이러한 대체는 테이블의 이름 및 저장 프로시저에 영향을 줍니다.

먼저 _inmem 스트레스 워크로드를 실행합니다.

RML Cmd 프롬프트 창을 사용하여 ostress.exe를 실행할 수 있습니다. 명령줄 매개 변수는 ostress에게 다음을 명령합니다.

• 동시에 100개의 연결을 실행합니다(-n100).
• 각 연결에서 T-SQL 스크립트를 50번(-r50) 실행합니다.

ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

이전 ostress.exe 명령줄을 실행하려면 다음을 수행합니다.

1. 이전 실행으로 삽입된 모든 데이터를 삭제하도록 SSMS에서 다음 명령을 실행하여 데이터베이스 데이터 콘텐츠를 다시 설정합니다.

   EXECUTE Demo.usp_DemoReset;
   

2. 이전 ostress.exe 명령줄의 텍스트를 클립보드에 복사합니다.

3. -S -U -P -d 매개 변수의 <placeholders>를 올바른 값으로 바꿉니다.

4. RML Cmd 창에서 편집된 명령줄을 실행합니다.

결과는 기간입니다.

ostress.exe가 완료되면 RML Cmd 창에서 실행 기간을 최종 출력 줄로 씁니다. 예를 들어 더 짧은 테스트 실행은 약 1.5분 동안 지속됩니다.

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

다시 설정하고 _ondisk를 편집한 다음, 다시 실행합니다.

_inmem 실행의 결과가 생성되면 _ondisk 실행에 대해 다음 단계를 수행합니다.

1. 이전 실행으로 삽입된 모든 데이터를 삭제하도록 SSMS에서 다음 명령을 실행하여 데이터베이스를 다시 설정합니다.

   EXECUTE Demo.usp_DemoReset;
   

2. 모든 _inmem을 _ondisk로 바꾸도록 ostress.exe 명령줄을 편집합니다.

3. ostress.exe를 두 번째로 실행하고 기간 결과를 캡처합니다.

4. 다시 데이터베이스를 다시 설정합니다.

예상된 비교 결과

데이터베이스와 동일한 Azure 지역의 Azure VM에서 ostress.exe를 실행할 때 이렇게 간단한 워크로드에서 메모리 내 OLTP 테스트의 성능이 9배까지 향상되었습니다.

3. 메모리 내 분석 샘플 설치

이 섹션에서는 columnstore 인덱스와 전형적인 B-트리 인덱스를 사용하는 경우의 IO 및 통계 결과를 비교합니다.

OLTP 워크로드에 대한 실시간 분석의 경우 비클러스터형 columnstore 인덱스를 사용하는 것이 가장 좋습니다. 자세한 내용은 Columnstore Indexes Described를 참조하세요.

columnstore 분석 테스트 준비

1. Azure Portal을 사용하여 샘플에서 새 AdventureWorksLT 데이터베이스를 만듭니다. columnstore 인덱스를 지원하는 모든 서비스 목표를 사용합니다.

2. sql_in-memory_analytics_sample을 클립보드에 복사합니다.

   • T-SQL 스크립트는 1단계에서 생성한 AdventureWorksLT 샘플 데이터베이스에서 필요한 개체를 생성합니다.
   • 스크립트는 차원 테이블과 두 개의 팩트 테이블을 생성합니다. 팩트 테이블은 각각 350만 개의 행으로 채워집니다.
   • 더 작은 서비스 목표에서 스크립트를 완료하는 데 15분 이상이 걸릴 수 있습니다.

3. T-SQL 스크립트를 SSMS에 붙여넣은 다음, 스크립트를 실행합니다. CREATE INDEX 문의 COLUMNSTORE 키워드(keyword)가 중요함: CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

4. AdventureWorksLT를 최신 호환성 수준인 SQL Server 2022(160)로 설정: ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

핵심 테이블 및 columnstore 인덱스

• dbo.FactResellerSalesXL_CCI는 클러스터형 columnstore 인덱스가 있는 테이블이며, 여기에는 데이터 수준의 고급 압축이 포함됩니다.

• dbo.FactResellerSalesXL_PageCompressed는 동등한 일반 클러스터형 인덱스가 있는 테이블이며 페이지 수준에서만 압축됩니다.

4. columnstore 인덱스를 비교하는 키 쿼리

성능 개선을 확인하기 위해 실행할 수 있는 몇 가지 T-SQL 쿼리 유형이 있습니다. T-SQL 스크립트의 2단계에서 이 쿼리 쌍에 주의하세요. 한 줄에서만 다릅니다.

• FROM FactResellerSalesXL_PageCompressed AS a
• FROM FactResellerSalesXL_CCI AS a

클러스터형 columnstore 인덱스는 FactResellerSalesXL_CCI 테이블에 있습니다.

다음 T-SQL 스크립트는 각 쿼리에 대해 SET STATISTICS IO 및 SET STATISTICS TIME을 사용하여 논리적 I/O 활동 및 시간 통계를 출력합니다.

/*********************************************************************
Step 2 -- Overview
-- Page compressed BTree table vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO

-- Execute a typical query that joins the fact table with dimension tables.
-- Note this query will run on the page compressed table. Note down the time.
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

P2 서비스 목표를 사용하는 데이터베이스에서 기존 rowstore 인덱스와 비교했을 때 클러스터형 columnstore 인덱스로 이 쿼리의 성능이 약 9배 향상될 것으로 예상됩니다. P15 서비스 목표를 사용하면 columnstore 인덱스로 약 57배의 성능 향상을 예상할 수 있습니다.

관련 콘텐츠

• 빠른 시작 1: 더 빠른 T-SQL 성능을 위한 메모리 내 OLTP 기술
• 메모리 내 OLTP를 사용하여 애플리케이션 성능 개선
• 메모리 내 OLTP 스토리지 모니터링
• 블로그: Azure SQL 데이터베이스의 메모리 내 OLTP
• 메모리 내 OLTP
• columnstore 인덱스
• columnstore 인덱스를 사용하는 실시간 운영 분석
• 기술 문서: 메모리 내 OLTP – SQL Server 2014의 일반적인 워크로드 패턴 및 마이그레이션 고려 사항