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 출력이다.

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

 <BatchSequence>

  <Batch>

   <Statements>

    <StmtSimple StatementCompId="6" StatementEstRows="419.335" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>

    ...

   <Statements>

  <Batch>

<BatchSequence>

 

아래 그림은 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 임계값에 도달하는 것을   있다.

참고 쿼리 조건자(WHERE 절의 필터 제약 조건의 존재로 인해 고려되는 액세스 방법 수가 줄어들가능성은 있다.

 

최적화 프로그램 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 대해서 데이터베이스 호환성 수준을 사용하면 이러한 수정을 적용하는 것이 좋다이로 인해 최적화 프로그램이 계획탐색에서 다른 경로를 사용하게 되어 결국 최적의 쿼리 계획으로 끝날  있다.

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

select ...

from t1

join t2

on t1.id = t2.id

join t3

on t3.id = t2.id

join t4

on t4.id = t3.id

 

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

select ...

into #temp1

from t1

join t2

on t1.id = t2.id

 

select ...

From t3

join #temp1

on t3.id = #temp1.id

join t4

on t4.id = t3.id

 

쿼리를 분할할때 중요한것은 여러 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



출처: https://sqlmvp.tistory.com/1275?category=618825 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.