자동 조정

  • 아티클

적용 대상: SQL Server 2017(14.x) 이상 Azure SQL DatabaseAzure SQL Managed Instance

자동 조정은 잠재적인 쿼리 성능 문제에 대한 인사이트를 제공하고, 솔루션을 권장하며, 식별된 문제를 자동으로 해결하는 데이터베이스 기능입니다.

SQL Server 2017(14.x)에 도입된 자동 조정은 잠재적인 성능 문제를 검색할 때마다 알려주고, 정정 작업을 적용하거나 데이터베이스 엔진에서 성능 문제를 자동으로 해결할 수 있도록 합니다. SQL Server 자동 조정은 쿼리 실행 계획 선택 회귀로 인한 성능 문제를 식별하고 해결합니다. 또한 Azure SQL Database 자동 조정은 필요한 인덱스를 만들고 사용하지 않는 인덱스를 삭제합니다. 쿼리 실행 계획에 관한 자세한 내용은 실행 계획을 참조하세요.

SQL Server 데이터베이스 엔진은 데이터베이스에서 실행되는 쿼리를 모니터링하며, 워크로드의 성능을 자동으로 향상시킬 수 있습니다. 데이터베이스 엔진에는 데이터베이스를 워크로드에 동적으로 적용하여 자동으로 조정하고 쿼리의 성능을 향상시킬 수 있는 기본 제공 인텔리전스 메커니즘이 있습니다. 여기서 사용할 수 있는 자동 조정 기능은 다음의 두 가지가 있습니다.

  • 자동 계획 수정은 매개 변수 민감도 또는 매개 변수 스니핑 문제와 같이 문제가 있는 쿼리 실행 계획을 식별하고 회귀가 발생하기 전에 마지막으로 알려진 좋은 계획을 강제로 적용하여 쿼리 실행 계획 관련 성능 문제를 해결합니다. 적용 대상: SQL Server(SQL Server 2017(14.x)부터) 및 Azure SQL Database 및 Azure SQL Managed Instance]

  • 자동 인덱스 관리는 데이터베이스에 추가되어야 하는 인덱스와 제거되어야 하는 인덱스를 식별합니다. 적용 대상: Azure SQL Database

자동 튜닝을 사용하는 이유는 무엇인가요?

클래식 데이터베이스 관리의 세 가지 주요 작업은 워크로드를 모니터링하고, 중요한 Transact-SQL 쿼리를 식별하며, 성능을 향상시키기 위해 추가해야 하는 인덱스 또는 성능을 향상시키기 위해 거의 사용되지 않고 제거할 수 있는 인덱스를 식별하는 것입니다. SQL Server 데이터베이스 엔진은 모니터링해야 하는 쿼리 및 인덱스에 대한 자세한 정보를 제공합니다. 그러나 지속적으로 데이터베이스를 모니터링하는 것은 특히 많은 데이터베이스를 처리할 때 힘들고 지루한 작업입니다. 수많은 데이터베이스를 효율적으로 관리하는 것은 불가능할 수 있습니다. 수동으로 데이터베이스를 모니터링하고 튜닝하는 대신 자동 조정 기능을 사용하여 데이터베이스 엔진에 대한 모니터링 및 튜닝 작업을 위임하는 것을 고려할 수 있습니다.

자동 튜닝은 어떻게 작동하나요?

자동 조정은 지속적으로 워크로드의 특징을 학습하고 잠재적인 문제 및 향상된 기능을 식별하는 지속적인 성능 모니터링 및 분석 프로세스입니다.

Automatic tuning process.

이 프로세스를 통해 해당 데이터베이스는 워크로드의 성능을 향상시킬 수 있는 인덱스와 계획은 물론, 워크로드에 영향을 주는 인덱스도 찾아 워크로드에 동적으로 적응할 수 있습니다. 이러한 결과에 따라 자동 조정은 워크로드의 성능을 향상시키는 튜닝 작업을 적용합니다. 또한 자동 조정은 변경 내용을 구현한 후 데이터베이스의 성능을 지속적으로 모니터링하여 워크로드의 성능을 향상시킵니다. 성능을 향상시키지 않은 모든 작업은 자동으로 되돌려집니다. 이 확인 프로세스는 자동 조정에서 수행한 변경 사항이 워크로드의 전체 성능을 저하시키지 않도록 보장하는 주요 기능입니다.

자동 플랜 수정

자동 계획 수정은 실행 계획 선택 회귀를 식별하고 마지막으로 알려진 좋은 계획을 강제로 적용하여 문제를 자동으로 해결하는 자동 조정 기능입니다. 쿼리 실행 계획과 쿼리 최적화 프로그램에 관한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.

Important

자동 계획 수정은 워크로드 추적을 위해 데이터베이스에서 사용하도록 설정되는 쿼리 저장소에 따라 달라집니다.

실행 계획 선택 회귀란 무엇인가요?

SQL Server 데이터베이스 엔진은 여러 가지 실행 계획을 사용하여 Transact-SQL 쿼리를 실행할 수 있습니다. 쿼리 계획은 통계, 인덱스 및 기타 요인에 따라 달라집니다. Transact-SQL 쿼리를 실행하는 데 사용해야 하는 최적의 계획은 이러한 요인의 변경 내용에 따라 시간이 경과하면서 변경될 수 있습니다. 경우에 따라서는 새 계획이 이전 계획보다 좋지 않을 수 있으며 새 계획으로 인해 매개 변수 민감도 또는 매개 변수 스니핑 관련 문제와 같은 성능 회귀 문제가 발생할 수 있습니다.

Query execution plan choice regression.

계획 선택 회귀가 발생했음을 알 때마다 이전의 좋은 계획을 찾아 현재 계획 대신 강제로 사용해야 합니다. 이 작업은 sp_query_store_force_plan 프로시저를 사용하여 수행할 수 있습니다. SQL Server 2017(14.x)의 데이터베이스 엔진은 회귀 계획 및 권장되는 수정 작업에 관한 정보를 제공합니다. 또한 데이터베이스 엔진을 사용하면 이 프로세스를 완전히 자동화할 수 있으며, 데이터베이스 엔진을 통해 계획 변경과 관련된 문제를 해결할 수 있습니다.

Important

워크로드 업그레이드 위험을 자동으로 완화하려면 기준이 캡처된 후 데이터베이스 호환성 수준 업그레이드 범위 내에서 자동 계획 수정을 사용해야 합니다. 이 사용 사례에 관한 자세한 내용은 최신 SQL Server로 업그레이드하는 동안 성능 안정성 유지를 참조하세요.

자동 계획 선택 수정

데이터베이스 엔진은 계획 선택 회귀가 검색될 때마다 마지막으로 알려진 좋은 계획으로 자동 전환할 수 있습니다.

Query execution plan choice correction.

데이터베이스 엔진은 잘못된 계획 대신 사용해야 하는 계획을 포함해 모든 잠재적 계획 선택 회귀를 자동으로 검색합니다. 자동 계획 수정에 의해 강제 적용된 결과 실행 계획은 마지막으로 알려진 좋은 계획과 동일하거나 유사합니다. 결과 계획은 마지막으로 알려진 좋은 계획과 다를 수 있으므로 강제 계획의 성능은 변동할 수 있습니다. 드물긴 하지만 성능 차이는 유의미하며 부정적일 수 있습니다. 이 경우 자동 계획 수정은 대체 계획을 강제로 적용하려는 시도를 자동으로 중지합니다.

데이터베이스 엔진은 회귀가 발생하기 전 마지막으로 알려진 좋은 계획을 적용할 때 강제 계획의 성능을 자동으로 모니터링합니다. 강제 계획이 회귀된 계획보다 낫지 않은 경우 새 계획은 강제로 적용되지 않으며 데이터베이스 엔진은 새 계획을 컴파일합니다. 강제 계획이 회귀된 계획보다 낫다는 것을 데이터베이스 엔진이 확인하면 강제 계획이 유지됩니다. 강제 계획은 다시 컴파일이 발생할 때까지 유지됩니다(예: 다음 통계 업데이트 또는 스키마 변경 시). 계획 강제 적용 및 강제 적용할 수 있는 계획의 유형에 관한 자세한 내용은 계획 강제 적용 제한을 참조하세요.

참고 항목

계획 강제 적용 작업이 확인되기 전에 SQL Server 인스턴스가 다시 시작되면 해당 계획은 자동으로 강제 취소됩니다. 그렇지 않으면 계획 강제 적용은 SQL Server가 다시 시작할 때 유지됩니다.

자동 계획 선택 수정 사용

데이터베이스마다 자동 조정을 활성화하고 일부 계획 변경 회귀가 검색될 때마다 마지막 좋은 계획이 강제 적용되어야 함을 지정할 수 있습니다. 다음 명령을 사용하여 자동 튜닝이 활성화됩니다.

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

이 옵션을 사용하도록 설정하면 데이터베이스 엔진은 예상된 CPU 이득이 10초를 초과하거나 새 계획의 오류 수가 권장된 계획의 오류 수보다 많을 때 모든 권장 사항을 자동으로 강제 적용하며, 강제 적용된 계획이 현재 계획보다 낫다는 것을 확인합니다.

Azure SQL Database 및 Azure SQL Managed Instance에서 자동 튜닝을 사용하도록 설정하려면 Azure Portal을 사용하여 Azure SQL Database에서 자동 튜닝 사용을 참조하세요.

대안 - 수동 계획 선택 수정

자동 튜닝 없이 사용자는 시스템을 주기적으로 모니터링해야 하며 회귀된 쿼리를 찾아야 합니다. 계획이 회귀된 경우 사용자는 이전의 좋은 계획을 찾아 sp_query_store_force_plan 프로시저를 사용하여 현재 계획 대신 강제로 적용해야 합니다. 통계 또는 인덱스 변경으로 인해 이전 계획이 유효하지 않을 수 있으므로 마지막으로 알려진 좋은 계획을 강제로 적용하는 것이 가장 좋습니다. 마지막으로 알려진 좋은 계획을 강제 적용하는 사용자는 강제 계획을 사용하여 실행되는 쿼리의 성능을 모니터링해야 하며, 강제 계획이 예상대로 작동하는지 확인해야 합니다. 모니터링 및 분석 결과에 따라 계획을 강제로 적용하거나 사용자가 쿼리를 다시 작성하는 등 쿼리를 최적화하는 다른 방법을 찾아야 합니다. 데이터베이스 엔진은 최적의 계획을 적용할 수 있어야 하므로 수동 강제 계획을 영구적으로 강제 적용하면 안 됩니다. 사용자 또는 DBA는 결국 sp_query_store_unforce_plan 프로시저를 사용하여 계획을 강제 취소하고 데이터베이스 엔진으로 최적의 계획을 찾을 수 있도록 해야 합니다.

팁

또는 강제 계획이 있는 쿼리 쿼리 저장소 보기를 사용하여 계획을 찾고 강제 취소합니다.

SQL Server는 성능을 모니터링하고 쿼리 저장소 문제를 해결하는 데 필요한 모든 보기 및 절차를 제공합니다.

SQL Server 2016(13.x)에서는 쿼리 저장소 시스템 보기를 사용하여 계획 선택 회귀를 찾을 수 있습니다. SQL Server 2017(14.x)부터 데이터베이스 엔진은 잠재적인 계획 선택 회귀를 검색하고 표시하며, sys.dm_db_tuning_recommendations(Transact-SQL) DMV에 적용해야 하는 권장 작업을 검색하고 표시합니다. DMV는 문제에 관한 정보와 문제의 중요성을 표시하며 식별된 쿼리, 회귀된 계획의 ID, 비교를 위한 기준으로 사용된 계획의 ID 및 문제를 해결하기 위해 실행할 수 있는 Transact-SQL 문 등의 세부 정보도 표시합니다.

type description Datetime score details ...
FORCE_LAST_GOOD_PLAN CPU 시간이 4ms에서 14ms로 변경됨 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN CPU 시간이 37ms에서 84ms로 변경됨 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

이 보기의 일부 열은 다음 목록에 설명되어 있습니다.

  • 권장 작업 FORCE_LAST_GOOD_PLAN의 유형입니다.
  • 이러한 계획 변경이 잠재적인 성능 회귀라고 데이터베이스 엔진이 판단하는 이유를 포함하는 설명입니다.
  • 잠재적인 회귀가 검색된 날짜/시간입니다.
  • 이 권장 사항의 점수입니다.
  • 검색된 계획의 ID, 회귀된 계획의 ID, 문제를 해결하기 위해 강제 적용할 계획의 ID, 문제를 해결하기 위해 적용할 수 있는 Transact-SQL 스크립트 등 제반 문제에 관한 세부 정보입니다. 이 세부 정보는 JSON 형식으로 저장됩니다.

다음 쿼리를 사용하여 문제를 해결하는 스크립트와 예상 이득에 관한 추가 정보를 가져옵니다.

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

결과 집합은 다음과 같습니다.

reason score 스크립트 query_id current plan_id recommended plan_id estimated_gain error_prone
CPU 시간이 3ms에서 46ms로 변경됨 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

이 열 estimated_gain은 권장 계획이 현재 계획 대신 쿼리 실행에 사용될 경우 저장될 예상 시간(초)을 나타냅니다. 게인이 10초를 초과하는 경우 현재 계획 대신 권장 계획을 강제 적용해야 합니다. 현재 계획에서 권장 계획보다 더 많은 오류(예: 시간 제한 또는 중단된 실행)가 있다면 열 error_prone은 값 YES로 설정됩니다. 오류가 발생하기 쉬운 계획은 현재 계획 대신 권장 계획을 강제 적용해야 하는 또 다른 이유입니다.

데이터베이스 엔진은 계획 선택 회귀를 식별하는 데 필요한 모든 정보를 제공하지만 지속적인 모니터링 및 성능 문제 해결은 지루한 프로세스가 될 수 있습니다. 자동 조정을 실행하면 이 프로세스는 훨씬 더 수월해집니다.

참고 항목

sys.dm_db_tuning_recommendations DMV의 데이터는 데이터베이스 엔진을 다시 시작한 후에도 유지되지 않습니다. sqlserver_start_time sys.dm_os_sys_info 열을 사용하여 마지막 데이터베이스 엔진 시작 시간을 찾습니다.

자동 인덱스 관리

Azure SQL Database에서는 Azure SQL Database가 워크로드에 대해 알아보고 데이터가 항상 최적으로 인덱싱되도록 하기 때문에 인덱스 관리가 쉽습니다. 적절한 인덱스 디자인은 작업의 최적의 성능을 위해 중요하고 자동 인덱스 관리는 인덱스를 최적화하는 데 도움이 될 수 있습니다. 자동 인덱스 관리는 잘못 인덱싱된 데이터베이스의 성능 문제를 해결하거나 기존 데이터베이스 스키마에서 인덱스를 유지 관리하고 개선할 수 있습니다. Azure SQL Database의 자동 조정은 다음 작업을 수행합니다.

  • 테이블에서 데이터를 읽는 Transact-SQL 쿼리의 성능을 향상시킬 수 있는 인덱스를 식별합니다.
  • 제거할 수 있는 더 긴 기간 동안 사용되지 않은 인덱스 또는 중복되는 인덱스를 식별합니다. 불필요한 인덱스를 제거하면 테이블의 데이터를 업데이트하는 쿼리의 성능이 향상됩니다.

인덱스 관리가 필요한 이유는 무엇인가요?

인덱스는 테이블에서 데이터를 읽는 일부 쿼리의 속도를 높이지만 데이터를 업데이트하는 쿼리의 속도가 저하될 수 있습니다. 인덱스를 만들어야 하는 시기와 인덱스에 포함해야 하는 열을 신중하게 분석해야 합니다. 일부 인덱스는 일정 시간 후에 필요하지 않을 수 있습니다. 따라서 이점을 가져오지 않는 인덱스를 정기적으로 식별하여 삭제해야 합니다. 사용되지 않는 인덱스를 무시하는 경우 데이터를 읽는 쿼리에 대한 아무런 이점 없이 데이터를 업데이트하는 쿼리의 성능은 저하될 것입니다. 사용하지 않는 인덱스는 추가 업데이트에 불필요한 로깅이 필요하기 때문에 시스템의 전반적인 성능에도 영향을 줍니다.

테이블에서 데이터를 읽고 업데이트에 미치는 영향을 최소화하는 쿼리의 성능을 향상시키는 최적의 인덱스 집합을 찾으려면 연속적이고 복잡한 분석이 필요할 수 있습니다.

Azure SQL Database는 쿼리를 분석하고 현재 워크로드에 최적인 인덱스를 식별하며 제거할 필요가 있는 인덱스를 식별하는 기본 제공 인텔리전스 및 고급 규칙을 사용합니다. Azure SQL Database에서는 다른 쿼리에 미치는 영향을 최소화하여 데이터를 읽는 쿼리를 최적화하는 인덱스로서 최소한으로 필요한 인덱스 집합을 사용할 수 있습니다.

자동 인덱스 관리

Azure SQL Database는 검색 외에도 식별된 권장 사항을 자동으로 적용할 수 있습니다. 기본 제공 규칙이 데이터베이스의 성능을 향상시키는 것을 발견하는 경우 Azure SQL Database에서 자동으로 인덱스를 관리하도록 할 수도 있습니다.

CREATE INDEX 또는 DROP INDEX 권장 사항을 적용할 때 Azure SQL Database는 인덱스의 영향을 받는 쿼리의 성능을 자동으로 모니터링합니다. 영향을 받는 쿼리의 성능이 개선된 경우에만 새 인덱스가 유지됩니다. 인덱스가 없어서 느리게 실행되는 일부 쿼리가 있다면 삭제된 인덱스가 자동으로 다시 생성됩니다.

자동 인덱스 관리 고려 사항

Azure SQL Database에서 필요한 인덱스를 만드는 데 필요한 작업은 리소스를 소비하며 일시적으로 작업 성능에 영향을 줄 수 있습니다. 인덱스 생성이 워크로드 성능에 미치는 영향을 최소화하기 위해 Azure SQL Database는 인덱스 관리 작업에 대한 적절한 기간을 찾습니다. 워크로드를 실행하기 위해 데이터베이스에 리소스가 필요한 경우 튜닝 작업은 지연되며, 유지 관리 작업에 사용할 수 있는 것으로서 사용되지 않은 리소스가 데이터베이스에 충분히 있는 경우에는 튜닝 작업이 다시 시작됩니다. 자동 인덱스 관리에서 중요한 기능 중 하나는 작업 확인입니다. Azure SQL Database에서 인덱스를 만들거나 삭제할 때 모니터링 프로세스는 해당 작업이 전체 성능을 향상시켰는지 확인하기 위해 워크로드의 성능을 분석합니다. 큰 성능 향상을 가져오지 않은 경우 작업은 즉시 되돌려집니다. 이러한 방식으로 Azure SQL Database는 자동 조정 작업이 워크로드의 성능에 부정적인 영향을 주지 않도록 합니다. 자동 튜닝으로 만든 인덱스는 기본 스키마의 유지 관리 작업에 대해 투명합니다. 열 삭제 또는 이름 바꾸기와 같은 스키마 변경 내용은 자동으로 생성된 인덱스의 존재로 인해 차단되지 않습니다. Azure SQL Database에서 자동으로 생성된 인덱스는 관련 테이블 또는 열이 삭제될 때 즉시 삭제됩니다.

대안 - 수동 인덱스 관리

자동 인덱스 관리가 없으면 사용자 또는 DBA가 sys.dm_db_missing_index_details(Transact-SQL) 보기를 수동으로 쿼리하거나 Management Studio 성능 대시보드 보고서를 사용하여 성능을 향상시킬 수 있는 인덱스를 찾고, 이 보기에 제공된 세부 정보를 사용하여 인덱스를 만들고, 쿼리 성능을 수동으로 모니터링해야 합니다. 삭제해야 하는 인덱스를 찾으려면 사용자는 인덱스의 운영 사용 통계를 모니터링하여 거의 사용되지 않는 인덱스를 찾아야 합니다.

Azure SQL Database는 이 프로세스를 간소화합니다. Azure SQL Database는 해당 워크로드를 분석하고 새 인덱스를 사용하여 더 빠르게 실행할 수 있는 쿼리를 식별하며 사용되지 않거나 중복된 인덱스를 식별합니다. Azure Portal의 인덱스 찾기 권장 사항에서 변경해야 하는 인덱스 식별에 대한 자세한 내용을 찾습니다.

다음 단계