자동 튜닝 설명

  • 7분

자동 튜닝은 워크로드에 대해 지속적으로 학습하고 잠재적인 문제 및 개선 사항을 식별하는 모니터링 및 분석 기능입니다.

자동 튜닝 권장 사항은 쿼리 저장소에서 수집된 데이터를 기반으로 합니다. 기본적으로 통계 업데이트를 초래하는 데이터 변경, 인덱스 수정 또는 스키마 변경 때문에 실행 계획은 시간이 지남에 따라 변화합니다. 이러한 변화로 인해 실행 계획이 지정된 쿼리의 요구 사항을 더 이상 충족하지 않으므로 쿼리가 제대로 수행되지 않을 수 있습니다.

또한 자동 튜닝을 사용하면 성능 메트릭을 기반으로 기계 학습 서비스를 수집하고 적용하여 권장되는 향상 기능을 제공하거나 자동으로 수정할 수도 있습니다.

온-프레미스 또는 클라우드에서 자동 튜닝 기능을 사용하면 쿼리 실행 계획 회귀로 인한 문제를 파악할 수 있습니다. 또한 Azure SQL Database에는 인덱스 튜닝을 통해 쿼리 성능을 향상시킬 수 있습니다. Azure SQL Database 자동 튜닝을 사용하면 쿼리 성능을 향상시키기 위해 데이터베이스에 추가하거나 제거해야 하는 인덱스를 파악할 수 있습니다.

자동 플랜 수정

쿼리 저장소 데이터의 도움으로 데이터베이스 엔진은 쿼리 실행 계획이 성능에서 회귀된 시기를 결정할 수 있습니다. 사용자 인터페이스를 통해 성능이 저하된 계획을 수동으로 식별할 수 있지만 쿼리 저장소에는 자동으로 알리는 옵션도 있습니다.

회귀된 계획 수정에 대한 쿼리 저장소 보기의 스크린샷

위의 예에서는 계획 ID 1에 체크 표시가 있음을 볼 수 있습니다. 이는 계획이 강제로 적용되었음을 나타냅니다. 이 기능을 사용하도록 설정하면 데이터베이스 엔진은 다음과 같은 경우에 권장되는 쿼리 실행 계획을 자동으로 강제 적용합니다.

  • 이전 계획에서 권장되는 계획보다 오류율이 더 높았습니다.
  • 예상 CPU 증가가 10초보다 컸습니다.
  • 강제 적용 계획은 이전 계획보다 더 잘 수행되었습니다.

이 계획은 쿼리를 15번 실행한 후 마지막으로 알려진 우수한 계획으로 되돌아갑니다.

계획 강제 적용을 자동으로 수행하면 데이터베이스 엔진에서 마지막으로 알려진 우수한 계획을 적용하고 쿼리 실행 계획 성능도 계속 모니터링합니다. 강제 적용 계획의 성능이 이전 계획보다 낫지 않으면 강제 적용되지 않고 새 계획을 강제로 컴파일합니다. 강제 계획이 이전의 잘못된 계획을 계속 능가하는 경우 다시 컴파일할 때까지 해당 강제 계획이 그대로 적용됩니다.

다음과 같이 T-SQL 쿼리를 통해 자동 계획 수정을 사용으로 설정할 수 있습니다. 명령에 성공하려면 쿼리 저장소를 사용으로 설정해야 하며 읽기-쓰기 모드에 있어야 합니다. 이러한 두 조건 중 하나가 충족되지 않으면 ALTER 문이 실패합니다.

ALTER DATABASE [WideWorldImporters] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

SQL Server 2017 이상에서 사용 가능하며 Azure SQL Database 솔루션에서도 사용할 수 있는 DMV(동적 관리 뷰), sys.dm_db_tuning_recommendations를 통해 자동 튜닝 권장 사항을 검토할 수 있습니다. 이 DMV에는 권장 사항을 제공하는 이유, 권장 사항의 유형, 권장 사항 상태 등의 정보가 제공됩니다. 데이터베이스에 대해 자동 튜닝이 사용하도록 설정되어 있는지 확인하려면 보기 sys.database_automatic_tuning_options을 확인합니다.

자동 인덱스 관리

Azure SQL Database에서 자동 인덱스 튜닝을 수행할 수 있습니다. 데이터베이스는 시간이 경과함에 따라 기존 워크로드에 관해 학습하고 향상된 성능을 위해 인덱스를 추가하거나 제거하는 데 관한 권장 사항을 제공합니다. 향상된 쿼리 계획의 강제 적용과 마찬가지로, 다음과 같이 기존 인덱스 성능에 따라 자동 인덱스 생성 또는 제거를 허용하도록 데이터베이스를 구성할 수 있습니다.

Azure SQL Database에 대한 자동 조정 옵션의 스크린샷

사용으로 설정하면 성능 권장 사항 페이지에서 쿼리 성능에 따라 만들거나 삭제할 수 있는 인덱스를 식별합니다. 이 기능은 온-프레미스 데이터베이스에는 사용할 수 없으며 Azure SQL Database에만 사용할 수 있습니다.

또는 다음 쿼리를 사용하여 데이터베이스에서 활성화된 자동 조정 기능을 확인합니다.

SELECT name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options

새 인덱스를 만드는 데는 리소스가 소비될 수 있으며 워크로드에 부정적인 영향을 미치지 않으려면 인덱스를 만드는 시기가 중요합니다.

Azure SQL Database는 성능 저하 문제를 방지하기 위해 새로운 인덱스를 구현하는 데 필요한 리소스를 모니터링합니다. 튜닝 작업은 사용 가능한 리소스를 사용할 수 있을 때까지 연기됩니다(예: 기존 워크로드에 리소스가 필요하고 인덱스를 만드는 데 사용할 수 없는 경우).

모니터링을 통해 수행된 모든 작업이 성능에 해를 끼치지 않도록 합니다. 인덱스를 삭제한 결과 쿼리 성능이 현저하게 저하되면 최근에 삭제된 인덱스가 자동으로 다시 생성됩니다.