쿼리 튜닝 도우미를 사용하여 데이터베이스 업그레이드

아티클
07/12/2024

적용 대상: SQL Server 2016(13.x) 이상 버전 Azure SQL Database Azure Synapse Analytics Analytics Platform System(PDW)

이전 버전의 SQL Server에서 SQL Server 2014(12.x) 이상 버전으로 마이그레이션할 때, 그리고 사용 가능한 최신 상태로 데이터베이스 호환성 수준을 업그레이드할 때는 워크로드가 성능 저하 위험에 노출될 수 있습니다. 이는 SQL Server 2014(12.x)와 최신 버전 간에 업그레이드할 때도 더 낮은 수준까지 가능합니다.

SQL Server 2014(12.x)부터 모든 새 버전에서는 쿼리 최적화 프로그램의 모든 변경 내용이 최신 데이터베이스 호환성 수준으로 제어되므로 실행 계획이 업그레이드 시점에 즉시 변경되지 않고 사용자가 COMPATIBILITY_LEVEL 데이터베이스 옵션을 최신 버전으로 변경하는 경우에 변경됩니다. Server 2014(12.x)에 도입된 쿼리 최적화 프로그램 변경 사항에 대한 자세한 내용은 카디널리티 예측 도구를 참조하세요. 호환성 수준 및 업그레이드에 미치는 영향에 대한 자세한 내용은 호환성 수준 및 데이터베이스 엔진 업그레이드를 참조하세요.

데이터베이스 호환성 수준에서 제공되는 이 제한 기능은 쿼리 저장소와 함께 업그레이드가 아래에 나와 있는 권장 워크플로를 따를 경우 업그레이드 프로세스의 쿼리 성능에 대한 상당한 수준의 제어를 제공합니다. 호환성 수준 업그레이드를 위해 권장되는 워크플로에 대한 자세한 내용은 데이터베이스 호환성 모드 변경 및 쿼리 저장소 사용을 참조하세요.

쿼리 저장소를 사용한 권장되는 데이터베이스 업그레이드 워크플로

이 업그레이드 제어는 자동 튜닝이 도입된 SQL Server 2017(14.x)을 통해 더욱 개선되었으며 위의 권장 워크플로에서 마지막 단계를 자동화할 수 있습니다.

SQL Server Management Studio v18부터 쿼리 저장소 사용 시나리오의 SQL Server의 최신 버전으로 업그레이드하는 동안 성능 안정성 유지 섹션에 설명된 대로 새로운 QTA(쿼리 튜닝 길잡이) 기능은 최신 SQL Server 버전으로 업그레이드하는 동안 성능 안정성을 유지하기 위해 사용자에게 권장되는 워크플로를 안내합니다. 그러나 QTA는 권장 워크플로의 마지막 단계에 나와 있는 대로 이전에 알려진 좋은 계획으로 롤백되지 않습니다. 대신 QTA는 쿼리 저장소 회귀된 쿼리 뷰에 있는 회귀를 추적하고, 적용 가능한 최적화 프로그램 모델 변형의 가능한 순열을 반복하여 더 나은 새 계획을 생성할 수 있도록 합니다.

Important

QTA는 사용자 워크로드를 생성하지 않습니다. 애플리케이션에서 사용되지 않는 환경에서 QTA를 실행하는 경우 대상 SQL Server 데이터베이스 엔진에서 다른 방법으로 대표 테스트 워크로드를 계속 실행할 수 있는지 확인합니다.

쿼리 튜닝 도우미 워크플로

QTA의 시작점에서는 이전 버전의 SQL Server에서 데이터베이스가 최신 버전의 SQL Server 데이터베이스 엔진으로 이동(CREATE DATABASE ... FOR ATTACH 또는 RESTORE를 통해)되었고, 업그레이드 전 데이터베이스 호환성 수준이 즉시 변경되지 않은 것으로 간주합니다. QTA가 다음 단계를 안내합니다.

사용자가 설정한 워크로드 기간(일)에 대한 권장된 설정에 따라 쿼리 저장소를 구성합니다. 일반적인 비즈니스 주기와 일치하는 워크로드 기간을 생각해 보세요.
필요한 워크로드 시작을 요청하여 해당 쿼리 저장소가 워크로드 데이터의 기준을 수집할 수 있도록 합니다(아직 사용할 수 없는 경우).
사용자가 선택한 대상 데이터베이스 호환성 수준으로 업그레이드합니다.
비교 및 회귀 검색을 위해 두 번째 워크로드 데이터 전달을 수집할 것을 요청합니다.
쿼리 저장소 회귀된 쿼리 보기에 따라 검색된 모든 회귀를 반복하고, 적용 가능한 최적화 도구 모델 변형에 대한 가능한 영구 구성에서 런타임 통계를 수집하여 검사하고, 결과를 측정합니다.
측정된 개선 사항을 보고하고 필요에 따라 계획 지침을 사용하여 변경 내용을 유지할 수 있도록 허용합니다.

데이터베이스 연결에 대한 자세한 내용은 데이터베이스 분리 및 연결을 참조하세요.

아래에서 QTA가 위에 나온 쿼리 저장소를 사용하여 호환성 수준을 업그레이드하기 위해 권장되는 워크플로의 마지막 단계만 변경하는 방법을 참조하세요. QTA는 현재 비효율적인 실행 계획과 마지막으로 알려진 실행 계획 중에서 선택할 수 있는 옵션을 갖는 대신, 선택한 회귀 쿼리에 특정한 튜닝 옵션을 제시하여 튜닝된 실행 계획을 사용하여 새로운 개선된 상태를 만듭니다.

QTA를 사용한 권장되는 데이터베이스 업그레이드 워크플로

QTA 튜닝 내부 검색 공간

QTA는 쿼리 저장소에서 실행할 수 있는 SELECT 쿼리만 대상으로 지정합니다. 컴파일된 매개 변수를 알고 있는 경우 매개 변수가 있는 쿼리가 적합합니다. 임시 테이블 또는 테이블 변수와 같은 런타임 구문에 의존하는 쿼리는 현재 적합하지 않습니다.

QTA는 CE(카디널리티예측 도구) 버전 변경으로 인한 쿼리 회귀의 알려진 가능한 패턴을 대상으로 합니다. 예를 들어, 데이터베이스를 SQL Server 2012(11.x) 및 데이터베이스 호환성 수준 110에서 SQL Server 2017(14.x) 및 데이터베이스 호환성 수준 140으로 업그레이드하는 경우 특별히 SQL Server 2012(11.x)에 존재하는 CE 버전(CE 70)으로 작업하도록 설계되었으므로 일부 쿼리가 회귀될 수 있습니다. 그렇다고 CE 140에서 CE 70으로 되돌리는 것이 유일한 옵션은 아닙니다. 최신 버전의 특정 변경 사항만 회귀에 도입하는 경우 쿼리가 새로운 CE 버전의 모든 개선 사항을 사용하는 동시에, 특정 쿼리에 더 잘 작동했던 이전 CE 버전의 관련 부분만 사용한다는 점을 의미할 수 있습니다. 또한 회귀하지 않은 워크로드의 다른 쿼리에서 최신 CE 개선의 이점을 누릴 수 있습니다.

QTA에서 검색하는 CE 패턴은 다음과 같습니다.

독립성과 상관 관계: 독립성 가정이 특정 쿼리에 대해 더 나은 예상치를 제공하는 경우 쿼리 힌트 USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES')로 인해 상관 관계를 설명하는 필터에 AND 조건자를 예상할 때 SQL Server가 최소 선택을 사용하여 실행 계획을 생성합니다. 자세한 내용은 USE HINT 쿼리 힌트 및 CE 버전을 참조하세요.
단순 포함과 기본 포함: 다른 조인 포함이 특정 쿼리에 대해 더 나은 예상치를 제공하는 경우 쿼리 힌트 USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')로 인해 SQL Server가 기본값인 기본 포함 가정 대신 단순 포함 가정을 사용하여 실행 계획을 생성합니다. 자세한 내용은 USE HINT 쿼리 힌트 및 CE 버전을 참조하세요.
행 100개와 행 1개의 MSTVF(다중 명령문 테이블 반환 함수) 고정 카디널리티 추측: 100개 행의 TVF에 대한 기본 고정 예상치가 1개 행(SQL Server 2008 R2(10.50.x) 및 이전 버전의 쿼리 최적화 CE 모델 아래의 기본값에 해당)의 TVF에 대한 고정 예상치를 사용하는 것에 비해 더 효율적인 계획으로 이어지지 않는 경우 실행 계획을 생성하는 데 쿼리 힌트 QUERYTRACEON 9488이 사용됩니다. MSTVF에 대한 자세한 내용은 사용자 정의 함수 만들기(데이터베이스 엔진)를 참조하세요.

참고 항목

마지막 수단으로, 좁은 범위의 힌트가 적합한 쿼리 패턴에 대해 충분한 결과를 산출하지 못하는 경우 쿼리 힌트 USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')을 사용하여 실행 계획을 생성하는 방법으로 CE 70의 전체 사용도 고려됩니다.

Important

힌트는 향후 SQL Server 업데이트에서 해결될 수 있는 특정 동작을 강제로 적용합니다. 다른 옵션이 없는 경우에만 힌트를 적용하고 모든 새 업그레이드를 통해 힌트 코드를 다시 방문하도록 계획하는 것이 좋습니다. 동작을 강제로 적용하면 최신 버전의 SQL Server에 도입된 향상된 기능을 활용하지 못하도록 워크로드를 배제할 수 있습니다.

데이터베이스 업그레이드를 위해 쿼리 튜닝 도우미 시작

QTA는 세션이 처음으로 만들어지는 사용자 데이터베이스의 msqta 스키마에 세션 상태를 저장하는 세션 기반 기능입니다. 시간이 지남에 따라 단일 데이터베이스에 여러 튜닝 세션을 만들 수 있지만 지정된 데이터베이스에 대해 하나의 활성 세션만 존재할 수 있습니다.

데이터베이스 업그레이드 세션 만들기

SQL Server Management Studio에서 개체 탐색기를 열고 데이터베이스 엔진에 연결합니다.
데이터베이스 호환성 수준을 업그레이드하려는 데이터베이스의 경우 데이터베이스 이름을 마우스 오른쪽 단추로 선택하고, 작업을 선택하고, 데이터베이스 업그레이드를 선택한 후, 새 데이터베이스 업그레이드 세션을 선택합니다.
QTA 마법사 창에서 세션을 구성하려면 다음 두 단계를 수행해야 합니다.
1. 설치 창에서 분석 및 튜닝할 워크로드 데이터의 전체 비즈니스 주기에 해당하는 항목을 캡처하도록 쿼리 저장소를 구성합니다.
  - 예상 워크로드 기간을 일 단위로 입력합니다(최소 1일). 이는 전체 기준을 잠정적으로 수집할 수 있도록 권장되는 쿼리 저장소 설정을 제안하는 데 사용됩니다. 데이터베이스 호환성 수준을 변경한 후 발견된 모든 회귀된 쿼리가 분석될 수 있도록 하려면 적합한 기준을 캡처하는 것이 중요합니다.
  - QTA 워크플로가 완료된 후 사용자 데이터베이스에 요구되는 대상 데이터베이스 호환성 수준을 설정합니다. 완료되면 다음을 선택합니다.
2. 설정 창의 두 열에는 대상 데이터베이스의 쿼리 저장소 현재 상태와 권장 설정이 표시됩니다.
  - 권장 설정은 기본적으로 선택되어 있지만, 현재 열 위에 있는 라디오 단추를 선택하면 현재 설정이 수락되며, 현재 쿼리 저장소 구성을 미세 조정할 수도 있습니다.
  - 제안된 부실 쿼리 임계값 설정은 예상 워크로드 기간의 두 배(일)입니다. 쿼리 저장소가 기준 워크로드 및 데이터베이스 업그레이드 후 워크로드에 대한 정보를 유지해야 하기 때문입니다. 완료되면 다음을 선택합니다.
  Important
  
  제안된 최대 크기는 짧은 시간 워크로드에 적합할 수 있는 임의의 값입니다. 하지만 매우 집약적인 워크로드에 대한 기준 및 데이터베이스 후 업그레이드 워크로드에 대한 정보를 보유하는 것으로 부족할 수 있습니다. 즉, 다양한 계획이 생성되는 등의 경우가 발생할 수 있습니다. 이러한 경우를 예상하는 경우 적절한 더 높은 값을 입력합니다.
튜닝 창에서는 세션 구성을 종료하고 다음 단계에 따라 세션을 열고 진행하도록 지시합니다. 완료되면 종료를 선택합니다.

데이터베이스 업그레이드 워크플로 실행

데이터베이스 호환성 수준을 업그레이드하려는 데이터베이스의 경우 데이터베이스 이름을 마우스 오른쪽 단추로 클릭하고, 작업을 선택하고, 데이터베이스 업그레이드를 선택한 후, 세션 모니터링을 선택합니다.
세션 관리 페이지에는 범위 내 데이터베이스에 대한 현재 및 이전 세션이 나열됩니다. 원하는 세션을 선택하고 세부 정보를 선택합니다.

참고 항목

현재 세션이 없는 경우 새로 고침 단추를 선택합니다.

이 목록에는 다음 정보가 포함됩니다.
- 세션 ID
- 세션 이름: 데이터베이스 이름, 세션을 만든 날짜 및 시간으로 구성된 시스템에서 생성된 이름입니다.
- 상태: 세션의 상태입니다(활성 또는 닫힘).
- 설명: 사용자가 선택한 대상 데이터베이스 호환성 수준 및 비즈니스 주기 워크로드에 대한 일 수로 구성된 시스템에서 생성된 항목입니다.
- 시작 시간: 세션이 만들어진 날짜와 시간입니다.
참고 항목

세션 삭제는 선택한 세션에 대해 저장된 데이터를 삭제합니다. 하지만 닫힌 세션을 삭제해도 이전에 배포된 계획 지침은 삭제되지 않습니다. 계획 지침을 배포한 세션을 삭제하는 경우 QTA를 사용하여 롤백할 수 없습니다. 대신 sys.plan_guides 시스템 테이블을 사용하여 계획 지침을 검색하고 sp_control_plan_guide를 사용하여 수동으로 삭제합니다.
새 세션에 대한 진입점은 데이터 컬렉션 단계입니다.

참고 항목

세션 버튼을 클릭하면 활성 세션을 그대로 두고 세션 관리 페이지로 돌아옵니다.

이 단계에는 세 가지 하위 단계가 있습니다.
1. 기준 데이터 컬렉션은 사용자에게 대표적인 워크로드 주기를 실행하도록 요청하므로 쿼리 저장소 기준을 수집할 수 있습니다. 워크로드가 완료되면 워크로드 실행 완료를 선택하고 다음을 선택합니다.
  
  참고 항목
  
  워크로드가 실행되는 동안 QTA 창을 닫을 수 있습니다. 나중에 활성 상태로 유지되는 세션으로 돌아가면 중단했던 동일한 단계에서 다시 시작됩니다.
2. 업그레이드 데이터베이스는 데이터베이스 호환성 수준을 원하는 대상으로 업그레이드할 수 있는 권한을 묻는 메시지를 표시합니다. 다음 하위 단계를 진행하려면 예를 선택합니다.
  
  다음 페이지에서는 데이터베이스 호환성 수준이 성공적으로 업그레이드되었는지 확인합니다.
3. 데이터 수집 관찰에서는 해당 쿼리 저장소가 최적화 기회를 검색하는 데 사용할 비교 기준을 수집할 수 있도록 대표적인 워크로드 주기를 다시 실행할 것을 사용자에게 요청합니다. 워크로드가 실행되면 새로 고침 단추를 사용하여 발견된 경우 회귀된 쿼리의 목록을 계속 업데이트합니다. 표시할 쿼리 값을 변경하여 표시되는 쿼리의 수를 제한합니다. 목록의 순서는 메트릭(기간 또는 CpuTime) 및 집계(평균이 기본값)의 영향을 받습니다. 표시할 쿼리 수도 선택합니다. 워크로드가 완료되면 워크로드 실행 완료를 선택하고 다음을 선택합니다.
  
  이 목록에는 다음 정보가 포함됩니다.
  - 쿼리 ID
  - 쿼리 텍스트: ... 버튼을 선택하여 확장할 수 있는 Transact-SQL 문입니다.
  - 실행: 전체 워크로드 컬렉션에 대한 해당 쿼리의 실행 수를 표시합니다.
  - 기준 메트릭: 데이터베이스 호환성 업그레이드 전에 데이터 수집을 위한 ms 단위의 선택한 메트릭(기간 또는 CpuTime)입니다.
  - 관찰된 메트릭: 데이터베이스 호환성 업그레이드 후에 데이터 수집을 위한 ms 단위의 선택한 메트릭(기간 또는 CpuTime)입니다.
  - 변경율: 데이터베이스 호환성 업그레이드 상태 이전과 이후 간에 선택한 메트릭의 변경율입니다. 음수는 쿼리에 대한 측정된 회귀의 양을 나타냅니다.
  - 튜닝 가능: 쿼리가 실험에 적합한지 여부에 따라 True 또는 False입니다.
분석 보기를 사용하면 실험할 쿼리를 선택하고 최적화 기회를 찾을 수 있습니다. 표시할 쿼리 값은 실험에 적합한 쿼리의 범위가 됩니다. 원하는 쿼리가 선택되면 다음을 선택하여 실험을 시작합니다.

참고 항목

튜닝 가능 = False인 쿼리는 실험용으로 선택할 수 없습니다.

Important

프롬프트는 QTA가 실험 단계로 이동하면 보기 분석 페이지로 돌아갈 수 없음을 알려 줍니다.
실험 단계로 이동하기 전에 적격 쿼리를 모두 선택하지 않으면 나중에 새 세션을 만들고 워크플로를 반복해야 합니다. 이렇게 하려면 데이터베이스 호환성 수준을 이전 값으로 다시 설정해야 합니다.
결과 보기에서는 제안된 최적화를 계획 지침으로 배포할 쿼리를 선택할 수 있습니다.

이 목록에는 다음 정보가 포함됩니다.
- 쿼리 ID
- 쿼리 텍스트: ... 버튼을 선택하여 확장할 수 있는 Transact-SQL 문입니다.
- 상태: 쿼리에 대한 현재 실험 상태를 표시합니다.
- 기준 메트릭: 데이터베이스 호환성 업그레이드 후 회귀된 쿼리를 나타내는 2단계 하위 3 단계에서 실행된 쿼리의 ms에서 선택한 메트릭(기간 또는 CpuTime)입니다.
- 관찰된 메트릭: 충분한 제안된 최적화를 위해 실험 후 쿼리에 대해 ms에서 선택한 메트릭(기간 또는 CpuTime)입니다.
- 변경율: 실험 단계 전후 사이의 선택한 메트릭에 대한 변경률을 백분율로 표시한 것입니다. 제안된 최적화를 사용한 쿼리에 대해 측정된 개선 사항의 양을 표시합니다.
- 쿼리 옵션: 쿼리 실행 메트릭을 개선하는 제안된 힌트로 연결됩니다.
- 배포 가능: 제안된 쿼리 최적화를 계획 지침으로 배포할 수 있는지 여부에 따라 True 또는 False입니다.
검증은 이 세션에 대해 이전에 선택한 쿼리의 배포 상태를 보여 줍니다. 이 페이지의 목록은 배포 가능 열을 롤백 가능으로 변경하여 이전 페이지와는 다릅니다. 이 열은 배포된 쿼리 최적화를 롤백할 수 있는지 여부와 계획 가이드를 제거할 수 있는지에 따라 True 또는 False일 수 있습니다.

나중에 제안된 최적화에서 롤백해야 하는 경우 관련 쿼리를 선택하고 롤백을 선택합니다. 해당 쿼리 계획 지침이 제거되고 롤백된 쿼리를 제거하기 위해 목록이 업데이트됩니다. 아래 그림에서는 해당 쿼리 8이 제거되었습니다.

참고 항목

닫힌 세션을 삭제해도 이전에 배포된 계획 지침은 삭제되지 않습니다. 계획 지침을 배포한 세션을 삭제하는 경우 QTA를 사용하여 롤백할 수 없습니다. 대신 sys.plan_guides 시스템 테이블을 사용하여 계획 지침을 검색하고 sp_control_plan_guide를 사용하여 수동으로 삭제합니다.