쿼리 저장소 관리에 대한 모범 사례

아티클
07/12/2024

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

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

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

참고 항목

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

Azure SQL 데이터베이스의 쿼리 저장소 기본값

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

구성	설명	기본값	Comment(설명)
MAX_STORAGE_SIZE_MB	쿼리 저장소가 사용자 데이터베이스 내부에서 사용하는 데이터 공간에 대한 제한을 지정합니다.	100 - SQL Server 2019(15.x) 이전 1000 - SQL Server 2019(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 데이터베이스는 쿼리 저장소 기본 워크로드 또는 신뢰할 수 있는 작업에 부정적인 영향을 주지 않는 한 고객이 설정한 구성 값을 변경하지 않습니다.

참고 항목

쿼리 저장소는 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) 이상 버전에서 기본 캡처 모드입니다.
없음	런타임에 모니터링하려는 쿼리 집합을 이미 캡처했으며 다른 쿼리 때문에 발생할 수 있는 방해 요소를 제거하고 싶은 경우에 사용합니다. 없음은 환경 테스트 및 벤치마킹에 적합합니다. 없음은 애플리케이션 워크로드를 모니터링하도록 구성된 쿼리 저장소 구성을 제공하는 소프트웨어 공급업체에도 적합합니다. 없음을 사용하면 중요한 새 쿼리를 추적하고 최적화하는 기회를 놓칠 수 있으므로 주의해서 사용해야 합니다. 필요한 특정 시나리오가 없으면 없음을 사용하지 마십시오.
사용자 지정	SQL Server 2019(15.x)는 `ALTER DATABASE ... SET QUERY_STORE` 명령 아래에 사용자 지정 캡처 모드를 도입했습니다. 자동은 권장되는 기본값이지만, 쿼리 저장소에 발생할 수 있는 오버헤드에 대한 우려가 있는 경우 데이터베이스 관리자는 사용자 지정 캡처 정책을 활용하여 쿼리 저장소 캡처 동작을 추가로 튜닝할 수 있습니다. 자세한 내용 및 권장 사항은 이 문서의 뒷부분에 있는 사용자 지정 캡처 정책을 참조하세요. 이 구문에 대한 자세한 내용은 ALTER DATABASE SET 옵션을 참조하세요.

참고 항목

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

쿼리 저장소에 가장 관련성이 높은 데이터 보관

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

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

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

사용자 지정 캡처 정책

사용자 지정 쿼리 저장소 캡처 모드를 사용하도록 설정한 경우, 특정 서버의 데이터 수집을 세부적으로 튜닝하기 위한 추가 쿼리 저장소 구성이 새 쿼리 저장소 캡처 정책 설정 아래에 제공됩니다.

새로운 사용자 지정 설정은 내부 캡처 정책 시간 임계값 내에서 수행되는 작업을 정의합니다. 이 시간 경계 내에서 구성 가능한 조건이 평가되고, 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 옵션 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);

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

사용하지 않을 기록 데이터는 유지하지 않는 것이 좋습니다. 이렇게 하면 읽기 전용 상태의 변경 사항이 줄어듭니다. 쿼리 저장소 데이터의 크기와 문제를 감지하고 완화하는 시간을 예측하기가 더 용이합니다. Management Studio 또는 다음 스크립트를 사용하여 시간 기반 정리 정책을 구성합니다.

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 데이터베이스의 쿼리 저장소 기본값

최적의 쿼리 캡처 모드 설정

쿼리 저장소에 가장 관련성이 높은 데이터 보관

사용자 지정 캡처 정책

사용자 지정 캡처 정책 예제

쿼리 저장소 최대 크기

데이터 플러시 간격(분)

쿼리 저장소 기본값 수정

쿼리 저장소 현재 설정 보기

예제

쿼리 저장소 유지 관리

쿼리 저장소 상태

쿼리 저장소 옵션 가져오기

쿼리 저장소 간격 설정

쿼리 저장소 공간 사용량

쿼리 저장소 옵션 설정

공간 정리

임시 쿼리 삭제

피드백

추가 리소스

다음을 통해 공유

쿼리 저장소 관리에 대한 모범 사례

Azure SQL 데이터베이스의 쿼리 저장소 기본값

최적의 쿼리 캡처 모드 설정

쿼리 저장소에 가장 관련성이 높은 데이터 보관

사용자 지정 캡처 정책

사용자 지정 캡처 정책 예제

쿼리 저장소 최대 크기

데이터 플러시 간격(분)

쿼리 저장소 기본값 수정

쿼리 저장소 현재 설정 보기

예제

쿼리 저장소 유지 관리

쿼리 저장소 상태

쿼리 저장소 옵션 가져오기

쿼리 저장소 간격 설정

쿼리 저장소 공간 사용량

쿼리 저장소 옵션 설정

공간 정리

임시 쿼리 삭제

관련 콘텐츠

피드백

추가 리소스