데모: 메모리 내 OLTP의 성능 향상

아티클
08/21/2023

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

이 항목의 코드 샘플에서는 메모리 최적화 테이블의 빠른 성능을 보여 줍니다. 메모리 최적화 테이블의 데이터가 기존의 해석된 Transact-SQL에서 액세스되는 경우 성능 향상이 분명합니다. 메모리 최적화 테이블의 데이터가 고유하게 컴파일된 저장 프로시저(NCSProc)에서 액세스하면 이 성능이 훨씬 향상됩니다.

메모리 내 OLTP의 잠재적 성능 향상에 대한 보다 포괄적인 데모를 보려면 메모리 내 OLTP 성능 데모 v1.0을 참조 하세요.

현재 문서의 코드 예제는 단일 스레드이며 메모리 내 OLTP의 동시성 이점을 활용하지 않습니다. 동시성을 사용하는 작업에서는 더 큰 성능 향상이 가능합니다. 코드 예제에서는 성능 향상의 한 측면, 즉 INSERT에 대한 데이터 액세스 효율성만 보여 줍니다.

메모리 최적화 테이블로 제공되는 성능 향상은 메모리 최적화 테이블의 데이터가 NCSProc에서 액세스될 때 완벽하게 실현됩니다.

코드 예

다음 하위 섹션에서는 각 단계를 설명합니다.

1a단계: SQL Server를 사용하는 경우 필수 구성 요소

이 첫 번째 하위 섹션의 단계는 SQL Server에서 실행 중인 경우에만 적용되며 Azure SQL Database에서 실행 중인 경우에는 적용되지 않습니다. 다음을 수행하십시오:

SSMS.exe(SQL Server Management Studio)를 사용하여 SQL Server에 연결합니다. 또는 SSMS.exe와 유사한 도구는 괜찮습니다.
C:\data\라는 디렉터리를 수동으로 만듭니다. 샘플 Transact-SQL 코드는 디렉터리가 존재할 것으로 예상합니다.
짧은 T-SQL을 실행하여 데이터베이스 및 메모리 최적화 파일 그룹을 생성하세요.

go  
CREATE DATABASE imoltp;    --  Transact-SQL  
go  
  
ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]  
    CONTAINS MEMORY_OPTIMIZED_DATA;  
  
ALTER DATABASE imoltp ADD FILE  
    (name = [imoltp_dir], filename= 'c:\data\imoltp_dir')  
    TO FILEGROUP imoltp_mod;  
go  
  
USE imoltp;  
go

1b단계: Azure SQL Database를 사용하는 경우 필수 구성 요소

이 하위 섹션은 Azure SQL Database를 사용하는 경우에만 적용됩니다. 다음을 수행하십시오:

코드 예제에 사용할 기존 테스트 데이터베이스를 결정합니다.
새 테스트 데이터베이스를 만들려면 Azure 포털 을 사용하여 이름이 imoltp인 데이터베이스를 생성합니다.

이를 위해 Azure Portal 사용 지침을 확인하려면 Azure SQL Database 시작을 참조하십시오.

2단계: 메모리 액세스에 최적화된 테이블과 NCSProc 만들기

이 단계에서는 메모리 최적화 테이블과 고유하게 컴파일된 저장 프로시저(NCSProc)를 만듭니다. 다음을 수행하십시오:

SSMS.exe를 사용하여 새 데이터베이스에 연결합니다.
데이터베이스에서 다음 T-SQL을 실행합니다.

go  
DROP PROCEDURE IF EXISTS ncsp;  
DROP TABLE IF EXISTS sql;  
DROP TABLE IF EXISTS hash_i;  
DROP TABLE IF EXISTS hash_c;  
go  
  
CREATE TABLE [dbo].[sql] (  
  c1 INT NOT NULL PRIMARY KEY,  
  c2 NCHAR(48) NOT NULL  
);  
go  
  
CREATE TABLE [dbo].[hash_i] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE TABLE [dbo].[hash_c] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE PROCEDURE ncsp  
    @rowcount INT,  
    @c NCHAR(48)  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS   
  BEGIN ATOMIC   
  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @i INT = 1;  
  WHILE @i <= @rowcount  
  BEGIN;  
    INSERT INTO [dbo].[hash_c] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
END;  
go

3단계: 코드 실행

이제 메모리 최적화 테이블의 성능을 보여 주는 쿼리를 실행할 수 있습니다. 다음을 수행하십시오:

SSMS.exe를 사용하여 데이터베이스에서 다음 T-SQL을 실행합니다.

첫 번째 실행에서 생성되는 모든 속도 또는 기타 성능 데이터를 무시합니다. 첫 번째 실행은 메모리의 초기 할당과 같은 여러 일회성 작업만 수행되도록 합니다.
다시 SSMS.exe를 사용하여 데이터베이스에서 다음 T-SQL을 다시 실행합니다.

go  
SET STATISTICS TIME OFF;  
SET NOCOUNT ON;  
  
-- Inserts, one at a time.  
  
DECLARE @starttime DATETIME2 = sysdatetime();  
DECLARE @timems INT;  
DECLARE @i INT = 1;  
DECLARE @rowcount INT = 100000;  
DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678';  
  
-- Harddrive-based table and interpreted Transact-SQL.  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
  BEGIN;  
    INSERT INTO [dbo].[sql] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'A: Disk-based table and interpreted Transact-SQL: '  
    + cast(@timems AS VARCHAR(10)) + ' ms';  
  
-- Interop Hash.  
  
SET @i = 1;  
SET @starttime = sysdatetime();  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
    BEGIN;  
      INSERT INTO [dbo].[hash_i] VALUES (@i, @c);  
      SET @i += 1;  
    END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'B: memory-optimized table with hash index and interpreted Transact-SQL: '  
    + cast(@timems as VARCHAR(10)) + ' ms';  
  
-- Compiled Hash.  
  
SET @starttime = sysdatetime();  
  
EXECUTE ncsp @rowcount, @c;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'C: memory-optimized table with hash index and native SP:'  
    + cast(@timems as varchar(10)) + ' ms';  
go  
  
DELETE sql;  
DELETE hash_i;  
DELETE hash_c;  
go

다음은 두 번째 테스트 실행에서 생성된 출력 시간 통계입니다.

10453 ms , A: Disk-based table and interpreted Transact-SQL.  
5626 ms , B: memory-optimized table with hash index and interpreted Transact-SQL.  
3937 ms , C: memory-optimized table with hash index and native SP.

참고 항목

메모리 내 OLTP(메모리 내 최적화)