쿼리 저장소를 사용하여 성능 모니터링

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics(전용 SQL 풀에만 해당)

쿼리 저장소 기능은 SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics에 대한 쿼리 계획 선택 및 성능에 대한 인사이트를 제공합니다. 쿼리 저장소는 쿼리 계획 변경으로 인해 발생하는 성능 차이를 신속하게 찾을 수 있도록 하여 성능 문제 해결을 간소화합니다. 쿼리 저장소는 쿼리, 계획 및 런타임 통계의 기록을 자동으로 캡처하고 사용자 검토를 위해 보관합니다. 데이터를 기간별로 구분하여 데이터베이스 사용 패턴을 파악하고 서버에서 쿼리 계획 변경이 발생한 시기를 이해할 수 있게 해줍니다. 쿼리 저장소는 ALTER DATABASE SET 옵션을 사용하여 구성할 수 있습니다.

  • Azure SQL Database에서 쿼리 저장소 운영하는 방법에 대한 자세한 내용은 Azure SQL Database에서 쿼리 저장소 운영을 참조하세요.
  • 실행 가능한 정보를 검색하고 쿼리 저장소 사용하여 성능을 조정하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 조정을 참조하세요.
  • 애플리케이션 코드를 변경하지 않고 쿼리 계획을 셰이핑하는 방법에 대한 자세한 내용은 쿼리 저장소 힌트를 참조하세요.

Important

SQL Server 2016(13.x)에서 just-in-Time 워크로드 인사이트를 위해 쿼리 저장소 사용하는 경우 가능한 한 빨리 KB 4340759 성능 확장성 수정 사항을 설치할 계획입니다.

쿼리 저장소 사용

  • 쿼리 저장소는 새 Azure SQL Database 및 Azure SQL Managed Instance 데이터베이스에 대해 기본적으로 사용하도록 설정됩니다.
  • 쿼리 저장소 SQL Server 2016(13.x), SQL Server 2017(14.x), SQL Server 2019(15.x)에 대해 기본적으로 사용하도록 설정되지 않습니다. SQL Server 2022(16.x)부터 새 데이터베이스에 대한 모드에서 기본적으로 READ_WRITE 사용하도록 설정됩니다. 기능을 사용하여 성능 기록을 더 잘 추적하고, 쿼리 계획 관련 문제를 해결하고, SQL Server 2022(16.x)에서 새로운 기능을 사용하도록 설정하려면 모든 데이터베이스에서 쿼리 저장소 사용하도록 설정하는 것이 좋습니다.
  • 쿼리 저장소는 새 Azure Synapse Analytics 데이터베이스에서 기본적으로 사용되지 않습니다.

SQL Server Management Studio에서 쿼리 저장소 페이지 사용

  1. 개체 탐색기 데이터베이스를 마우스 오른쪽 단추로 클릭한 다음 속성을 선택합니다.

    참고 항목

    Management Studio 버전 16 이상이 필요합니다.

  2. 데이터베이스 속성 대화 상자에서 쿼리 저장소 페이지를 선택합니다.

  3. 작업 모드(요청됨) 상자에서 읽기 쓰기를 선택합니다.

Transact-SQL 문 사용

ALTER DATABASE 문을 사용하여 지정된 데이터베이스에 대한 쿼리 저장소를 사용하도록 설정합니다. 예시:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Azure Synapse Analytics에서 추가 옵션 없이 쿼리 저장소를 사용하도록 설정합니다. 예를 들면 다음과 같습니다.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

쿼리 저장소와 관련된 구문 옵션에 대한 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

참고 항목

master 또는 tempdb 데이터베이스에 대해 쿼리 저장소를 사용하도록 설정할 수 없습니다.

Important

쿼리 저장소를 사용하도록 설정하고 사용자의 워크로드에 맞게 조정하는 방법에 대한 자세한 내용은 쿼리 저장소에 대한 모범 사례를 참조하세요.

쿼리 저장소 정보

SQL Server의 특정 쿼리에 대한 실행 계획은 일반적으로 통계 변경, 스키마 변경, 인덱스 만들기/삭제 등과 같은 다양한 이유로 인해 시간이 지남에 따라 진화합니다. 캐시된 쿼리 계획이 저장되는 프로시저 캐시는 최신 실행 계획만 저장합니다. 또한 메모리 압력으로 인해 계획 캐시에서 계획이 제거됩니다. 따라서 실행 계획 변경으로 인한 쿼리 성능 저하는 간단한 문제가 아니며 해결하는 데 시간이 걸릴 수 있습니다.

쿼리 저장소 쿼리당 여러 실행 계획을 유지하므로 쿼리 프로세서가 쿼리에 특정 실행 계획을 사용하도록 지시하는 정책을 적용할 수 있습니다. 이를 계획 강제라고 합니다. 쿼리 저장소 계획 강제 적용은 USE PLAN 쿼리 힌트와 유사한 메커니즘을 사용하여 제공되지만 사용자 애플리케이션을 변경할 필요는 없습니다. 계획 강제 적용은 계획 변경으로 인한 쿼리 성능 저하를 짧은 시간 내에 해결할 수 있습니다.

참고 항목

쿼리 저장소 SELECT, INSERT, UPDATE, DELETE, MERGE 및 BULK INSERT와 같은 DML 문에 대한 계획을 수집합니다.

기본적으로 쿼리 저장소 CREATE INDEX 등과 같은 DDL 문에 대한 계획을 수집하지 않습니다. 쿼리 저장소 기본 DML 문에 대한 계획을 수집하여 누적 리소스 사용량을 캡처합니다. 예를 들어 쿼리 저장소 새 인덱스를 채우기 위해 내부적으로 실행된 SELECT 및 INSERT 문을 표시할 수 있습니다.

쿼리 저장소 기본적으로 고유하게 컴파일된 저장 프로시저에 대한 데이터를 수집하지 않습니다. Sp_xtp_control_query_exec_stats를 사용하여 고유하게 컴파일된 저장 프로시저에 대한 데이터 수집을 사용하도록 설정합니다.

대기 통계는 데이터베이스 엔진 성능 문제를 해결하는 데 도움이 되는 또 다른 정보 원본입니다. 오랜 시간 동안 대기 통계는 인스턴스 수준에서만 사용할 수 있었기 때문에 특정 쿼리에 대한 대기를 역추적하기가 어려웠습니다. SQL Server 2017(14.x) 및 Azure SQL Database부터 쿼리 저장소 대기 통계를 추적하는 차원이 포함되어 있습니다. 다음 예제에서는 쿼리 저장소 대기 통계를 수집할 수 있도록 합니다.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

쿼리 저장소 기능을 사용하는 일반적인 시나리오는 다음과 같습니다.

  • 이전 쿼리 계획을 적용하여 계획 성능 저하를 빠르게 찾고 해결합니다. 실행 계획 변경으로 인해 최근 성능이 저하된 쿼리를 수정합니다.
  • 지정된 기간 동안 쿼리가 실행된 횟수를 확인하여 DBA가 성능 리소스 문제를 해결하는 데 도움을 줍니다.
  • 지난 x시간의 상위 n개 쿼리(실행 시간, 메모리 사용량 등)를 식별합니다.
  • 지정된 쿼리에 대한 쿼리 계획의 기록을 감사합니다.
  • 특정 데이터베이스에 대한 리소스(CPU, I/O 및 메모리) 사용 패턴을 분석합니다.
  • 리소스에서 대기 중인 상위 n개 쿼리를 식별합니다.
  • 특정 쿼리 또는 계획에 대한 대기 특성을 이해합니다.

쿼리 저장소 다음 세 개의 저장소를 포함합니다.

  • 실행 계획 정보를 유지하기 위한 계획 저장소입니다.
  • 런타임 통계 저장소 - 실행 통계 정보 유지
  • 대기 통계 정보를 유지하기 위한 대기 통계 저장소입니다.

쿼리 저장소에서 쿼리에 대해 저장할 수 있는 고유한 계획의 수는 max_plans_per_query 구성 옵션으로 제한됩니다. 성능을 향상시키기 위해 정보는 비동기적으로 저장소에 기록됩니다. 공간 사용량을 최소화하기 위해 런타임 통계 저장소의 런타임 실행 통계는 고정된 기간 동안 집계됩니다. 이러한 저장소의 정보는 쿼리 저장소 카탈로그 뷰를 쿼리하여 볼 수 있습니다.

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

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

보조 복제본에 대한 쿼리 저장소

적용 대상: SQL Server(SQL Server 2022(16.x)부터)

보조 복제본에 대한 쿼리 저장소 기능을 사용하면 주 복제본에 사용할 수 있는 보조 복제본 워크로드에서 동일한 쿼리 저장소 기능을 사용할 수 있습니다. 보조 복제본에 대한 쿼리 저장소를 사용하도록 설정하면 복제본은 일반적으로 쿼리 저장소에 저장되는 쿼리 실행 정보를 주 복제본으로 다시 보냅니다. 그런 다음, 주 복제본은 자체 쿼리 저장소 내에서 디스크에 데이터를 유지합니다. 기본적으로 주 복제본과 모든 보조 복제본 간에 하나의 쿼리 저장소가 공유됩니다. 쿼리 저장소는 주 복제본에 존재하며 모든 복제본에 대한 데이터를 함께 저장합니다.

보조 복제본(replica) 대한 쿼리 저장소 대한 자세한 내용은 Always On 가용성 그룹 보조 복제본(replica) 대한 쿼리 저장소 참조하세요.

회귀된 쿼리 기능 사용

쿼리 저장소 사용하도록 설정한 후 개체 탐색기 창의 데이터베이스 부분을 새로 고쳐 쿼리 저장소 섹션을 추가합니다.

Screenshot of the Query Store reporting tree in SSMS Object Explorer.

참고 항목

Azure Synapse Analytics의 경우 개체 탐색기 창의 데이터베이스 부분에 있는 시스템 뷰에서 쿼리 저장소 보기를 사용할 수 있습니다.

회귀된 쿼리를 선택하여 SQL Server Management Studio에서 회귀된 쿼리 창을 엽니다. 재발된 쿼리 창에는 쿼리 저장소의 쿼리 및 계획이 표시됩니다. 맨 위에 있는 드롭다운 목록 상자를 사용하여 기간(ms) (기본값), CPU 시간(ms), 논리적 읽기(KB), 논리적 쓰기(KB), 물리적 읽기(KB), CLR 시간(ms), DOP, 메모리 사용량(KB), 행 수, 사용된 로그 메모리(KB), 임시 DB 메모리 사용(KB) 및 대기 시간(ms) 등 다양한 조건에 따라 쿼리를 필터링합니다.

계획을 선택하면 그래픽 쿼리 계획이 표시됩니다. 단추를 사용하여 원본 쿼리를 보고, 쿼리 계획을 강제로 적용 및 적용 해제하고, 그리드 형식과 차트 형식 간에 전환하고, 선택한 계획을 비교하고(두 개 이상 선택한 경우), 디스플레이를 새로 고칠 수 있습니다.

Screenshot of the SQL Server Regressed Queries report in SSMS Object Explorer.

계획을 강제로 적용하려면 쿼리 및 계획을 선택한 다음 계획 강제 적용을 선택합니다. 쿼리 계획 기능에 의해 저장되고 쿼리 계획 캐시에 유지되는 계획만 강제 적용할 수 있습니다.

대기 중인 쿼리 찾기

SQL Server 2017(14.x) 및 Azure SQL Database부터 시간이 지남에 따라 쿼리당 대기 통계를 쿼리 저장소 사용할 수 있습니다.

쿼리 저장소 대기 유형은 대기 범주결합됩니다. 대기 범주를 대기 형식에 매핑하는 작업은 sys.query_store_wait_stats(Transact-SQL)에서 제공됩니다.

쿼리 대기 통계를 선택하여 SQL Server Management Studio v18 이상에서 쿼리 대기 통계 창을 엽니다. 쿼리 대기 통계 창에는 쿼리 저장소 상위 대기 범주가 포함된 가로 막대형 차트가 표시됩니다. 맨 위에 있는 드롭다운 목록을 사용하여 대기 시간에 대한 집계 조건(평균, 최대, 최소, std 개발 및 합계 (기본값)을 선택합니다.

Screenshot of the SQL Server Query Wait Statistics report in SSMS Object Explorer.

막대에서 선택하여 대기 범주를 선택하고 선택한 대기 범주의 세부 정보 보기가 표시됩니다. 이 새 가로 막대형 차트에는 해당 대기 범주에 기여한 쿼리가 포함되어 있습니다.

Screenshot of the SQL Server Query Wait Statistics detail view in SSMS Object Explorer.

맨 위에 있는 드롭다운 목록 상자를 사용하여 평균, 최대, 최소, std 개발 및 합계 (기본값)와 같이 선택한 대기 범주에 대한 다양한 대기 시간 기준에 따라 쿼리를 필터링합니다. 계획을 선택하면 그래픽 쿼리 계획이 표시됩니다. 단추를 사용하여 원본 쿼리를 보고, 쿼리 계획을 강제로 적용 및 강제 해제하고, 디스플레이를 새로 고칠 수 있습니다.

대기 범주 는 서로 다른 대기 유형을 기본적으로 비슷한 버킷으로 결합합니다. 다른 대기 범주는 문제를 해결하기 위해 다른 후속 분석이 필요하지만 동일한 범주의 대기 유형은 매우 유사한 문제 해결 환경으로 이어지며, 영향을 받는 쿼리를 대기 위에 제공하는 것은 대부분의 조사를 성공적으로 완료하는 데 누락된 부분이 될 것입니다.

다음은 쿼리 저장소 대기 범주를 도입하기 전과 후에 워크로드에 대한 더 많은 인사이트를 얻을 수 있는 몇 가지 예입니다.

이전 경험 새로운 환경 작업
데이터베이스당 높은 RESOURCE_SEMAPHORE 대기 특정 쿼리에 대한 쿼리 저장소 높은 메모리 대기 쿼리 저장소 상위 메모리 사용 쿼리를 찾습니다. 이러한 쿼리는 영향을 받는 쿼리의 추가 진행을 지연시킬 수 있습니다. 이러한 쿼리 또는 영향을 받는 쿼리에 대해 MAX_GRANT_PERCENT 쿼리 힌트를 사용하는 것이 좋습니다.
데이터베이스당 높은 LCK_M_X 대기 특정 쿼리에 대한 쿼리 저장소 높은 잠금 대기 영향을 받는 쿼리에 대한 쿼리 텍스트를 확인하고 대상 엔터티를 식별합니다. 자주 실행되거나 기간이 긴 동일한 엔터티를 수정하는 다른 쿼리는 쿼리 저장소 살펴봅니다. 이러한 쿼리를 식별한 후에는 애플리케이션 논리를 변경하여 동시성을 개선하거나 덜 제한적인 격리 수준을 사용하는 것이 좋습니다.
데이터베이스당 높은 PAGEIOLATCH_SH 대기 특정 쿼리에 대한 쿼리 저장소 높은 버퍼 IO 대기 쿼리 저장소 실제 읽기 수가 많은 쿼리를 찾습니다. IO 대기가 높은 쿼리와 일치하는 경우 검색 대신 찾기를 수행하여 쿼리의 IO 오버헤드를 최소화하기 위해 기본 엔터티에 대한 인덱스를 도입하는 것이 좋습니다.
데이터베이스당 높은 SOS_SCHEDULER_YIELD 대기 특정 쿼리에 대한 쿼리 저장소 높은 CPU 대기 쿼리 저장소 최상위 CPU 사용 쿼리를 찾습니다. 그 중에서 높은 CPU 추세가 영향을 받는 쿼리에 대한 높은 CPU 대기와 상관 관계가 있는 쿼리를 식별합니다. 이러한 쿼리를 최적화하는 데 집중합니다. 계획 회귀 또는 누락된 인덱스가 있을 수 있습니다.

구성 옵션

쿼리 저장소 매개 변수를 구성하는 데 사용할 수 있는 옵션은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

뷰를 sys.database_query_store_options 쿼리하여 쿼리 저장소 현재 옵션을 확인합니다. 값에 대한 자세한 내용은 sys.database_query_store_options 참조하세요.

Transact-SQL 문을 사용하여 구성 옵션을 설정하는 방법에 대한 예제는 옵션 관리를 참조하세요.

참고 항목

Azure Synapse Analytics의 경우 다른 플랫폼에서와 같이 쿼리 저장소를 사용하도록 설정할 수 있지만 추가 구성 옵션은 지원되지 않습니다.

Management Studio를 통해 또는 다음 보기 및 절차를 사용하여 쿼리 저장소 보고 관리합니다.

쿼리 저장소 함수

함수는 쿼리 저장소 작업에 도움이 됩니다.

쿼리 저장소 카탈로그 뷰

카탈로그 뷰는 쿼리 저장소 대한 정보를 제공합니다.

저장 프로시저 쿼리 저장소

저장 프로시저는 쿼리 저장소 구성합니다.

sp_query_store_consistency_check (Transact-SQL)1

1 극단적인 시나리오에서는 내부 오류로 인해 쿼리 저장소 오류 상태가 될 수 있습니다. SQL Server 2017(14.x)부터 이 경우 영향을 받는 데이터베이스에서 저장 프로시저를 실행하여 sp_query_store_consistency_check 쿼리 저장소 복구할 수 있습니다. 열 설명에 actual_state_desc 설명된 자세한 내용은 sys.database_query_store_options 참조하세요.

쿼리 저장소 기본 테넌트

쿼리 저장소 기본 테넌트 및 관리에 대한 모범 사례 및 권장 사항은 이 문서에서 확장되었습니다. 쿼리 저장소 관리하기 위한 모범 사례입니다.

성능 감사 및 문제 해결

쿼리 저장소를 사용하여 성능 조정에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 조정을 참조하세요.

기타 성능 항목:

참고 항목

다음 단계