쿼리 저장소 사용 시나리오

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

쿼리 저장소는 예측 가능한 워크로드 성능을 추적하고 보장하는 것이 중요한 광범위한 시나리오에서 사용될 수 있습니다. 다음은 고려할 수 있는 몇 가지 예입니다.

• 계획 선택 재발이 있는 쿼리 식별 및 수정

• 상위 리소스 사용 쿼리 식별 및 튜닝

• A/B 테스트

• SQL Server로 업그레이드하는 동안 성능 안정성 유지

• 임시 워크로드 식별 및 개선

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

• 실행 가능한 정보를 검색하고 쿼리 저장소 사용하여 성능을 조정하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 조정을 참조하세요.

• Azure SQL Database에서 쿼리 저장소를 운영하는 방법에 대한 자세한 내용은 Azure SQL Database에서 쿼리 저장소 운영을 참조 하세요.

계획 선택 재발이 있는 쿼리 식별 및 수정

일반 쿼리 실행 중 데이터 카디널리티가 변경되거나, 인덱스가 생성, 변경 또는 삭제되거나, 통계가 업데이트되는 등 중요한 입력이 달라졌기 때문에 쿼리 최적화 프로그램에서 다른 계획을 사용하도록 결정하는 경우가 있을 수 있습니다. 일반적으로 새 계획은 이전에 사용된 계획보다 더 낫거나 거의 동일합니다. 그러나 새 계획이 훨씬 더 악화되는 경우가 있습니다. 이 상황을 계획 선택 변경 회귀라고 합니다. 쿼리 저장소 이전에는 SQL Server에서 사용자가 시간이 지남에 따라 사용된 실행 계획을 확인할 수 있는 기본 제공 데이터 스토리지를 제공하지 않았기 때문에 이는 식별하고 수정하기 어려운 문제였습니다.

쿼리 저장소를 사용하여 다음을 신속하게 수행할 수 있습니다.

• 관심 기간(지난 1시간, 일, 주 등)에서 실행 메트릭이 저하된 모든 쿼리를 식별합니다. SQL Server Management Studio에서 재발된 쿼리를 사용하여 분석 속도를 높입니다.

• 재발된 쿼리 중에서 여러 계획이 있고 잘못된 계획 선택으로 인해 저하된 쿼리를 쉽게 찾을 수 있습니다. 회귀된 쿼리의 계획 요약 창을 사용하여 회귀된 쿼리에 대한 모든 계획과 시간에 따른 쿼리 성능을 시각화합니다.

• 기록에서 이전 계획이 더 나은 것으로 증명된 경우 이를 적용합니다. 회귀된 쿼리에서 계획 강제 적용 단추를 사용하여 쿼리에 대해 선택한 계획을 강제로 적용합니다.

시나리오에 대한 자세한 설명은 쿼리 저장소: 데이터베이스 블로그의 플라이트 데이터 레코더를 참조하세요.

상위 리소스 사용 쿼리 식별 및 튜닝

워크로드에서 수천 개의 쿼리를 생성할 수 있지만 일반적으로 그 중 소수의 쿼리만 대부분의 시스템 리소스를 사용하므로 주의가 필요합니다. 상위 리소스 소비 쿼리 중에서 추가 조정을 통해 개선할 수 있는 쿼리 또는 재발된 쿼리를 찾습니다.

탐색을 시작하는 가장 쉬운 방법은 Management Studio에서 상위 리소스 소비 쿼리를 여는 것입니다. 사용자 인터페이스는 상위 리소스 소비 쿼리를 나타내는 히스토그램(왼쪽), 선택한 쿼리에 대한 계획 요약(오른쪽) 및 선택한 계획에 대한 시각적 쿼리 계획(아래쪽)의 세 개의 창으로 구분됩니다. 구성을 선택하여 분석할 쿼리 수 및 관심 있는 시간 간격을 제어할 수 있습니다. 또한 다양한 리소스 소비 차원(기간, CPU, 메모리, IO, 실행 수) 및 기준(평균, 최소, 최대, 합계, 표준 편차) 중에서 선택할 수 있습니다.

오른쪽의 계획 요약을 살펴보고 실행 기록을 분석하고 다양한 계획 및 해당 런타임 통계에 대해 알아봅니다. 아래쪽 창을 사용하여 다른 계획을 검사하거나 나란히 렌더링하여 시각적으로 비교(비교 단추 사용)할 수 있습니다.

최적이 아니면 쿼리를 식별하는 경우 작업은 문제의 특성에 따라 달라집니다.

1. 쿼리가 여러 계획으로 실행되고 마지막 계획이 이전 계획보다 훨씬 나쁜 경우 계획 적용 메커니즘을 사용하여 SQL Server에서 향후 실행에 최적 계획을 사용하도록 할 수 있습니다.

2. 최적화 프로그램이 XML 계획에서 누락된 인덱스를 제안하는지 확인합니다. 그렇다면 누락된 인덱스 만들기 및 쿼리 저장소를 사용하여 인덱스 생성 후 쿼리 성능 평가

3. 쿼리에서 사용하는 기본 테이블에 대한 통계가 최신 상태인지 확인합니다.

4. 쿼리에서 사용하는 인덱스가 조각 모음되었는지 확인합니다.

5. 비용이 많이 드는 쿼리를 다시 작성하는 것이 좋습니다. 예를 들어 쿼리 매개 변수화를 활용하고 동적 SQL의 사용량을 줄입니다. 데이터를 읽을 때의 최적 논리를 구현합니다(애플리케이션 쪽이 아니라 데이터베이스 쪽에서 데이터 필터링 적용).

A/B 테스트

쿼리 저장소를 사용하여 애플리케이션 변경 이전과 이후의 워크로드 성능을 비교합니다.

다음 목록에는 쿼리 저장소를 사용하여 환경 또는 애플리케이션 변경이 워크로드 성능에 미치는 영향을 평가할 수 있는 몇 가지 예제가 포함되어 있습니다.

• 새 애플리케이션 버전을 배포합니다.

• 서버에 새 하드웨어 추가

• 비용이 많이 드는 쿼리에서 참조하는 테이블에 누락된 인덱스 만들기

• 행 수준 보안을 위한 필터링 정책 적용 자세한 내용은 쿼리 저장소를 사용하여 행 수준 보안 최적화를 참조 하세요.

• OLTP 애플리케이션에서 자주 수정하는 테이블에 임시 시스템 버전 관리 추가

이러한 시나리오에서 다음 워크플로를 적용합니다.

1. 계획된 변경 전에 쿼리 저장소를 사용하여 워크로드를 실행하여 성능 기준을 생성합니다.

2. 제어된 순간에 애플리케이션 변경 내용을 적용합니다.

3. 변경 후 시스템의 성능 이미지를 생성할 수 있는 충분한 시간 동안 워크로드를 계속 실행합니다.

4. #1 및 #3의 결과를 비교합니다.

   1. 전체 데이터베이스 사용량을 열어 전체 데이터베이스에 미치는 영향을 확인합니다.

   2. 상위 리소스 소비 쿼리를 열거나 Transact-SQL을 사용한 사용자 고유 분석을 실행하여 가장 중요한 쿼리에 미치는 변경의 영향을 분석합니다.

5. 새 성능이 허용되지 않는 경우 변경을 유지할지 또는 롤백을 수행할지 결정합니다.

다음 그림에서는 인덱스 만들기가 누락된 경우 쿼리 저장소 분석(4단계)을 보여 줍니다. 상위 리소스 사용 쿼리/계획 요약 창을 열어 인덱스 생성의 영향을 받는 쿼리에 대한 이 보기를 가져옵니다.

또한 인덱스 만들기 전후에 계획을 나란히 렌더링하여 비교할 수 있습니다. 도구 모음에 빨간색 사각형으로 표시된 "별도의 창에서 선택한 쿼리에 대한 계획 비교" 도구 모음 옵션입니다.

인덱스 만들기 이전 계획(plan_id = 1, 위)에 누락된 인덱스 힌트가 있으며, Clustered Index Scan이 쿼리에서 가장 부담이 큰 연산자였음을 검사할 수 있습니다(빨간색 사각형).

이제 누락된 인덱스 만들기 이후 계획(plan_id = 15, 아래)에 전체 쿼리 비용을 줄이고 성능을 개선하는 Index Seek(비클러스터형)가 있습니다(녹색 사각형).

분석을 기반으로 쿼리 성능이 향상됨에 따라 인덱스를 유지할 수 있습니다.

SQL Server로 업그레이드하는 동안 성능 안정성 유지

SQL Server 2014(12.x) 이전에는 사용자가 최신 플랫폼 버전으로 업그레이드하는 동안 성능 회귀 위험에 노출되었습니다. 이러한 이유로 새 비트가 설치되는 즉시 최신 버전의 쿼리 최적화 프로그램이 활성화되었습니다.

SQL Server 2014(12.x)부터는 쿼리 최적화 프로그램의 모든 변경 내용이 최신 데이터베이스 호환성 수준에 연결되므로 계획이 업그레이드 시점에 즉시 변경되지 않고 사용자가 COMPATIBILITY_LEVEL을 최신 상태로 변경할 때 변경됩니다. 이 기능은 쿼리 저장소와 함께 업그레이드 프로세스에서 쿼리 성능에 대한 뛰어난 제어 수준을 제공합니다. 권장 업그레이드 워크플로는 다음 그림에 나와 있습니다.

1. 데이터베이스 호환성 수준을 변경하지 않고 SQL Server를 업그레이드합니다. 최신 쿼리 최적화 프로그램 변경 내용을 노출하지 않고 쿼리 저장소를 포함하는 최신 SQL Server 기능을 제공합니다.

2. 쿼리 저장소를 사용하도록 설정합니다. 자세한 내용은 쿼리 저장소를 워크로드에 맞게 조정된 상태로 유지를 참조하세요.

3. 쿼리 저장소가 쿼리 및 계획을 캡처하도록 허용하고 원본/이전 데이터베이스 호환성 수준으로 성능 기준을 설정합니다. 모든 계획을 캡처하고 안정적인 기준선을 얻을 때까지 이 단계를 계속 유지합니다. 이는 프로덕션 워크로드에 대한 일반적인 비즈니스 주기의 기간일 수 있습니다.

4. 최신 데이터베이스 호환성 수준으로 이동: 워크로드를 최신 쿼리 최적화 프로그램에 노출하여 잠재적으로 새 계획을 만들 수 있도록 합니다.

5. 분석 및 회귀 수정에 쿼리 저장소 사용: 대부분의 경우 새 쿼리 최적화 프로그램 개선 사항은 더 나은 계획을 생성해야 합니다. 그러나 쿼리 저장소는 계획 선택 회귀를 식별하고 계획 강제 적용 메커니즘을 사용하여 수정하는 간편한 방법을 제공합니다. SQL Server 2017(14.x)부터는 자동 계획 수정 기능을 사용할 때 이 단계가 자동화됩니다.

   a. 회귀가 있는 경우 쿼리 저장소에서 이미 알려진 좋은 계획을 강제로 적용합니다.

   b. 강제로 적용하지 못하는 쿼리 계획이 있거나 성능이 아직 부족한 경우 데이터베이스 호환성 수준을 이전 설정으로 되돌려 Microsoft 고객 지원에 참여하는 것이 좋습니다.

팁

SQL Server Management Studio 데이터베이스 업그레이드 작업을 사용하여 데이터베이스 의 데이터베이스 호환성 수준을 업그레이드합니다. 자세한 내용은 쿼리 튜닝 도우미를 사용하여 데이터베이스 업그레이드를 참조하세요.

임시 워크로드 식별 및 개선

일부 워크로드에는 전체 애플리케이션 성능을 개선하기 위해 조정할 수 있는 주요 쿼리가 없습니다. 이러한 워크로드는 일반적으로 각각 시스템 리소스의 일부를 소비하는 비교적 많은 수의 다른 쿼리로 특징지어집니다. 고유하기 때문에 이러한 쿼리는 매우 드물게 실행되므로(일반적으로 한 번만, 따라서 임시로 이름 지정) 런타임 사용량이 중요하지 않습니다. 반면, 애플리케이션이 항상 완전히 새로운 쿼리를 생성하는 경우에는 시스템 리소스의 상당 부분이 최적화되지 않은 쿼리 컴파일에 소비됩니다. 이는 많은 수의 쿼리와 계획이 예약된 공간을 차지하는 경우 쿼리 저장소에 이상적인 상황이 아닙니다. 즉, 쿼리 저장소가 매우 빠르게 읽기 전용 모드로 전환될 수 있습니다. 크기 기반 정리 정책을 활성화한 경우(쿼리 저장소를 항상 가동 및 실행하는 것이 좋습니다.) 백그라운드 프로세스는 대부분의 시간 동안 쿼리 저장소 구조를 정리하며 상당한 시스템 리소스를 사용합니다.

상위 리소스 소비 쿼리 뷰는 워크로드의 임시 특성에 대한 첫 번째 표시를 제공합니다.

실행 수 메트릭을 사용하여 상위 쿼리가 임시 쿼리인지 분석합니다( QUERY_CAPTURE_MODE = ALL을 사용하여 쿼리 저장소를 실행해야 함). 위의 다이어그램에서 상위 리소스 사용 쿼리의 90%가 한 번만 실행되는 것을 볼 수 있습니다.

또는 Transact-SQL 스크립트를 실행하여 시스템의 총 쿼리 텍스트 수, 쿼리 수, 계획 수를 파악하고 query_hash 및 query_plan_hash를 비교하여 차이점을 확인할 수 있습니다.

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

이는 임시 쿼리를 사용하는 워크로드의 경우 얻을 수 있는 잠재적 결과 중 하나입니다.

쿼리 결과는 쿼리 저장소에 많은 수의 쿼리와 계획이 있음에도 불구하고 해당 query_hash 및 query_plan_hash가 실제로 다르지 않음을 보여 줍니다. 고유한 쿼리 텍스트와 1보다 훨씬 큰 고유 쿼리 해시 간의 비율은 쿼리 간의 유일한 차이점은 쿼리 텍스트의 일부로 제공되는 리터럴 상수(매개 변수)이므로 워크로드가 매개 변수화에 적합하다는 것을 나타냅니다.

일반적으로 이 상황은 애플리케이션이 저장 프로시저 또는 매개 변수가 있는 쿼리를 호출하는 대신 쿼리를 생성하거나 기본적으로 쿼리를 생성하는 개체 관계형 매핑 프레임워크를 사용하는 경우에 발생합니다.

애플리케이션 코드를 제어하는 경우 데이터 액세스 계층을 다시 작성하여 저장 프로시저 또는 매개 변수가 있는 쿼리를 활용하는 것이 좋습니다. 그러나 전체 데이터베이스(모든 쿼리) 또는 query_hash가 동일한 개별 쿼리 템플릿에 대해 쿼리 매개 변수화를 적용하여 애플리케이션을 변경하지 않고 이 상황을 크게 개선할 수도 있습니다.

개별 쿼리 템플릿을 사용하는 방법에는 계획 가이드를 생성해야 합니다.

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

계획 지침이 있는 솔루션이 더 정확하지만 여기에는 추가 작업이 필요합니다.

모든 쿼리(또는 대부분의 쿼리)가 자동 매개 변수화에 적합한 경우 전체 데이터베이스에 대해 PARAMETERIZATION = FORCED 구성을 고려하세요. 자세한 내용은 강제 매개 변수화 사용에 대한 지침을 참조 하세요.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

이러한 단계를 적용하면 상위 리소스 사용 쿼리에 워크로드에 대한 다양한 그림이 표시됩니다.

경우에 따라 애플리케이션은 자동 매개 변수화에 적합하지 않은 다양한 쿼리를 많이 생성할 수 있습니다. 이 경우 시스템에 많은 수의 쿼리가 표시되지만 고유 쿼리와 고유 query_hash 쿼리 간의 비율은 1에 가까울 수 있습니다.

이 경우 임시 워크로드 서버 최적화 옵션을 사용하도록 설정하여 다시 실행되지 않는 쿼리에서 캐시 메모리를 낭비하지 않도록 할 수 있습니다. 쿼리 저장소에서 이러한 쿼리의 캡처를 방지하려면 QUERY_CAPTURE_MODE 를 AUTO로 설정합니다.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

다음 단계

• 쿼리 저장소를 사용하여 성능 모니터링
• 쿼리 저장소에 대한 모범 사례
• 쿼리 튜닝 도우미를 사용하여 데이터베이스 업그레이드