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

적용 대상: SQL Server 2016(13.x) 이상 버전 Azure SQL DatabaseAzure 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 경우에 변경됩니다. SQL Server 2014(12.x)에 도입된 쿼리 최적화 프로그램 변경 내용에 대한 자세한 내용은 카디널리티 추정기를 참조 하세요. 호환성 수준 및 업그레이드에 미치는 영향에 대한 자세한 내용은 호환성 수준 및 데이터베이스 엔진 업그레이드를 참조하세요.

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

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

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

Important

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

쿼리 튜닝 도우미 워크플로

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

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

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

아래에서 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 70)에 있는 CE 버전과 작동하도록 특별히 설계되었기 때문에 회귀할 수 있습니다. 그렇다고 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 버전을 참조하세요.
• MSTVF(다중 문 테이블 반환 함수)는 100개 행과 비교한 카드진수 추측을 수정했습니다. 행 1개: 100개 행의 TVF에 대한 기본 고정 추정이 1행의 TVF에 대해 고정된 추정을 사용하는 것보다 더 효율적인 계획이 되지 않는 경우(SQL Server 2008 R2(10.50.x) 및 이전 버전의 쿼리 최적화 프로그램 CE 모델에서 기본값에 해당) 그런 다음 쿼리 힌트 QUERYTRACEON 9488 를 사용하여 실행 계획을 생성합니다. MSTVF에 대한 자세한 내용은 사용자 정의 함수 만들기(데이터베이스 엔진)를 참조하세요.

참고 항목

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

Important

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

데이터베이스 업그레이드에 대한 쿼리 튜닝 도우미 시작

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

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

1. SQL Server Management Studio에서 개체 탐색기 열고 데이터베이스 엔진 연결합니다.

2. 데이터베이스 호환성 수준을 업그레이드하려는 데이터베이스의 경우 데이터베이스 이름을 마우스 오른쪽 단추로 클릭하고 작업을 선택하고 데이터베이스 업그레이드를 선택한 다음 새 데이터베이스 업그레이드 세션을 선택합니다.

3. QTA 마법사 창에서 세션을 구성하려면 다음 두 단계가 필요합니다.

   1. 설치 창에서 분석 및 튜닝할 워크로드 데이터의 전체 비즈니스 주기에 해당하는 항목을 캡처하도록 쿼리 저장소 구성합니다.

      • 예상 워크로드 기간을 일 단위로 입력합니다(최소 1일). 이는 전체 기준을 잠정적으로 수집할 수 있도록 권장되는 쿼리 저장소 설정을 제안하는 데 사용됩니다. 데이터베이스 호환성 수준을 변경한 후 발견된 모든 회귀된 쿼리가 분석될 수 있도록 하려면 적합한 기준을 캡처하는 것이 중요합니다.
      • QTA 워크플로가 완료된 후 사용자 데이터베이스가 있어야 하는 대상 데이터베이스 호환성 수준을 설정합니다. 완료되면 다음을 선택합니다.

      

   2. 설정 창에서 두 개의 열에는 대상 데이터베이스의 쿼리 저장소 현재 상태와 권장 설정이 표시됩니다.

      • 권장 설정은 기본적으로 선택되어 있지만, 현재 열 위에 있는 라디오 단추를 선택하면 현재 설정이 수락되며, 현재 쿼리 저장소 구성을 미세 조정할 수도 있습니다.
      • 제안된 부실 쿼리 임계값 설정은 예상 워크로드 기간(일)의 두 배입니다. 쿼리 저장소가 기준 워크로드 및 데이터베이스 업그레이드 후 워크로드에 대한 정보를 유지해야 하기 때문입니다. 완료되면 다음을 선택합니다.

      

      Important

      제안된 최대 크기 는 짧은 시간 워크로드에 적합할 수 있는 임의의 값입니다. 그러나 매우 집약적인 워크로드에 대한 기준 및 데이터베이스 후 업그레이드 워크로드에 대한 정보를 보유하는 것은 부족할 수 있습니다. 즉, 다양한 계획이 생성될 수 있는 경우입니다. 이러한 상황이 예상되는 경우에는 적절한 더 높은 값을 입력합니다.

4. 튜닝 창은 세션 구성을 종료하고 다음 단계에 따라 세션을 열고 진행하도록 지시합니다. 완료되면 마침을 선택합니다.

   

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

1. 데이터베이스 호환성 수준을 업그레이드하려는 데이터베이스의 경우 데이터베이스 이름을 마우스 오른쪽 단추로 클릭하고 작업을 선택하고 데이터베이스 업그레이드를 선택한 다음 세션 모니터링을 선택합니다.

2. 세션 관리 페이지에는 범위 내 데이터베이스에 대한 현재 및 이전 세션이 나열됩니다. 원하는 세션을 선택하고 세부 정보를 선택합니다.

   참고 항목

   현재 세션이 없으면 새로 고침 단추를 선택합니다.

   목록에는 다음 정보가 포함됩니다.

   • 세션 ID
   • 세션 이름: 데이터베이스 이름, 날짜 및 세션 생성 시간으로 구성된 시스템 생성 이름입니다.
   • 상태: 세션의 상태입니다(활성 또는 닫힘).
   • 설명: 사용자가 선택한 대상 데이터베이스 호환성 수준 및 비즈니스 주기 워크로드의 일 수로 구성된 시스템 생성
   • 시작 시간: 세션이 만들어진 날짜와 시간입니다.

   

   참고 항목

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

3. 새 세션에 대한 진입점은 데이터 컬렉션 단계입니다.

   참고 항목

   세션 단추가 세션 관리 페이지로 돌아와 활성 세션을 있는 그대로 둡니다.

   이 단계에는 세 가지 하위 단계가 있습니다.

   1. 기준 데이터 수집은 사용자에게 대표적인 워크로드 주기를 실행하도록 요청하므로 쿼리 저장소 기준을 수집할 수 있습니다. 워크로드가 완료되면 워크로드 실행 완료를 선택하고 다음을 선택합니다.

      참고 항목

      워크로드가 실행되는 동안 QTA 창을 닫을 수 있습니다. 나중에 활성 상태로 다시 기본 세션으로 돌아가면 중단된 동일한 단계에서 다시 시작됩니다.

      

   2. 업그레이드 데이터베이스 는 데이터베이스 호환성 수준을 원하는 대상으로 업그레이드할 수 있는 권한을 묻는 메시지를 표시합니다. 다음 하위 단계로 진행하려면 [예]를 선택합니다.

      

      다음 페이지에서는 데이터베이스 호환성 수준이 성공적으로 업그레이드되었는지 확인합니다.

      

   3. 데이터 수집 관찰에서는 해당 쿼리 저장소가 최적화 기회를 검색하는 데 사용할 비교 기준을 수집할 수 있도록 대표적인 워크로드 주기를 다시 실행할 것을 사용자에게 요청합니다. 워크로드가 실행되면 새로 고침 단추를 사용하여 발견된 경우 회귀된 쿼리의 목록을 계속 업데이트합니다. 표시할 쿼리 값을 변경하여 표시되는 쿼리의 수를 제한합니다. 목록의 순서는 메트릭(기간 또는 CpuTime) 및 집계(평균이 기본값)의 영향을 받습니다. 표시할 쿼리 수도 선택합니다. 워크로드가 완료되면 워크로드 실행 완료를 선택하고 다음을 선택합니다.

      

      목록에는 다음 정보가 포함됩니다.

      • 쿼리 ID
      • 쿼리 텍스트: ... 단추를 선택하여 확장할 수 있는 Transact-SQL 문입니다.
      • 실행: 전체 워크로드 컬렉션에 대한 해당 쿼리의 실행 수를 표시합니다.
      • 기준 메트릭: 데이터베이스 호환성 업그레이드 전에 초기 계획 데이터 컬렉션에 대해 ms에서 선택한 메트릭(기간 또는 CpuTime)입니다.
      • 관찰된 메트릭: 데이터베이스 호환성 업그레이드 후 데이터 수집에 대해 ms에서 선택한 메트릭(기간 또는 CpuTime)입니다.
      • % 변경: 데이터베이스 호환성 업그레이드 상태 이전과 이후 간에 선택한 메트릭에 대한 변경율입니다. 음수는 쿼리에 대한 측정된 회귀의 양을 나타냅니다.
      • 튜닝 가능: 쿼리가 실험에 적합한지 여부에 따라 True 또는 False입니다.

4. 분석 보기를 사용하면 실험할 쿼리를 선택하고 최적화 기회를 찾을 수 있습니다. 표시할 쿼리 값은 실험에 적합한 쿼리의 범위가 됩니다. 원하는 쿼리가 검사 다음을 선택하여 실험을 시작합니다.

   참고 항목

   튜닝 가능 = False인 쿼리는 실험을 위해 선택할 수 없습니다.

   Important

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

   

5. 뷰 결과 에서는 제안된 최적화를 계획 지침으로 배포할 쿼리를 선택할 수 있습니다.

   목록에는 다음 정보가 포함됩니다.

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

   

6. 확인은 이 세션에 대해 이전에 선택한 쿼리의 배포 상태 보여 줍니다. 이 페이지의 목록은 배포 가능 열을 롤백 가능으로 변경하여 이전 페이지와는 다릅니다. 이 열은 배포된 쿼리 최적화를 롤백할 수 있는지 여부와 계획 가이드를 제거할 수 있는지에 따라 True 또는 False 일 수 있습니다.

   

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

   

   참고 항목

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

사용 권한

db_owner 역할의 멤버 자격이 필요합니다.

참고 항목

• 호환성 수준 및 데이터베이스 엔진 업그레이드
• 성능 모니터링 및 튜닝 도구
• 쿼리 저장소 사용하여 성능 모니터링
• 데이터베이스 호환성 모드 변경 및 쿼리 저장소 사용
• 추적 플래그
• USE HINT 쿼리 힌트
• 카디널리티 추정기
• 자동 튜닝
• SQL Server 쿼리 튜닝 도우미 사용