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

아티클
07/12/2024

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics(전용 SQL 풀 한정)

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

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

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에서 쿼리 저장소 페이지 사용

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

참고 항목

Management Studio 버전 16 이상이 필요합니다.
데이터베이스 속성 대화 상자에서 쿼리 저장소 페이지를 선택합니다.
작업 모드(요청됨) 상자에서 읽기 쓰기를 선택합니다.

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 데이터베이스부터 쿼리 저장소는 대기 통계를 추적하는 차원을 포함합니다. 다음 예제에서는 쿼리 저장소 대기 통계를 수집할 수 있도록 합니다.

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

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

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

쿼리 저장소에는 다음과 같은 3가지 저장소가 포함되어 있습니다.

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

쿼리 저장소에서 쿼리에 대해 저장할 수 있는 고유한 계획의 수는 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)부터)

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

보조 복제본의 쿼리 저장소 대한 자세한 내용은 Always On 가용성 그룹 보조 복제본을 위한 쿼리 저장소를 참조하세요.

회귀된 쿼리 기능 사용

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

SSMS 개체 탐색기의 쿼리 저장소 보고 트리 스크린샷.

참고 항목

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

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

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

SSMS 개체 탐색기의 SQL Server 회귀된 쿼리 보고서 스크린샷.

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

대기 중인 쿼리 찾기

SQL Server 2017(14.x)부터, 그리고 Azure SQL 데이터베이스에서 쿼리 저장소의 시간별 쿼리당 대기 통계를 확인할 수 있습니다.

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

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

SSMS 개체 탐색기의 SQL Server 쿼리 대기 통계 보고서 스크린샷.

선택한 대기 범주 디스플레이에서 막대 및 세부 정보 보기를 선택하여 대기 범주를 선택합니다. 이 새 가로 막대형 차트에는 해당 대기 범주에 기여한 쿼리가 포함되어 있습니다.

맨 위에 있는 드롭다운 목록 상자를 사용하여 평균, 최대, 최소, 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를 통해 또는 다음 보기 및 절차를 사용하여 쿼리 저장소를 보고 관리합니다.

쿼리 저장소 함수

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

sys.fn_stmt_sql_handle_from_sql_stmt(Transact-SQL)

쿼리 저장소 카탈로그 뷰

카탈로그 뷰에 쿼리 저장소에 대한 정보가 표시됩니다.

sys.database_query_store_options(Transact-SQL)

sys.query_context_settings(Transact-SQL)

sys.query_store_plan(Transact-SQL)

sys.query_store_query(Transact-SQL)

sys.query_store_query_text(Transact-SQL)

sys.query_store_runtime_stats(Transact-SQL)

sys.query_store_wait_stats(Transact-SQL)

sys.query_store_runtime_stats_interval(Transact-SQL)

sys.database_query_store_internal_state(Transact-SQL)

쿼리 저장소 저장 프로시저

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

sp_query_store_flush_db(Transact-SQL)

sp_query_store_reset_exec_stats(Transact-SQL)

sp_query_store_force_plan(Transact-SQL)

sp_query_store_unforce_plan(Transact-SQL)

sp_query_store_remove_plan(Transact-SQL)

sp_query_store_remove_query(Transact-SQL)

sp_query_store_clear_message_queues(Transact-SQL)

sp_query_store_consistency_check(Transact-SQL)¹

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

쿼리 저장소 유지 관리

쿼리 저장소 유지 관리 및 관리에 대한 모범 사례 및 권장 사항은 쿼리 저장소 관리하기 위한 모범 사례 문서에서 확장되었습니다.

성능 감사 및 문제 해결

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

기타 성능 항목:

쿼리 저장소 사용 시나리오

다음을 통해 공유

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

쿼리 저장소 사용

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

Transact-SQL 문 사용

쿼리 저장소의 정보

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

회귀된 쿼리 기능 사용

대기 중인 쿼리 찾기

구성 옵션

쿼리 저장소 함수

쿼리 저장소 카탈로그 뷰

쿼리 저장소 저장 프로시저

쿼리 저장소 유지 관리

성능 감사 및 문제 해결

참고 항목

다음 단계

피드백

피드백

추가 리소스

다음을 통해 공유

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

쿼리 저장소 사용

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

Transact-SQL 문 사용

쿼리 저장소의 정보

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

회귀된 쿼리 기능 사용

대기 중인 쿼리 찾기

구성 옵션

관련된 뷰, 함수, 프로시저

쿼리 저장소 함수

쿼리 저장소 카탈로그 뷰

쿼리 저장소 저장 프로시저

쿼리 저장소 유지 관리

성능 감사 및 문제 해결

참고 항목

다음 단계

피드백

피드백

추가 리소스