Linux의 SQL Server 성능 기능 연습

  • 아티클

적용 대상:SQL Server - Linux

SQL Server를 처음 사용하는 Linux 사용자인 경우 다음 작업에서 몇 가지 성능 기능을 안내합니다. 이 보안 작업은 Linux에 고유하거나 특정하지 않으며 추가로 조사할 영역에 대한 아이디어를 제공하는 데 도움이 됩니다. 각 예제에서는 해당 영역의 심층 설명서에 대한 링크가 제공됩니다.

참고

다음 예에서는 AdventureWorks2022 샘플 데이터베이스를 사용합니다. 이 샘플 데이터베이스를 구하여 설치하는 방법에 대한 지침은 Windows에서 Linux로 SQL Server 데이터베이스 복원을 참조하세요.

columnstore 인덱스 만들기

Columnstore 인덱스는 columnstore라는 열 데이터 형식의 대규모 데이터 저장 영역을 저장 및 쿼리하는 기술입니다.

  1. 다음 Transact-SQL 명령을 실행하여 SalesOrderDetail 테이블에 columnstore 인덱스를 추가합니다.

    CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
   ON Sales.SalesOrderDetail
   (UnitPrice, OrderQty, ProductID);
GO

  2. columnstore 인덱스를 사용하여 테이블을 검색하는 다음 쿼리를 실행합니다.

    SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
   SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM Sales.SalesOrderDetail
   GROUP BY ProductID
   ORDER BY ProductID;

  3. columnstore 인덱스의 object_id를 조회하고 SalesOrderDetail 테이블의 사용 통계에 표시되는지 확인하여 columnstore 인덱스를 사용했는지 검토합니다.

    SELECT * FROM sys.indexes WHERE name = 'IX_SalesOrderDetail_ColumnStore'
GO

SELECT *
FROM sys.dm_db_index_usage_stats
   WHERE database_id = DB_ID('AdventureWorks2022')
   AND object_id = OBJECT_ID('AdventureWorks2022.Sales.SalesOrderDetail');

메모리 내 OLTP 사용

SQL Server는 애플리케이션 시스템의 성능을 크게 향상시킬 수 있는 메모리 내 OLTP 기능을 제공합니다. 이 섹션에서는 메모리에 저장된 메모리 최적화 테이블과 컴파일하거나 해석할 필요 없이 테이블에 액세스할 수 있는 고유하게 컴파일된 저장 프로시저를 만드는 단계를 안내합니다.

메모리 내 OLTP에 대한 데이터베이스 구성

  1. 메모리 내 OLTP를 사용하려면 최소 130의 호환성 수준으로 데이터베이스를 설정하는 것이 좋습니다. 다음 쿼리를 사용하여 현재 호환성 수준을 AdventureWorks2022검사.

    USE AdventureWorks2022;
GO
SELECT d.compatibility_level
FROM sys.databases as d
    WHERE d.name = DB_NAME();
GO

    필요한 경우 이 수준을 130으로 업데이트합니다.

    ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 130;
GO

  2. 트랜잭션이 디스크 기반 테이블 및 메모리 최적화 테이블과 관련된 경우, 이러한 트랜잭션에서 메모리 최적화 트랜잭션 부분은 SNAPSHOT이라고 하는 트랜잭션 격리 수준에서 작동해야 합니다. 컨테이너 간 트랜잭션에서 메모리 최적화 테이블에 대해 이 수준을 안정적으로 적용하려면 다음을 실행합니다.

    ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

  3. 메모리 최적화 테이블을 만들려면 먼저, 데이터 파일에 대해 메모리 최적화 파일 그룹과 컨테이너를 만들어야 합니다.

    ALTER DATABASE AdventureWorks2022
   ADD FILEGROUP AdventureWorks_mod
      CONTAINS memory_optimized_data;
GO
ALTER DATABASE AdventureWorks2022
   ADD FILE (NAME='AdventureWorks_mod',
   FILENAME='/var/opt/mssql/data/AdventureWorks_mod')
      TO FILEGROUP AdventureWorks_mod;
GO

메모리 최적화 테이블 만들기

메모리 최적화 테이블의 기본 저장소는 주 메모리이므로 디스크 기반 테이블과 달리 디스크에서 메모리 버퍼로 데이터를 읽을 필요가 없습니다. 메모리 최적화 테이블을 만들려면 MEMORY_OPTIMIZED = ON 절을 사용합니다.

  1. 다음 쿼리를 실행하여 메모리 최적화 테이블 dbo.ShoppingCart를 만듭니다. 기본적으로 데이터는 내구성을 위해 디스크에 유지됩니다(DURABILITY는 스키마만 유지하도록 설정할 수도 있음).

    CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON);
GO

  2. 테이블에 레코드를 삽입합니다.

    INSERT dbo.ShoppingCart VALUES (8798, SYSDATETIME(), NULL);
INSERT dbo.ShoppingCart VALUES (23, SYSDATETIME(), 45.4);
INSERT dbo.ShoppingCart VALUES (80, SYSDATETIME(), NULL);
INSERT dbo.ShoppingCart VALUES (342, SYSDATETIME(), 65.4);

고유하게 컴파일된 저장 프로시저

SQL Server는 메모리 최적화 테이블에 액세스하는 고유하게 컴파일된 저장 프로시저를 지원합니다. T-SQL 문은 기계어 코드로 컴파일되고 네이티브 DLL로 저장되므로 기존 T-SQL보다 더 빠르게 데이터에 액세스하고 보다 효율적으로 쿼리를 실행할 수 있습니다. NATIVE_COMPILATION으로 표시된 저장 프로시저는 고유하게 컴파일됩니다.

  1. 다음 스크립트를 실행하여 ShoppingCart 테이블에 많은 수의 레코드를 삽입하는 고유하게 컴파일된 저장 프로시저를 만듭니다.

    CREATE PROCEDURE dbo.usp_InsertSampleCarts @InsertCount INT
    WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

DECLARE @i INT = 0

WHILE @i < @InsertCount
   BEGIN
       INSERT INTO dbo.ShoppingCart VALUES (1, SYSDATETIME(), NULL)

       SET @i += 1
   END
END

  2. 100만개의 행을 삽입하려면 다음을 실행합니다.

    EXEC usp_InsertSampleCarts 1000000;

  3. 다음과 같이 행이 삽입되었는지 확인합니다.

    SELECT COUNT(*) FROM dbo.ShoppingCart;

쿼리 저장소 사용

쿼리 저장소는 쿼리, 실행 계획 및 런타임 통계에 대한 자세한 성능 정보를 수집합니다.

SQL Server 2022(16.x) 이전에는 쿼리 저장소가 기본적으로 사용하도록 설정되지 않으며 ALTER DATABASE를 통해 사용하도록 설정할 수 있습니다.

ALTER DATABASE AdventureWorks2022 SET QUERY_STORE = ON;

다음 쿼리를 실행하여 쿼리 저장소의 쿼리 및 계획에 대한 정보를 반환합니다.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
   JOIN sys.query_store_query AS Qry
      ON Pl.query_id = Qry.query_id
   JOIN sys.query_store_query_text AS Txt
      ON Qry.query_text_id = Txt.query_text_id;

쿼리 동적 관리 뷰

동적 관리 뷰는 서버 인스턴스 상태 모니터링, 문제 진단 및 성능 튜닝에 사용할 수 있는 서버 상태 정보를 반환합니다.

dm_os_wait stats 동적 관리 뷰를 쿼리하려면 다음을 실행합니다.

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;

참고 항목

관련 콘텐츠