Azure SQL Database의 메모리 내 샘플

아티클
03/25/2024

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

이 문서에는 Azure SQL Database의 columnstore 인덱스뿐만 아니라 메모리 내 OLTP의 사용을 보여 주는 두 개의 샘플이 표시됩니다.

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

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

메모리 내 OLTP의 경우 더 간단하지만 시각적으로 뛰어난 성능 데모는 다음을 참조하세요.

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

1. 메모리 내 OLTP 샘플 설치

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

설치 단계

Azure Portal에서 서버에 프리미엄(DTU) 또는 비즈니스 크리티컬(vCore) 데이터베이스를 생성합니다. 원본을 AdventureWorksLT 샘플 데이터베이스로 설정합니다. 자세한 지침은 Azure SQL Database에서 첫 번째 데이터베이스 만들기를 참조하세요.
SQL Server Management Studio (SSMS)를 사용하여 데이터베이스에 연결합니다.
메모리 내 OLTP Transact-SQL 스크립트 를 클립보드에 복사합니다. T-SQL 스크립트는 1단계에서 생성한 AdventureWorksLT 샘플 데이터베이스에서 필요한 메모리 내 개체를 생성합니다.
T-SQL 스크립트를 SSMS에 붙여넣은 다음, 스크립트를 실행합니다. MEMORY_OPTIMIZED = ON 절, CREATE TABLE 문은 매우 중요합니다. 예시:

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 방식은 지원되지 않으며 1 방식은 지원됩니다. 메모리 내 기술은 Azure SQL Database 프리미엄(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), 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를 실행할 수 있습니다.

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

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

메모리 내 OLTP에 대한 샘플 데이터베이스에서 ostress.exe 관련 논의.
메모리 내 OLTP에 대한 샘플 데이터베이스.
ostress.exe 설치에 대한 블로그.

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 명령줄을 실행하려면 다음을 수행합니다.

이전 실행으로 삽입된 모든 데이터를 삭제하도록 SSMS에서 다음 명령을 실행하여 데이터베이스 데이터 콘텐츠를 다시 설정합니다.
```
EXECUTE Demo.usp_DemoReset;
```
이전 ostress.exe 명령줄의 텍스트를 클립보드에 복사합니다.
-S -U -P -d 매개 변수의 <placeholders>를 올바른 실제 값으로 바꿉니다.
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 실행에 대해 다음 단계를 수행합니다.

이전 실행으로 삽입된 모든 데이터를 삭제하도록 SSMS에서 다음 명령을 실행하여 데이터베이스를 다시 설정합니다.
```
EXECUTE Demo.usp_DemoReset;
```
모든 _inmem을 _ondisk로 바꾸도록 ostress.exe 명령줄을 편집합니다.
ostress.exe를 두 번째로 실행하고 기간 결과를 캡처합니다.
많은 양의 테스트 데이터를 확실히 제거하기 위해 데이터베이스를 다시 설정합니다.

예상된 비교 결과

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

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

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

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

columnstore 분석 테스트 준비

Azure Portal을 사용하여 샘플에서 새 AdventureWorksLT 데이터베이스를 만듭니다.
- 정확한 이름을 사용합니다.
- Premium 서비스 계층을 선택합니다.
sql_in-memory_analytics_sample을 클립보드에 복사합니다.
- T-SQL 스크립트는 1단계에서 생성한 AdventureWorksLT 샘플 데이터베이스에서 필요한 메모리 내 개체를 생성합니다.
- 스크립트는 차원 테이블과 두 개의 팩트 테이블을 생성합니다. 팩트 테이블은 각각 350만 개의 행으로 채워집니다.
- 스크립트를 완료하는 데는 15분이 걸릴 수 있습니다.
T-SQL 스크립트를 SSMS에 붙여넣은 다음, 스크립트를 실행합니다. CREATE INDEX 문의 COLUMNSTORE 키워드(keyword)가 중요함: CREATE NONCLUSTERED COLUMNSTORE INDEX ...;
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 v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
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 Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
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 가격 책정 계층이 적용되는 데이터베이스에서 기존 인덱스와 비교했을 때 클러스터형 columnstore 인덱스로 이 쿼리의 성능이 약 9배 향상될 것으로 예상됩니다. P15를 사용하면 columnstore 인덱스로 약 57배의 성능 향상을 예상할 수 있습니다.

Azure SQL Database의 메모리 내 샘플

1. 메모리 내 OLTP 샘플 설치

설치 단계

오류 40536

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

2. 샘플 OLTP 워크로드 실행

RML 유틸리티 및 ostress 설치

ostress.exe 스크립트

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

결과는 기간입니다.

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

예상된 비교 결과

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

columnstore 분석 테스트 준비

핵심 테이블 및 columnstore 인덱스

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

피드백

피드백

추가 리소스

Azure SQL Database의 메모리 내 샘플

1. 메모리 내 OLTP 샘플 설치

설치 단계

오류 40536

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

2. 샘플 OLTP 워크로드 실행

RML 유틸리티 및 ostress 설치

ostress.exe 스크립트

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

결과는 기간입니다.

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

예상된 비교 결과

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

columnstore 분석 테스트 준비

핵심 테이블 및 columnstore 인덱스

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

관련 콘텐츠

피드백

피드백

추가 리소스