쿼리 저장소 살펴보기
SQL Server 쿼리 저장소는 쿼리, 계획 및 런타임 통계 기록을 자동으로 캡처하여 성능 문제 해결 및 쿼리 튜닝을 간소화하는 데이터베이스별 기능입니다. 데이터베이스 사용 패턴 및 리소스 사용량에 대한 인사이트도 제공합니다.
쿼리 저장소는 세 개의 저장소로 구성됩니다.
- 계획 저장소: 예상 실행 계획 정보를 저장합니다.
- 런타임 통계 저장소: 실행 통계 정보를 저장합니다.
- 대기 통계 저장소: 대기 통계 정보를 유지합니다.
쿼리 저장소 사용
쿼리 저장소는 기본적으로 Azure SQL 데이터베이스에서 사용하도록 설정됩니다. SQL Server 및 Azure Synapse Analytics와 함께 사용하려면 먼저 쿼리 저장소를 사용하도록 설정해야 합니다. 쿼리 저장소 기능을 사용하도록 설정하려면 다음 쿼리를 자신의 환경에 맞게 사용하세요.
-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
쿼리 저장소에서 데이터를 수집하는 방법
쿼리 저장소는 여러 단계에서 쿼리 처리 파이프라인과 통합됩니다. 각 통합 지점에서 데이터는 메모리에 수집되고, I/O 오버헤드를 최소화하기 위해 비동기적으로 디스크에 기록됩니다. 통합 지점은 다음과 같습니다.
쿼리를 처음으로 실행하면 쿼리 텍스트와 최초 실행 계획이 쿼리 저장소에 전송되어 유지됩니다.
쿼리가 다시 컴파일되면 쿼리 저장소에서 계획이 업데이트됩니다. 다시 컴파일한 결과 실행 계획이 새로 생성되면, 이전 계획을 보강하기 위해 새 실행 계획이 쿼리 저장소에 유지됩니다. 또한 쿼리 저장소는 비교를 위해 각 쿼리 계획의 실행 통계를 추적합니다.
컴파일 및 재컴파일 단계 확인을 진행하는 동안, 쿼리 저장소에서 쿼리의 강제 실행 계획이 있는지 확인합니다. 쿼리 저장소 프로시저 캐시에 있는 계획과 다른 강제 계획을 제공하는 경우에는 쿼리가 다시 컴파일됩니다.
쿼리가 실행되면 관련 런타임 통계가 쿼리 저장소에 유지됩니다. 쿼리 저장소는 모든 쿼리 계획을 정확하게 표현하기 위해 이 데이터를 집계합니다.
쿼리 저장소에서 데이터를 수집하는 방법에 대한 자세한 내용은 쿼리 저장소에서 데이터를 수집하는 방법을 참조하세요.
일반적인 시나리오
SQL Server 쿼리 저장소는 데이터베이스 작업의 성능에 대한 귀중한 인사이트를 제공합니다. 일반적인 시나리오는 다음과 같습니다.
- 열등한 쿼리 실행 계획 선택으로 인한 성능 회귀 확인 및 수정
- 가장 높은 리소스 사용량 쿼리 확인 및 튜닝.
- 데이터베이스 및 애플리케이션 변경의 영향을 평가하는 A/B 테스트.
- SQL Server 업그레이드 후 성능 안정성 보장.
- 가장 자주 사용한 쿼리 확인.
- 쿼리에 대한 쿼리 계획의 기록을 감사합니다.
- 계획되지 않은 워크로드 식별 및 개선.
- 데이터베이스의 일반적인 대기 범주 및 대기 시간에 영향을 주는 쿼리와 계획 이해
- 리소스 사용량(CPU, I/O, 메모리) 측면에서 시간 경과에 따른 데이터베이스 사용량 패턴을 분석합니다.
쿼리 저장소 뷰 검색
데이터베이스에서 쿼리 저장소가 사용하도록 설정되면, 개체 탐색기의 데이터베이스에 쿼리 저장소 폴더가 표시됩니다. Azure Synapse Analytics의 경우 쿼리 저장소 뷰는 시스템 뷰에 표시됩니다. 쿼리 저장소 뷰를 이용하면 SQL Server 데이터베이스의 성능 측면을 대상으로 집계한 인사이트를 빠르게 확인할 수 있습니다.
회귀된 쿼리
회귀된 쿼리는 실행 계획이 변경되어 시간이 지남에 따라 성능 저하를 겪습니다. 예상 실행 계획은 스키마 변경, 통계 변경, 인덱스 변경을 포함한 다양한 요인으로 인해 변경될 수 있습니다. 프로시저 캐시를 조사하는 것이 가장 먼저 떠오르겠지만, 여기에는 쿼리에 대한 최신 실행 계획만 저장되며, 계획은 시스템의 메모리 요구에 따라 제거될 수 있습니다. 그러나 쿼리 저장소는 각 쿼리에 대해 여러 실행 계획을 유지하므로 계획 변경으로 인한 쿼리 성능 회귀를 처리하도록 강제하는 계획을 통해 특정 계획을 유연하게 선택할 수 있습니다.
회귀된 쿼리 뷰는 지정된 기간 동안 실행 계획 변경으로 인해 실행 메트릭이 회귀하는 쿼리를 정확히 파악할 수 있습니다. 이 보기에서는 선택한 메트릭(기간, CPU 시간, 행 수 등)과 통계(전체, 평균, 최솟값, 최댓값 또는 표준 편차)를 기준으로 필터링할 수 있습니다. 그런 다음 제공된 필터를 기준으로 상위 25개 회귀 쿼리를 나열합니다. 기본적으로 쿼리는 그래픽 막대형 차트로 표시되지만, 선택적으로 쿼리를 그리드 형식으로 볼 수 있습니다.
계획 요약 창에는 왼쪽 상단 쿼리 창에서 쿼리를 선택한 후 시간이 지남에 따라 쿼리와 연결되는 지속형 쿼리 계획이 표시됩니다. 계획 요약 창에서 쿼리 계획을 선택하면 하단 창에 그래픽 쿼리 계획이 표시됩니다. 계획 요약 창과 그래픽 쿼리 계획 창 모두에 있는 도구 모음 단추를 사용하면 선택한 쿼리에 대해 선택한 계획을 강제로 적용할 수 있습니다. 이 창 구조와 동작은 모든 SQL 쿼리 보기에서 일관되게 사용됩니다.
sp_query_store_force_plan 저장 프로시저를 사용하여 계획 강제 적용을 사용할 수도 있습니다.
EXEC sp_query_store_force_plan @query_id=73, @plan_id=79
전체 리소스 사용량
전체 리소스 사용량 보기를 사용하면 지정된 기간 동안 여러 실행 메트릭(예: 실행 수, 기간, 대기 시간 등)에 대한 총 리소스 사용량을 분석할 수 있습니다. 렌더링된 차트는 대화형입니다. 차트 중 하나에서 측정값을 선택하면 선택한 측정값과 연결된 쿼리를 표시하는 드릴스루 뷰가 새 탭에 표시됩니다.
세부 정보 뷰에서는 선택한 메트릭에 기여한 상위 25개의 리소스 소비자 쿼리를 확인할 수 있습니다. 이 세부 정보 뷰는 연결된 쿼리 및 쿼리 세부 정보를 검사하고, 저장된 예상 쿼리 계획을 평가하고, 필요한 경우 계획 강제 사용을 통해 성능을 개선할 수 있는 일관된 인터페이스를 사용합니다. 이 뷰는 CPU 사용량이 용량에 도달하는 경우처럼 시스템 리소스 경합이 문제가 될 때 유용합니다.
리소스 사용량 상위 쿼리
상위 리소스 사용 쿼리 보기는 전체 리소스 사용량 보기의 세부 정보 드릴다운과 유사합니다. 메트릭 및 통계를 필터로 선택할 수도 있습니다. 그러나 여기서 표시되는 쿼리는 선택한 필터 및 기간을 기준으로 가장 영향력이 큰 상위 25개 쿼리입니다.
상위 리소스 사용 쿼리 보기는 계획되지 않은 워크로드를 식별하고 개선할 때 계획되지 않은 워크로드의 특성을 처음으로 보여 줍니다. 예를 들어 다음 이미지에서는 실행 수 메트릭 및 합계 통계가 선택되어, 리소스를 사용하는 상위 쿼리의 약 90%가 한 번만 실행됨을 표시합니다.
강제 계획이 포함된 쿼리
강제 계획이 있는 쿼리 보기는 강제 쿼리 계획이 있는 쿼리를 빠르게 살펴봅니다. 이 뷰는 강제 계획이 더 이상 예상대로 실행되지 않고 다시 평가해야 하는 경우에 유효합니다. 이 뷰에서는 선택한 쿼리에 대한 지속형 예상 실행 계획을 모두 검토하여 성능에 더 적합한 다른 계획이 있는지를 쉽게 확인할 수 있습니다. 더 적합한 계획이 있다면 도구 모음 단추를 사용하여 계획 강제 적용을 해제할 수 있습니다.
고변형 쿼리
쿼리 성능은 실행할 때마다 다를 수 있습니다. 변형이 높은 쿼리 보기에는 선택한 메트릭에 대한 변형 또는 표준 편차가 가장 높은 쿼리 분석이 포함되어 있습니다. 인터페이스는 쿼리 세부 정보 검사, 실행 계획 평가 및 필요한 경우 특정 계획 강제 적용을 할 수 있는 대부분의 쿼리 저장소 뷰와 일치합니다. 이 뷰를 사용하여 예측할 수 없는 쿼리를 보다 일관적인 성능 패턴으로 튜닝하세요.
쿼리 대기 통계
쿼리 대기 통계 보기는 데이터베이스에 대한 가장 활성 대기 범주를 분석하고 차트를 렌더링합니다. 이 차트는 대화형입니다. 대기 범주를 선택하면 대기 시간 통계에 기여하는 쿼리의 세부 정보를 자세히 살펴볼 수 있습니다.
세부 정보 뷰 인터페이스도 쿼리 세부 정보 검사, 실행 계획 평가 및 필요한 경우 특정 계획 강제 적용을 할 수 있는 대부분의 쿼리 저장소 뷰와 일치합니다. 이 뷰는 애플리케이션 전체에서 사용자 환경에 영향을 주는 쿼리를 식별하는 데 도움이 됩니다.
추적 쿼리
추적 쿼리 뷰를 사용하면 입력한 쿼리 ID 값을 기반으로 특정 쿼리를 분석할 수 있습니다. 실행하면 뷰는 쿼리의 전체 실행 기록을 제공합니다. 실행에 확인 표시가 되어 있다면 강제 계획이 사용되었다는 뜻입니다. 이 뷰는 쿼리 성능이 안정적으로 유지되는지 확인할 수 있도록 강제 계획이 포함된 쿼리 같은 쿼리에 대한 인사이트를 제공합니다.
쿼리 저장소를 사용하여 쿼리 대기 찾기
시스템의 성능이 저하되기 시작하면 쿼리 대기 통계를 참조하여 원인을 파악하는 것이 좋습니다. 튜닝이 필요한 쿼리 식별에 더해, 도움이 될 수 있는 잠재적인 인프라 업그레이드도 파악할 수 있습니다.
SQL 쿼리 저장소는 데이터베이스의 상위 대기 범주에 대한 인사이트를 제공하기 위해 쿼리 대기 통계 보기를 제공합니다. 현재 대기 범주는 23개입니다.
가로 막대형 차트는 쿼리 대기 통계 뷰를 열 때 데이터베이스에 대한 가장 영향력 있는 대기 범주를 표시합니다. 또한 대기 범주 창의 도구 모음에 있는 필터를 사용하면 총 대기 시간(기본값), 평균 대기 시간, 최소 대기 시간, 최대 대기 시간 또는 표준 편차 대기 시간을 기준으로 대기 통계를 계산할 수 있습니다.
대기 범주를 선택하면 해당 대기 범주에 기여하는 쿼리의 세부 정보로 이동합니다. 이 뷰에서는 가장 영향력 있는 개별 쿼리를 조사할 수 있습니다. 쿼리 창에서 쿼리를 선택하면 계획 요약 창에 표시되는 지속형 예상 실행 계획에 액세스할 수 있습니다. 계획 요약 창에서 쿼리 계획을 선택하면 하단 창에 그래픽 쿼리 계획이 표시됩니다. 이 뷰에서는 쿼리에 대한 쿼리 계획을 강제로 적용하거나 강제 적용을 해제하여 성능을 개선할 수 있습니다.
자동 플랜 수정
SQL Server 2017 및 Azure SQL Database에서는 쿼리 저장소 데이터를 분석하는 자동 플랜 수정이라는 개념을 도입했습니다. SQL Server 2017 이상 및 Azure SQL Database에서 쿼리 저장소를 사용하도록 설정하는 경우 SQL Server 엔진은 쿼리 계획 회귀를 찾은 후 권장 사항을 제공합니다. sys.dm_db_tuning_recommendations DMV(동적 관리 뷰)에서 이러한 권장 사항을 볼 수 있습니다. 이러한 권장 사항에는 성능이 “양호한 상태”일 때 수동으로 쿼리 계획을 강제 적용하는 T-SQL 문이 포함됩니다.
이러한 권장 사항을 확신하는 경우 회귀가 발생할 때 플랜을 자동으로 적용하도록 SQL Server를 설정할 수 있습니다. ALTER DATABASE 및 AUTOMATIC_TUNING 인수를 사용하여 자동 계획 수정을 사용하도록 설정합니다.
Azure SQL Database의 경우 Azure Portal 또는 REST API에서 자동 조정 옵션을 통해 자동 플랜 수정을 사용하도록 설정할 수도 있습니다. 자동 플랜 수정 권장 사항은 쿼리 저장소를 사용하도록 설정한(Azure SQL Database 및 Azure SQL Managed Instance의 기본 설정) 모든 데이터베이스에서 항상 사용하도록 설정됩니다. 새 데이터베이스의 경우 Azure SQL Database에 대해 자동 계획 수정(FORCE_PLAN)이 기본적으로 사용하도록 설정됩니다.