메모리 내 OLTP와 쿼리 저장소 사용

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

SQL Server 쿼리 저장소를 사용하면 메모리 내 OLTP를 실행하는 워크로드에 대해 고유하게 컴파일된 코드의 성능을 모니터링할 수 있습니다.

컴파일 및 런타임 통계는 디스크 기반 워크로드와 동일한 방식으로 수집되고 노출됩니다. 메모리 내 OLTP로 마이그레이션하는 경우 SQL Server Management Studio의 쿼리 저장소 보기와 디스크 기반 작업용으로 개발한 사용자 지정 스크립트를 사용하여 진행한 후 마이그레이션할 수 있습니다. 이렇게 하면 쿼리 저장소 기술 학습에 대한 투자가 절약되고, 모든 워크로드의 문제를 해결하는 데 사용할 수 있게 됩니다.
쿼리 저장소 사용에 대한 일반 정보는 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.

메모리 내 OLTP에서 쿼리 저장소를 사용하는 데에는 추가 기능 구성이 필요하지 않습니다. 데이터베이스에서 사용하도록 설정하면 모든 유형의 워크로드에서 작동합니다.
하지만 메모리 내 OLTP와 함께 쿼리 저장소 사용할 때 사용자가 알아야 할 몇 가지 특정한 측면이 있습니다.

• 쿼리 저장소 사용하도록 설정하면 쿼리, 계획 및 컴파일 시간 통계가 기본적으로 수집됩니다. 하지만 런타임 통계 컬렉션은 sys.sp_xtp_control_query_exec_stats(Transact-SQL)를 사용하여 명시적으로 사용하도록 설정한 경우에만 활성화됩니다.

• @new_collection_value를 0으로 설정한 경우 쿼리 저장소는 영향을 받는 프로시저 또는 전체 SQL Server 인스턴스에 대한 런타임 통계 수집을 중지합니다.

• sys.sp_xtp_control_query_exec_stats(Transact-SQL)로 구성된 값은 유지되지 않습니다. SQL Server를 다시 시작한 후에는 통계 컬렉션을 확인 후 다시 구성해야 합니다.

• 일반 쿼리 통계 수집과 마찬가지로, 쿼리 저장소를 사용하여 워크로드 실행을 추적할 경우 성능이 저하됩니다. 고유하게 컴파일된 저장 프로시저의 중요한 하위 집합에 대해서만 통계 수집을 사용하도록 설정하는 것이 좋습니다.

• 쿼리 및 계획은 첫 번째 기본 컴파일에 캡처 및 저장되고 다시 컴파일될 때마다 업데이트됩니다.

• 모든 기본 저장 프로시저가 컴파일된 후 쿼리 저장소를 활성화하거나 해당 컨텐츠를 지운 경우 수동으로 다시 컴파일해야 쿼리 저장소에 캡처할 수 있습니다. sp_query_store_remove_query(Transact-SQL) 또는 sp_query_store_remove_plan(Transact-SQL)을 사용하여 쿼리를 수동으로 제거한 경우에도 이 방식이 적용됩니다. sp_recompile(Transact-SQL)을 사용하여 프로시저를 강제로 다시 컴파일합니다.

• 쿼리 저장소 메모리 내 OLTP의 계획 생성 메커니즘을 활용하여 컴파일 중에 쿼리 실행 계획을 캡처합니다. 저장된 계획은 한 가지 차이점을 제외하면 SET SHOWPLAN_XML ON을 사용하여 얻을 수 있는 계획과 의미상 동일합니다. 쿼리 저장소 계획은 개별 문별로 분할되고 저장됩니다.

• 혼합 워크로드가 있는 데이터베이스에서 쿼리 저장소 실행하는 경우 sys.query_store_plan(Transact-SQL)의 is_natively_compiled 필드를 사용하여 네이티브 코드 컴파일에서 생성되는 쿼리 계획을 빠르게 찾을 수 있습니다.

• 쿼리 저장소 캡처 모드(ALTER TABLE 문의 QUERY_CAPTURE_MODE 매개 변수)는 구성된 값과 관계없이 항상 캡처되므로 기본적으로 컴파일된 모듈에서 쿼리에 영향을 주지 않습니다. 여기에는 QUERY_CAPTURE_MODE = NONE을 설정하는 것이 포함됩니다.

• 쿼리 저장소 캡처한 쿼리 컴파일 기간에는 네이티브 코드가 생성되기 전에 쿼리 최적화에 소요되는 시간만 포함됩니다. 보다 정확하게 말하면, C 코드 생성에 필요한 C 코드 컴파일 및 내부 구조 생성 시간이 포함되지 않습니다.

• sys.query_store_runtime_stats(Transact-SQL) 내의 메모리 부여 메트릭은 고유하게 컴파일된 쿼리에 대해 채워지지 않습니다. 값이 항상 0입니다. 메모리 부여 열은 avg_query_max_used_memory, last_query_max_used_memory, min_query_max_used_memory, max_query_max_used_memory 및 stdev_query_max_used_memory입니다.

메모리 내 OLTP와 쿼리 저장소 설정 및 사용

다음의 간단한 예제에서는 엔드투엔드 사용자 시나리오에서 메모리 내 OLTP와 함께 쿼리 저장소를 사용하는 방법을 보여 줍니다. 이 예제에서는 메모리 내 OLTP에 대해 데이터베이스(MemoryOLTP)가 사용하도록 설정되어 있다고 가정합니다.
메모리 최적화 테이블의 필수 구성 요소에 대한 자세한 내용은 메모리 최적화 테이블 및 고유하게 컴파일된 저장 프로시저 만들기를 참조하세요.

USE MemoryOLTP;
GO

-- Create a simple memory-optimized table
CREATE TABLE dbo.Ord
   (OrdNo INTEGER not null PRIMARY KEY NONCLUSTERED,
    OrdDate DATETIME not null,
    CustCode NVARCHAR(5) not null)
WITH (MEMORY_OPTIMIZED=ON);
GO

-- Enable Query Store before native module compilation
ALTER DATABASE MemoryOLTP SET QUERY_STORE = ON;
GO

-- Create natively compiled stored procedure
CREATE PROCEDURE dbo.OrderInsert(@OrdNo integer, @CustCode nvarchar(5))
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
    BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English')

    DECLARE @OrdDate DATETIME = GETDATE();
    INSERT INTO dbo.Ord (OrdNo, CustCode, OrdDate)
        VALUES (@OrdNo, @CustCode, @OrdDate);
END;
GO

-- Enable runtime stats collection for queries from dbo.OrderInsert stored procedure
DECLARE @db_id INT = DB_ID()
DECLARE @proc_id INT = OBJECT_ID('dbo.OrderInsert');
DECLARE @collection_enabled BIT;

EXEC [sys].[sp_xtp_control_query_exec_stats] @new_collection_value = 1,
    @database_id = @db_id, @xtp_object_id = @proc_id;

-- Check the state of the collection flag
EXEC sp_xtp_control_query_exec_stats @database_id = @db_id,
    @xtp_object_id = @proc_id,
    @old_collection_value= @collection_enabled output;
SELECT @collection_enabled AS 'collection status';

-- Execute natively compiled workload
EXEC dbo.OrderInsert 1, 'A';
EXEC dbo.OrderInsert 2, 'B';
EXEC dbo.OrderInsert 3, 'C';
EXEC dbo.OrderInsert 4, 'D';
EXEC dbo.OrderInsert 5, 'E';

-- Check Query Store Data
-- Compile time data
SELECT q.query_id, plan_id, object_id, query_hash, p.query_plan,
    p.initial_compile_start_time, p.last_compile_start_time,
    p.last_execution_time, p.avg_compile_duration,
    p.last_force_failure_reason, p.force_failure_count
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
    ON q.query_id = p.plan_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');

-- Get runtime stats
-- Check count_executions field to verify that runtime statistics
-- have been collected by the Query Store
SELECT q.query_id, p.plan_id, object_id, rsi.start_time, rsi.end_time,
    p.last_force_failure_reason, p.force_failure_count, rs.*
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
    ON q.query_id = p.plan_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');

참고 항목

• 쿼리 저장소를 사용한 성능 모니터링
• 메모리 액세스에 최적화된 테이블 및 고유하게 컴파일된 저장 프로시저 만들기
• 쿼리 저장소에 대한 모범 사례
• 쿼리 저장소 저장 프로시저(Transact-SQL)
• 쿼리 저장소 카탈로그 뷰(Transact-SQL)