SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout

·         Version : SQL Server

SQL Server는 비용 기반(cost-based) 쿼리 최적화 프로그램을 사용한다. 따라서 여러 쿼리 계획을 작성하고 검사한 후 비용이 가장 낮은 쿼리 계획을 선택한다. SQL Server 쿼리 최적화 프로그램(QO)의 목적중 하나는 쿼리 실행과 비교하여 쿼리 최적화에 “합리적인 시간”을 소비하는 것이다. 따라서 QO에는 최적화 프로세스를 중지하기 전에 고려해야 할 태스크 임계값이 내장되어 있다. QO가 모든 계획은 아니지만 가능한 대부분의 계획을 고려하기 전에 임계값에 도달하면 Optimizer Timeout 한계에 도달한다. 이벤트 로그에는 “Reason For Early Termination of Statement Optimization.”로 기록된다. 여기서 이해야하는 중요한 부분이 임계 값은 실제 시간이 아니라 고려된 가능성의 수를 기반으로 한다는 것이다. 현재 SQL QO 버전에서는 Timeout에 도달하기 전에 50만개가 넘는 가능성이 고려된다.

최적화 프로그램 Timeout은 Microsoft SQL Server에서 디자인 하였으며 많은 경우 쿼리 성능에 영향을 주는 요소가 아니다. 그러나 경우에 따라 SQL 쿼리 계획 선택에서 최적화 프로그램 Timeout이 적용될 수 있으므로 성능에 영향을 줄 수 있다. 이러한 문제가 발생하면 SQL Server 최적화 프로그램 Timeout 메커니즘과 복잡한 쿼리가 영향을 받을 수 있음을 이해하면 성능 문제를 보다 효과적으로 해결하고 개선하는데 도움이 될 수 있다.

증상은 무엇인가?

·         여러 개의 조인된 테이블을 포함하는 복잡한 쿼리가 있다.(예 : 8개 이상의 테이블이 조인됨)

·         쿼리를 다른 SQL Server 버전이나 다른 시스템과 비교하였을 때 느리게 실행되거나 느리게 실행될 수 있다. (성능 베이스라인이 필수적으로 있어야 한다.)

·         XML 쿼리 계획에서 StatementOptmEarlyAbortReason=”TimeOut” 정보를 확인한다. 또는 Microsoft SQL Server Management Studio에서 가장 왼쪽 계획 연산자의 속성을 확인해보면 “Reason For Early Termination of Statement Optimization” 값이 “TimeOut”인지 확인한다.

아래 결과는 옵티마이저 Timeout을 보여주는 쿼리 계획의 XML 출력이다.

아래 그림은 TimeOut값을 표시하는 실행계획의 그래픽 표현 이다.

어떻게 작동 할까?

옵티마이저 임계값에 도달하거나 초과하게 하는 조건을 판별하는 간단한 공식은 없다. 그러나 아래 요소는 QO가 최상의 계획을 결정하는 몇 가지 요소이다.

·         테이블을 조인할 순서 : (테이블 수가 많을 수록 가능성이 커짐)

o   Table1과 Table2, Table3과 조인

o   Table1과 Table3, Table2와 조인

·          힙(Heap) 또는 이진트리(HoBT) 테이블에서 행을 검색하는데 사용하는 액세스 구조 :

o   Nonclustered Index 1

o   Nonclustered Index 2

o   Clustered Index 등

·         액세스 방법 :

o   Index seek

o   Index scan

o   Table scan

·         물리적 조인 연산자?

o   Nested Loop

o   Hash Match

o   Merge Join (NL, HM, MJ)

·         병렬 계획 또는 직렬 계획을 사용?

이해를 돕기위해 한가지 예를 들어 본다. 3개의 테이블 (T1, T2, T3)간의 조인을 예로 들며 각 테이블에는 클러스터된 인덱스만 있다. 여기에는 두 개의 조인이 포함되며 세 개의 실제 조인 가능성 (NL, HM, MJ)이 있으므로 두 조인을6(2*3) 방식으로 수행할 수 있다. 조인 순서도 고려해야한다.

·         T1이 T2에 조인되고 T3에 조인됨

·         T1이 T3에 조인되고 T2에 조인됨

·         T2는 T3에 조인되고 T1에 조인됨

이제 6 *3 조인 명령을 곱하면 선택할 수 있는 최소 18가지 가능한 계획이 있다. HoBT의 Seek 또는 Scan과 같은 병렬성 및 기타 요인의 가능성을 포하 시키면 가능한 계획이 훨씬 더 늘어난다. 만약 쿼리에 10개의 테이블이 포함될 때 수백만개의 가능한 순열이 있음을 알 수 있다. 따라서 많은 조인이 있는 쿼리가 조인이 적은 쿼리보다 더 많은 시간이 옵티마이저 Timeout 임계값에 도달하는 것을 볼 수 있다.

최적화 프로그램 Timeout 임계 값에 도달한 결과 SQL Server는 최적화 가능성을 모두 고려하지 않았으므로 실행 시간이 단축 될 수 있는 계획을 놓쳤을 수 있다. QO는 임계 값에서 멈추고 그 시점에서 가장 비용이 적게 드는 쿼리 계획을 고려한다.

간단한 쿼리로 Optimizer Timeout을 재현할 수 있을까?

QO는 단순하지 않다. 가능한 많은 시나리오가 있으며 복잡성이 너무 높아서 모든 가능성을 파악하기 어렵다. Query Optimizer는 특정 단계에서 발견된 계획의 비용을 기반으로 제한 시간 임계값을 동적으로 조정/설정할 수 있다. 예를 들어 상대적으로 “저렴한”것으로 보이는 계획이 발견되면 더 나은 계획을 찾기 위한 작업이 제한 될 수 있다. 따라서 과소 평과된 카디널리티 추정은 옵티마이저 timeout을 일찍 도달하게 하는 하나의 원인일 수 있다. 이 경우 조사의 초점은 카디널리티 추정이다. 이는 복잡한 쿼리 실행에 대해 이전에 논의 된 시나리오보다 드문 경우 이지만 가능하다.

 무엇을 해야하는가?

아무것도 하지 않아도 된다. 많은 경우에 있어서 얻는 계획은 상당히 합리적이며 실행중인 쿼리가 잘 수행된다. 그러나 조정하고 최적화 할 필요가 있는 경우 다음 옵션을 고려해야한다.

·         SQL Server의 다른 빌드에서 실행하거나 다른 CE 구성 또는 다른 시스템을 사용하는것과 비교할 때 조사중인 쿼리가 느려지는지 확인해야 한다.

·         복잡성을 판별할때 자세히 조회해야한다. 처음 검토 할때 쿼리가 복잡하고 많은 조인이 필요하다는 것은 분명하지않을 수 있다. 뷰 또는 테이블 반환 함수가 관련된 일반적인 경우다. 예를 들어 표면에 두 개의 뷰를 결합하기 때문에 쿼리가 간단해 보일 수 있다. 그러나 뷰를 검사할 때 각 뷰가 7개의 테이블을 조인한다는것을 알 수 있다. 두 개의 뷰가 조인되면 결국 14개의 테이블로 조인된다..

아래는 쿼리 성능을 향상 시키는데 도움이 되는 다양한 방법이다. 다시 한번 말하지만 쿼리 계획에 최적화 프로그램 Timeout이 있다는 사실이 반드시 쿼리 속도가 느린 이유라는 것을 의미 하지는 않는다.

·         특정 계획 강제 실행 :  테스트를 통해 특정 계획이 쿼리에 더 적합하다고 판단되면 QO에 해당 계획을 선택하도록 요청해야한다.

o   QDS(Query Data Store)를 사용하여 계획을 강제로 실행하는 방법 : https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-2017

o   쿼리 힌트 옵션 (PLAN <XML PLAN HERE> 사용) : https://docs.microsoft.com/ko-kr/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

·         플랜 가이드 : QO가 고려해야할 조인 가능성을 줄여야한다. 여기에는 다양한 옵션으로 쿼리를 테스트하는 것이 포함된다. QO를 사용한 대부분의 결정과 마찬가지로 선택의 폭이 매우 다양하기 때문에 항상 결정적인 것은 아니다. 따라서 성공적인 전략을 보장한 사람은 없다. 이것들은 선택된 쿼리의 성능을 향상 시키거나 악화시킬수 있다. 자세한 내용은 쿼리 힌트를 참고한다.

o   쿼리 힌트 : https://docs.microsoft.com/ko-kr/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

o   조인 순서 고정 : OPTION (FORCEORDER)

o   JOIN 가능성을 줄이기 : OPTION( 해시 조인 , 병합조인), OPTION(해시 조인, 조인 루프), OPTION(병합조인)

·         Cardinality Estimation(CE) 구성 변경 : 레거시 CE에서 새 CE로 또는 새CE에서 레거시 CE로 전환하여 카디널리티 예상 구성을 변경할 수 있다. Cardinality Estimation 구성을 변경하면 SQL Server가 쿼리 계획을 평가하고 생성할 때 QO가 다른 경로를 선택하게 될 수 있다. 따라서 최적화 프로그램 시간 초과 문제가 발생하더라도 대체 CE 구성을 사용하여 선택한 것보다 더 최적으로 수행하는 계획으르 수립할 수 있다. 자세한 내용은 SQL Server 시스템 최상의 카디널리티 추정 구성을 평가하고 선택하는 방법을 참조한다.

o   Cardinality Estimation : https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2017&viewFallbackFrom=sql-server-2017%20

·         Optimizer fixes : T4199를 통해 QO 수정을 사용하지 않았거나 SQL Server 2016 이상 또는 ALTER DATABASE SCOPED CONFIGURATION...QUERY_OPTIMIZER_HOTFIXES =ON에 대해서 데이터베이스 호환성 수준을 사용하면 이러한 수정을 적용하는 것이 좋다. 이로 인해 최적화 프로그램이 계획탐색에서 다른 경로를 사용하게 되어 결국 최적의 쿼리 계획으로 끝날 수 있다.

·         쿼리를 다시 작성 : 임시 테이블을 사용하여 단일 다중 테이블 쿼리를 여러 쿼리로 분할하는 것을 고려해야한다. 그러나 근본적인 해결책은 아니다. 쿼리를 분리하면 최적화 프로그램의 작업을 단순화 하는 방법중 하나이다. 아래 샘플을 참고한다.

최적화하려면 두 가지 쿼리로 나눈다.

쿼리를 분할할때 중요한것은 여러 CTE (Common Table Expression)를 사용하는것이 쿼리를 단순화하는 적절한 솔루션이 아니다. 여러 CTE는 쿼리의 복잡성만 증가 시킨다. 따라서 비생산적이다. CTE는 논리적으로 쿼리를 중단하지만 단일 쿼리로 결합되어 결국 테이블의 단일 큰 조인으로 최적화 된다.

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2018/10/19/understanding-optimizer-timeout-and-how-complex-queries-can-be-affected-in-sql-server/

2018-10-26 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MSSQL, SQL Optimizer, 옵티마이저, 쿼리 튜닝, DB 튜닝, Optimizer timeout