쿼리 저장소 관리 모범 사례

아티클
01/08/2024

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

이 문서에서는 SQL Server 쿼리 저장소 관리 및 주변 기능에 대해 간략하게 설명합니다.

쿼리 저장소를 구성하고 관리하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.

참고 항목

SQL Server 2022(16.x)에서는 이제 성능 기록을 더 잘 추적하고, 쿼리 계획 관련 문제를 해결하고, 새 쿼리 프로세서 기능을 사용하도록 설정하기 위해 새로 만든 모든 SQL Server 데이터베이스에 대해 쿼리 저장소 기본적으로 사용하도록 설정됩니다.

Azure SQL Database의 쿼리 저장소 기본값

이 섹션에서는 쿼리 저장소 및 종속 기능의 안정적인 작동을 보장하도록 설계된 Azure SQL Database의 최적의 구성 기본값에 대해 설명합니다. 기본 구성은 연속 데이터 수집에 최적화되어 있으며, 이는 OFF/READ_ONLY 상태에서 소요되는 최소 시간입니다. 사용 가능한 모든 쿼리 저장소 옵션에 대한 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

구성	설명	기본값	Comment(설명)
MAX_STORAGE_SIZE_MB	쿼리 저장소 고객 데이터베이스 내에서 사용할 수 있는 데이터 공간의 제한을 지정합니다.	SQL Server 2019 이전 100개(15.x) SQL Server 2019부터 1000(15.x)	새 데이터베이스에 적용
INTERVAL_LENGTH_MINUTES	쿼리 계획에 대해 수집된 런타임 통계가 집계되고 지속되는 기간의 규모를 정의합니다. 모든 활성 쿼리 계획에는 이 구성으로 정의된 기간 동안 최대 하나의 행이 있습니다.	60	새 데이터베이스에 적용
STALE_QUERY_THRESHOLD_DAYS	지속되는 런타임 통계 및 비활성 쿼리의 보존 기간을 제어하는 시간 기반 정리 정책	30	새 데이터베이스 및 이전 기본값을 포함하는 데이터베이스에 적용(367)
SIZE_BASED_CLEANUP_MODE	쿼리 저장소 데이터 크기가 제한에 접근할 때 자동 데이터 클린 발생 여부를 지정합니다.	AUTO	모든 데이터베이스에 적용
QUERY_CAPTURE_MODE	모든 쿼리 또는 쿼리 하위 집합만 추적되는지 여부를 지정합니다.	AUTO	모든 데이터베이스에 적용
DATA_FLUSH_INTERVAL_SECONDS	디스크로 플러시하기 전에 캡처된 런타임 통계가 메모리에 유지되는 최대 기간을 지정합니다.	900	새 데이터베이스에 적용

Important

이러한 기본값은 Azure SQL Database에서 쿼리 저장소 활성화의 최종 단계에서 자동으로 적용됩니다. 사용하도록 설정한 후 Azure SQL Database는 쿼리 저장소 기본 워크로드 또는 신뢰할 수 있는 작업에 부정적인 영향을 주지 않는 한 고객이 설정한 구성 값을 변경하지 않습니다.

참고 항목

쿼리 저장소는 Azure SQL Database 단일 데이터베이스 및 탄력적 풀에서 사용하지 않도록 설정할 수 없습니다. ALTER DATABASE [database] SET QUERY_STORE = OFF를 실행하면 'QUERY_STORE=OFF' is not supported in this version of SQL Server. 경고가 반환됩니다.

사용자 지정 설정을 유지하려면 쿼리 저장소 옵션과 함께 ALTER DATABASE를 사용하여 구성을 이전 상태로 되돌리기. 최적의 구성 매개 변수를 선택하는 방법을 알아보려면 쿼리 저장소 모범 사례를 확인하세요.

최적의 쿼리 저장소 캡처 모드 설정

쿼리 저장소에 가장 관련성이 높은 데이터를 보관합니다. 다음 표에서는 각 쿼리 저장소 캡처 모드의 일반적인 시나리오를 설명합니다.

쿼리 저장소 캡처 모드	시나리오
모두	모든 쿼리의 셰이프와 실행 빈도 및 기타 통계를 기준으로 워크로드를 철저히 분석합니다. 워크로드에서 새 쿼리를 식별합니다. 임시 쿼리를 사용하여 사용자 또는 자동 매개 변수화 기회를 식별하는지 검색합니다. 참고: SQL Server 2016(13.x) 및 SQL Server 2017(14.x)의 기본 캡처 모드입니다.
자동	관련성이 높고 실행 가능한 쿼리에 주목합니다. 예를 들어 정기적으로 실행되거나 리소스 사용량이 많은 쿼리가 있습니다. 참고: SQL Server 2019(15.x) 이상 버전에서는 기본 캡처 모드입니다.
없음	런타임에 모니터링하려는 쿼리 집합을 이미 캡처했으며 다른 쿼리에서 발생할 수 있는 방해를 제거하려고 합니다. 테스트 및 벤치마킹 환경에 적합한 것은 없습니다. 애플리케이션 워크로드를 모니터링하도록 구성된 쿼리 저장소 구성을 배송하는 소프트웨어 공급업체에도 적합한 것은 없습니다. 중요한 새 쿼리를 추적하고 최적화할 수 있는 기회를 놓칠 수 있으므로 주의해서 사용할 수 없습니다. 필요한 특정 시나리오가 없으면 None을 사용하지 마십시오.
사용자 지정	SQL Server 2019(15.x)는 명령 아래에 사용자 지정 캡처 모드를 도입했습니다 `ALTER DATABASE ... SET QUERY_STORE` . 자동은 기본값이며 권장되지만 오버헤드 쿼리 저장소 발생할 수 있는 문제가 여전히 있는 경우 데이터베이스 관리자는 사용자 지정 캡처 정책을 사용하여 쿼리 저장소 캡처 동작을 추가로 조정할 수 있습니다. 자세한 내용 및 권장 사항은 이 문서의 뒷부분에 있는 사용자 지정 캡처 정책을 참조하세요. 이 구문에 대한 자세한 내용은 ALTER DATABASE SET 옵션을 참조 하세요.

참고 항목

커서, 저장 프로시저 내의 쿼리 및 고유하게 컴파일된 쿼리는 쿼리 저장소 캡처 모드가 모두, 자동 또는 사용자 지정으로 설정된 경우 항상 캡처됩니다. 고유하게 컴파일된 쿼리를 캡처하려면 sys.sp_xtp_control_query_exec_stats 사용하여 쿼리별 통계 수집을 사용하도록 설정합니다.

가장 관련성이 큰 데이터를 쿼리 저장소

관련 데이터만 포함하도록 쿼리 저장소 구성하여 지속적으로 실행되고 일반 워크로드에 미치는 영향을 최소화하면서 훌륭한 문제 해결 환경을 제공합니다.

다음 표에서는 모범 사례를 제공합니다.

모범 사례	설정
보관된 기록 데이터를 제한합니다.	자동 클린 활성화하도록 시간 기반 정책을 구성합니다.
다시 실행하지 않는 쿼리를 필터링합니다.	쿼리 저장소 캡처 모드를 자동으로 구성합니다.
최대 크기에 도달하면 관련성이 낮은 쿼리를 삭제합니다.	크기 기반 클린up 정책을 활성화합니다.

사용자 지정 캡처 정책

CUSTOM 쿼리 저장소 캡처 모드를 사용하도록 설정하면 새 쿼리 저장소 캡처 정책 설정에서 추가 쿼리 저장소 구성을 사용하여 특정 서버의 데이터 수집을 미세 조정할 수 있습니다.

새 사용자 지정 설정은 내부 캡처 정책 시간 임계값 중에 발생하는 동작을 정의합니다. 이 시간 경계 내에서 구성 가능한 조건이 평가되고, true인 조건이 있으면 쿼리 저장소에서 쿼리를 캡처할 수 있습니다.

쿼리 저장소 캡처 모드는 쿼리 저장소 대한 쿼리 캡처 정책을 지정합니다.

모두: 모든 쿼리를 캡처합니다. 이 옵션은 SQL Server 2016(13.x) 및 SQL Server 2017(14.x)의 기본값입니다.
자동: 중요하지 않은 컴파일 및 실행 기간이 있는 드문 쿼리 및 쿼리는 무시됩니다. 실행 수, 컴파일 및 런타임 기간에 대한 임계값은 내부적으로 결정됩니다. SQL Server 2019(15.x)부터 기본 옵션입니다.
없음: 쿼리 저장소 새 쿼리 캡처를 중지합니다.
사용자 지정: 추가 제어 및 데이터 수집 정책을 미세 조정하는 기능을 허용합니다. 새 사용자 지정 설정은 내부 캡처 정책 시간 임계값 중에 발생하는 동작을 정의합니다. 이 시간 경계 내에서 구성 가능한 조건이 평가되고, true인 조건이 있으면 쿼리 저장소에서 쿼리를 캡처할 수 있습니다.

환경에 적합한 사용자 지정 캡처 정책 튜닝은 다음과 같은 경우에 고려해야 합니다.

데이터베이스가 매우 큽합니다.
데이터베이스에는 많은 수의 고유한 임시 쿼리가 있습니다.
데이터베이스에는 특정 크기 또는 증가 제한이 있습니다.

SSMS
T-SQL

Management Studio에서 현재 설정을 보려면 다음을 수행합니다.

SQL Server Management Studio 개체 탐색기 데이터베이스를 마우스 오른쪽 단추로 클릭합니다.
속성을 선택합니다.
쿼리 저장소 선택합니다. 쿼리 저장소 페이지에서 작업 모드(요청됨)가 읽기 쓰기인지 확인합니다.
쿼리 저장소 캡처 모드를 사용자 지정으로변경합니다.
이제 쿼리 저장소 캡처 정책 아래의 4개의 캡처 정책 필드가 사용하도록 설정되고 구성할 수 있습니다.

사용자 지정 캡처 정책 예제

다음 예제에서는 QUERY_CAPTURE_MODE AUTO로 설정하고 사용자 지정 캡처 모드를 설정합니다. 다음 각각은 SQL Server 2022(16.x)에서 사용자 지정 캡처 정책을 기본값으로 설정합니다. 캡처된 쿼리 수를 줄이고, 따라서 쿼리 저장소 디스크의 공간을 줄이려면 이러한 값을 조정하는 것이 좋습니다. 이러한 값을 조금씩 변경하는 것이 좋습니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

다음 샘플 쿼리는 기본 설정을 EXECUTION_COUNT 재정의하는 사용자 지정 캡처 정책을 사용하도록 기존 쿼리 저장소 변경합니다TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

쿼리 저장소 최대 크기

쿼리 저장소 기본 최대 크기 값은 SQL Server 2019(15.x)부터 1000MB입니다. 이전 버전에서는 기본값이 100MB였습니다. 쿼리 저장소 최대 크기 제한을 늘리는 것은 고유한 쿼리 계획이 많은 사용 중인 데이터베이스에 적합합니다. 캡처 정책 조정(이전 섹션 참조)은 쿼리 저장소 디스크의 크기를 제한하고 쿼리 저장소 READ_ONLY 모드로 전환되지 않도록 하는 것이 더 중요합니다. 쿼리 저장소에서 쿼리, 실행 계획 및 통계를 수집하는 동안 이 한도에 도달할 때까지 데이터베이스에서 해당 크기가 증가합니다. 이 경우 쿼리 저장소 자동으로 작업 모드를 READ_ONLY 변경하고 새 데이터 수집을 중지합니다. 즉, 성능 분석이 더 이상 정확하지 않습니다.

SQL Server 및 Azure SQL Managed Instance에서는 MAX_STORAGE_SIZE_MB 제한이 엄격하게 적용되지 않습니다.
Azure SQL Database에서 허용되는 MAX_STORAGE_SIZE_MB 최대값은 10,240MB입니다.

쿼리 저장소가 디스크에 데이터를 쓰는 경우에만 스토리지 크기가 확인됩니다. 이 간격은 DATA_FLUSH_INTERVAL_SECONDS 옵션 또는 Management Studio 쿼리 저장소 대화 상자 옵션 데이터 플러시 간격에서 설정됩니다.

간격 기본값은 900초, 즉 15분입니다.
쿼리 저장소 스토리지 크기 검사 사이의 제한을 위반 MAX_STORAGE_SIZE_MB 하면 읽기 전용 모드로 전환됩니다.
SIZE_BASED_CLEANUP_MODE가 사용하도록 설정된 경우 MAX_STORAGE_SIZE_MB 한도를 적용하는 정리 메커니즘도 트리거됩니다.
- 충분한 공간이 지워지면 쿼리 저장소 모드가 자동으로 READ_WRITE 모드로 다시 전환됩니다.

자세한 내용은 ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB 참조하세요.

데이터 플러시 간격(분)

데이터 플러시 간격은 수집된 런타임 통계가 디스크에 유지되기 전의 빈도를 정의합니다. SQL Server Management Studio에서 값은 분 단위이지만 Transact-SQL에서는 초 단위로 표시됩니다. 기본값은 15분(900초)입니다.

데이터 플러시 간격을 늘리면 전체 쿼리 저장소 스토리지 I/O 영향을 줄일 수 있지만 스토리지 I/O 워크로드가 더 뾰족해지고 디스크 사용률이 더 적지만 더 무거워집니다. 워크로드에서 서로 다른 쿼리와 계획을 대량으로 생성하지 않거나 데이터베이스가 종료되기까지 데이터를 더 오래 유지해도 되는 경우 값을 늘리는 것이 좋습니다.
데이터 플러시 간격을 줄이면 종료, 전원 손실 또는 장애 조치(failover) 시 손실될 쿼리 저장소 데이터의 양이 줄어듭니다. 디스크에 더 자주 쓰지만 더 적은 데이터로 쿼리 저장소 스토리지 I/O 영향을 부드럽게 할 수도 있습니다.

참고 항목

추적 플래그 7745를 사용하면 장애 조치(failover) 또는 종료 명령의 경우 쿼리 저장소 데이터가 디스크에 기록되지 않습니다. 자세한 내용은 중요 업무용 서버에서 쿼리 저장소 사용을 참조하세요.

쿼리 저장소 기본값 수정

작업 및 성능 문제 해결 요구 사항을 기반으로 쿼리 저장소를 구성합니다. 시작할 때는 기본 매개 변수를 사용하는 것이 좋지만, 시간이 흐름에 따라 쿼리 저장소가 동작하는 방식을 모니터링하여 구성을 적절하게 조정해야 합니다.

현재 설정 쿼리 저장소 보기

SSMS(SQL Server Management Studio) 또는 T-SQL에서 현재 쿼리 저장소 설정을 봅니다.

SSMS
T-SQL

Management Studio에서 현재 설정을 보려면 다음을 수행합니다.

SQL Server Management Studio 개체 탐색기 데이터베이스를 마우스 오른쪽 단추로 클릭합니다.
속성을 선택합니다.
쿼리 저장소 선택합니다.

다음 T-SQL 스크립트를 실행하여 쿼리 저장소 크기에 대한 최신 정보를 가져옵니다.

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

다음 스크립트는 최대 크기(MB)에 대한 새 값을 설정합니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

SQL Server Management Studio 또는 Transact-SQL을 사용하여 데이터 플러시 간격에 다른 값을 설정할 수 있습니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

통계 수집 간격: 수집된 런타임 통계의 세분성 수준을 정의합니다(분 단위로 표시). 기본값은 60분입니다. 문제를 감지하고 완화하는 데 더 세분화되거나 더 적은 시간이 필요한 경우 더 낮은 값을 사용하는 것이 좋습니다. 값은 쿼리 저장소 데이터의 크기에 직접적인 영향을 줍니다. SQL Server Management Studio 또는 Transact-SQL을 사용하여 통계 수집 간격에 다른 값을 설정할 수 있습니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

부실 쿼리 임계값(일): 지속형 런타임 통계 및 비활성 쿼리의 보존 기간을 제어하는 시간 기반 클린up 정책(일)입니다. 기본적으로 쿼리 저장소 30일 동안 데이터를 유지하도록 구성되며, 이는 시나리오에 불필요하게 길 수 있습니다.

사용하지 않으려는 기록 데이터를 유지하지 마세요. 이렇게 하면 읽기 전용 상태 대한 변경 내용이 줄어듭니다. 쿼리 저장소 데이터의 크기와 문제를 감지하고 완화하는 시간이 더 예측 가능합니다. Management Studio 또는 다음 스크립트를 사용하여 시간 기반 클린up 정책을 구성합니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

크기 기반 정리 모드: 쿼리 저장소 데이터 크기가 제한에 접근할 때 자동 데이터 클린 수행되는지 여부를 지정합니다. 크기 기반 클린 활성화하여 쿼리 저장소 항상 읽기/쓰기 모드에서 실행되고 최신 데이터를 수집하도록 합니다. 많은 워크로드에서는 쿼리 저장소 클린업이 제한에 따라 데이터 크기를 일관되게 기본 보장하지 않습니다. 자동 데이터 정리가 지연되고 (일시적으로) 읽기 전용 모드로 전환될 수 있습니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

쿼리 저장소 캡처 모드: 쿼리 저장소 대한 쿼리 캡처 정책을 지정합니다.

모두: 모든 쿼리를 캡처합니다. 이 옵션은 SQL Server 2016(13.x) 및 SQL Server 2017(14.x)의 기본값입니다.
자동: 중요하지 않은 컴파일 및 실행 기간이 있는 드문 쿼리 및 쿼리는 무시됩니다. 실행 수, 컴파일 및 런타임 기간에 대한 임계값은 내부적으로 결정됩니다. SQL Server 2019(15.x)부터 기본 옵션입니다.
없음: 쿼리 저장소 새 쿼리 캡처를 중지합니다.
사용자 지정: 추가 제어 및 데이터 수집 정책을 미세 조정하는 기능을 허용합니다. 새 사용자 지정 설정은 내부 캡처 정책 시간 임계값 중에 발생하는 동작을 정의합니다. 이 시간 경계 내에서 구성 가능한 조건이 평가되고, true인 조건이 있으면 쿼리 저장소에서 쿼리를 캡처할 수 있습니다.

Important

다음 스크립트는 QUERY_CAPTURE_MODE를 AUTO로 설정합니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

예제

다음 예제에서는 QUERY_CAPTURE_MODE AUTO로 설정하고 SQL Server 2016(13.x)에서 다른 권장 옵션을 설정합니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

다음 예제에서는 QUERY_CAPTURE_MODE AUTO로 설정하고 대기 통계를 포함하도록 SQL Server 2017(14.x)의 다른 권장 옵션을 설정합니다.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

다음 예제에서는 사용자 지정 캡처 정책을 새 기본 AUTO 캡처 모드 대신 SQL Server 2019(15.x) 기본값으로 설정합니다. 사용자 지정 캡처 정책 옵션 및 기본값에 대한 자세한 내용은 query_capture_policy_option_list> 참조<하세요.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

쿼리 저장소 기본 테넌트

이 섹션에서는 쿼리 저장소 기능 자체를 관리하는 데 대한 지침을 제공합니다.

쿼리 저장소 상태

쿼리 저장소는 데이터를 사용자 데이터베이스 내에 저장하며 이 때문에 쿼리 저장소에는 크기 제한(MAX_STORAGE_SIZE_MB로 구성)이 있습니다. 쿼리 저장소의 데이터가 이 제한에 도달하면 쿼리 저장소는 자동으로 상태를 읽기/쓰기에서 읽기 전용으로 변경하고 새 데이터 수집을 중지합니다.

쿼리 저장소가 현재 활성 상태인지, 및 현재 런타임 통계를 수집하는지의 여부를 정하려면 sys.database_query_store_options를 쿼리합니다.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

쿼리 저장소 상태는 actual_state 열에 의해 결정됩니다. 원하는 상태 다른 경우 열에서 readonly_reason 자세한 정보를 제공할 수 있습니다. 쿼리 저장소의 크기가 할당량을 초과하면 기능이 read_only 모드로 전환되고 이유가 제공됩니다. 이유에 대한 자세한 내용은 sys.database_query_store_options 참조하세요.

쿼리 저장소 옵션 가져오기

쿼리 저장소 상태 대한 자세한 정보를 확인하려면 사용자 데이터베이스에서 다음을 실행합니다.

SELECT * FROM sys.database_query_store_options;

쿼리 저장소 간격 설정

쿼리 런타임 통계 집계 간격을 재정의할 수 있습니다(기본값은 60분). 간격에 대한 새 값은 보기를 통해 sys.database_query_store_options 노출됩니다.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

정수 값은 INTERVAL_LENGTH_MINUTES에 허용되지 않습니다. 1, 5, 10, 15, 30, 60 또는 1440분 간격 중 하나를 사용합니다.

참고 항목

Azure Synapse Analytics의 경우 이 섹션에서 설명한 대로 쿼리 저장소 구성 옵션 사용자 지정이 지원되지 않습니다.

쿼리 저장소 공간 사용량

현재 쿼리 저장소 크기 및 제한을 검사 사용자 데이터베이스에서 다음 문을 실행합니다.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

쿼리 저장소 스토리지가 가득 차면 다음 문을 사용하여 스토리지를 확장합니다.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

쿼리 저장소 옵션 설정

단일 ALTER DATABASE 문을 사용하여 여러 쿼리 저장소 옵션을 한 번에 설정할 수 있습니다.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

구성 옵션의 전체 목록은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

공간 정리

데이터베이스 생성 중 쿼리 저장소 내부 테이블이 PRIMARY 파일 그룹에 만들어지며 해당 구성은 나중에 변경할 수 없습니다. 공간이 부족한 경우 다음 문을 사용하여 이전 쿼리 저장소 데이터를 지울 수 있습니다.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

또는 쿼리 최적화 및 계획 분석에는 관련성이 낮지만 공간을 많이 차지하므로 임시 쿼리 데이터만 지우는 것이 좋습니다.

Azure Synapse Analytics에서는 쿼리 저장소를 삭제할 수 없습니다. 데이터는 지난 7일 동안 자동으로 보존됩니다.

임시 쿼리 삭제

이렇게 하면 쿼리 저장소 공간이 부족하지 않고 추적해야 하는 쿼리가 제거되지 않도록 쿼리 저장소 임시 및 내부 쿼리가 제거됩니다.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

더 이상 중요하지 않은 데이터를 지우는 다른 논리를 사용하여 프로시저를 직접 정의할 수 있습니다.

이전 예제에서는 불필요한 데이터를 제거하기 위해 확장 저장 프로시저를 사용합니다 sp_query_store_remove_query . 다음도 가능합니다.

지정된 계획에 대한 런타임 통계를 지우는 데 사용합니다 sp_query_store_reset_exec_stats .
단일 계획을 제거하는 데 사용합니다 sp_query_store_remove_plan .

쿼리 저장소 관리 모범 사례

Azure SQL Database의 쿼리 저장소 기본값

최적의 쿼리 저장소 캡처 모드 설정

가장 관련성이 큰 데이터를 쿼리 저장소

사용자 지정 캡처 정책

사용자 지정 캡처 정책 예제

쿼리 저장소 최대 크기

데이터 플러시 간격(분)

쿼리 저장소 기본값 수정

현재 설정 쿼리 저장소 보기

예제

쿼리 저장소 기본 테넌트

쿼리 저장소 상태

쿼리 저장소 옵션 가져오기

쿼리 저장소 간격 설정

쿼리 저장소 공간 사용량

쿼리 저장소 옵션 설정

공간 정리

임시 쿼리 삭제

피드백

피드백

추가 리소스

쿼리 저장소 관리 모범 사례

Azure SQL Database의 쿼리 저장소 기본값

최적의 쿼리 저장소 캡처 모드 설정

가장 관련성이 큰 데이터를 쿼리 저장소

사용자 지정 캡처 정책

사용자 지정 캡처 정책 예제

쿼리 저장소 최대 크기

데이터 플러시 간격(분)

쿼리 저장소 기본값 수정

현재 설정 쿼리 저장소 보기

예제

쿼리 저장소 기본 테넌트

쿼리 저장소 상태

쿼리 저장소 옵션 가져오기

쿼리 저장소 간격 설정

쿼리 저장소 공간 사용량

쿼리 저장소 옵션 설정

공간 정리

임시 쿼리 삭제

관련 콘텐츠

피드백

피드백

추가 리소스