쿼리 최적화 프로그램 시간 제한의 영향을 받는 느린 쿼리 문제 해결

아티클
12/14/2024

적용 대상: SQL Server

이 문서에서는 최적화 프로그램 시간 제한, 쿼리 성능에 미치는 영향 및 성능을 최적화하는 방법을 소개합니다.

최적화 프로그램 시간 제한이란?

SQL Server는 비용 기반 QO(쿼리 최적화 프로그램 )를 사용합니다. QO에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조 하세요. 비용 기반 쿼리 최적화 프로그램은 여러 쿼리 계획을 빌드하고 평가한 후 가장 낮은 비용으로 쿼리 실행 계획을 선택합니다. SQL Server 쿼리 최적화 프로그램의 목표 중 하나는 쿼리 실행에 비해 쿼리 최적화에 적절한 시간을 소비하는 것입니다. 쿼리 최적화는 쿼리를 실행하는 것보다 훨씬 빠릅니다. 이 목표를 달성하기 위해 QO에는 최적화 프로세스를 중지하기 전에 고려해야 할 작업의 기본 제공 임계값이 있습니다. QO가 가능한 모든 계획을 고려하기 전에 임계값에 도달하면 최적화 프로그램 제한에 도달합니다. 최적화 프로그램 시간 제한 이벤트는 쿼리 계획에서 문 최적화의 조기 종료 사유에 따라 TimeOut으로 보고됩니다. 이 임계값은 클록 시간이 아니라 최적화 프로그램에서 고려한 가능성 수를 기반으로 한다는 것을 이해하는 것이 중요합니다. 현재 SQL Server QO 버전에서는 시간 제한에 도달하기 전에 절반 이상의 작업이 고려됩니다.

최적화 프로그램 시간 제한은 SQL Server로 설계되었으며 대부분의 경우 쿼리 성능에 영향을 주는 요소가 아닙니다. 그러나 경우에 따라 SQL 쿼리 계획 선택은 최적화 프로그램 시간 제한에 의해 부정적인 영향을 받을 수 있으며 쿼리 성능이 느려질 수 있습니다. 이러한 문제가 발생하면 최적화 프로그램 시간 제한 메커니즘과 복잡한 쿼리에 영향을 줄 수 있는 방법을 이해하면 쿼리 속도를 개선하고 문제를 해결하는 데 도움이 될 수 있습니다.

최적화 프로그램 제한 시간 임계값에 도달한 결과 SQL Server는 최적화에 대한 전체 가능성을 고려하지 않았습니다. 즉, 실행 시간이 짧아질 수 있는 계획을 놓쳤을 수 있습니다. QO는 임계값에서 중지되고 더 나은 미개척 옵션이 있더라도 해당 시점에서 최소 비용 쿼리 계획을 고려합니다. 최적화 프로그램 시간 제한에 도달한 후 선택한 계획은 쿼리에 대한 적절한 실행 기간을 생성할 수 있습니다. 그러나 경우에 따라 선택한 계획으로 인해 쿼리 실행이 최적이 아닐 수 있습니다.

최적화 프로그램 시간 제한을 검색하는 방법

최적화 프로그램 시간 제한을 나타내는 증상은 다음과 같습니다.

복잡한 쿼리

조인된 테이블이 많이 포함된 복잡한 쿼리가 있습니다(예: 8개 이상의 테이블이 조인됨).
느린 쿼리

쿼리는 다른 SQL Server 버전 또는 시스템에서 실행되는 것보다 느리거나 느리게 실행될 수 있습니다.

쿼리 계획에 StatementOptmEarlyAbortReason=Timeout이 표시됩니다.

쿼리 계획은 XML 쿼리 계획에 표시됩니다 StatementOptmEarlyAbortReason="TimeOut" .

<?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  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

Microsoft SQL Server Management Studio에서 가장 왼쪽에 있는 계획 연산자의 속성을 확인합니다. 문 최적화의 조기 종료 이유 값이 TimeOut임을 확인할 수 있습니다.

최적화 프로그램 시간 제한의 원인은 무엇인가요?

최적화 프로그램 임계값에 도달하거나 초과할 조건을 결정하는 간단한 방법은 없습니다. 다음 섹션은 최상의 계획을 찾는 경우 QO에서 탐색하는 플랜 수에 영향을 주는 몇 가지 요소입니다.

테이블을 조인해야 하는 순서는 무엇인가요?

다음은 3개의 테이블 조인(, Table2, Table3)Table1의 실행 옵션 예입니다.
- 조 Table1 인 Table2 및 결과 Table3
- 조 Table1 인 Table3 및 결과 Table2
- 조 Table2 인 Table3 및 결과 Table1
참고: 테이블 수가 클수록 가능성이 커집니다.
테이블에서 행을 검색하는 데 사용할 HoBT(힙 또는 이진 트리) 액세스 구조는 무엇인가요?
- 클러스터형 인덱스
- 비클러스터형 Index1
- 비클러스터형 인덱스2
- 테이블 힙
사용할 물리적 액세스 방법은 무엇인가요?
- 인덱스 검색
- 인덱스 검사
- 테이블 검색
사용할 물리 조인 연산자는 무엇인가요?
- NJ(중첩 루프 조인)
- 해시 조인(HJ)
- 병합 조인(MJ)
- 적응 조인(SQL Server 2017(14.x)부터)
자세한 내용은 Joins를 참조하세요.
쿼리의 일부를 병렬 또는 직렬로 실행하시겠습니까?

자세한 내용은 병렬 쿼리 처리를 참조하세요.

다음 요소는 고려된 액세스 방법의 수를 줄여주므로 고려될 수 있습니다.

쿼리 조건자(절의 WHERE 필터)
제약 조건의 존재
잘 디자인되고 최신 통계의 조합

참고: QO가 임계값에 도달한다고 해서 쿼리 속도가 느려지는 것은 아닙니다. 대부분의 경우 쿼리가 잘 수행되지만 경우에 따라 쿼리 실행 속도가 느려질 수 있습니다.

요인을 고려하는 방법의 예

이를 설명하기 위해 세 테이블(t1및 t2) 간의 조인 예제를 t3살펴보겠습니다. 각 테이블에는 클러스터형 인덱스와 비클러스터형 인덱스가 있습니다.

먼저 실제 조인 유형을 고려합니다. 여기에는 두 개의 조인이 포함됩니다. 또한 세 가지 물리적 조인 가능성(NJ, HJ 및 MJ)이 있기 때문에 3² = 9 방법으로 쿼리를 수행할 수 있습니다.

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

그런 다음 순열(P(n, r)을 사용하여 계산되는 조인 순서를 고려합니다. 처음 두 테이블의 순서는 중요하지 않으므로 P(3,1) = 3가지 가능성이 있을 수 있습니다.

다음으로 조 t1 인 t2t3
다음으로 조 t1 인 t3t2
다음으로 조 t2 인 t3t1

다음으로, 데이터 검색에 사용할 수 있는 클러스터형 및 비클러스터형 인덱스를 고려합니다. 또한 각 인덱스마다 검색 또는 검색의 두 가지 액세스 방법이 있습니다. 즉, 각 테이블에 대해 2 2 = 4 선택 항목이 있습니다. 3개의 테이블이 있으므로 4³ = 64개의 선택 항목이 있을 수 있습니다.

마지막으로 이러한 모든 조건을 고려하면 9*3*64 = 1728 가능한 계획이 있을 수 있습니다.

이제 쿼리에 조인된 테이블이 n개이고 각 테이블에 클러스터형 인덱스와 비클러스터형 인덱스가 있다고 가정해 보겠습니다. 다음 사항을 고려합니다.

조인 주문: P(n,n-2) = n!/2
조인 유형: 3^n-1
seek 및 scan 메서드를 사용하여 다른 인덱스 형식: 4ⁿ

위의 모든 것을 곱하면 가능한 계획 수를 얻을 수 있습니다. 2*n!*12^n-1. n = 4이면 숫자는 82,944입니다. n = 6이면 숫자는 358,318,080입니다. 따라서 쿼리와 관련된 테이블 수가 증가함에 따라 가능한 계획 수가 기하학적으로 증가합니다. 또한 병렬 처리 및 기타 요인의 가능성을 포함하는 경우 얼마나 많은 가능한 계획이 고려될지 상상할 수 있습니다. 따라서 조인이 많은 쿼리는 조인 수가 적은 쿼리보다 최적화 프로그램 제한 시간 임계값에 도달할 가능성이 높습니다.

위의 계산은 최악의 시나리오를 보여 줍니다. 우리가 지적했듯이 필터 조건자, 통계 및 제약 조건자와 같은 가능성의 수를 줄이는 요인이 있습니다. 예를 들어 필터 조건자와 업데이트된 통계는 검사보다 인덱스 검색을 사용하는 것이 더 효율적일 수 있으므로 물리적 액세스 방법의 수를 줄입니다. 이렇게 하면 조인을 더 작게 선택할 수도 있습니다.

간단한 쿼리로 최적화 프로그램 시간 제한이 표시되는 이유는 무엇인가요?

쿼리 최적화 프로그램이 있는 것은 간단하지 않습니다. 가능한 시나리오가 많고 복잡성 정도가 너무 높아 모든 가능성을 파악하기가 어렵습니다. 쿼리 최적화 프로그램은 특정 단계에서 찾은 계획의 비용에 따라 시간 제한 임계값을 동적으로 설정할 수 있습니다. 예를 들어 비교적 효율적인 것으로 보이는 계획이 발견되면 더 나은 계획을 검색하는 작업 제한이 줄어들 수 있습니다. 따라서 과소 평가된 CE(카디널리티 예측 )는 최적화 프로그램 시간 제한을 조기에 타격하기 위한 한 가지 시나리오일 수 있습니다. 이 경우 조사의 초점은 CE입니다. 이전 섹션에서 설명한 복잡한 쿼리를 실행하는 시나리오와 비교할 때 드문 경우이지만 가능합니다.

해결 방법

쿼리 계획에 표시되는 최적화 프로그램 시간 제한이 반드시 쿼리 성능 저하의 원인이 되는 것은 아닙니다. 대부분의 경우 이 상황에 대해 아무 작업도 수행할 필요가 없습니다. SQL Server가 종료되는 쿼리 계획이 적절할 수 있으며 실행 중인 쿼리가 잘 수행될 수 있습니다. 최적화 프로그램 시간 초과가 발생했음을 알 수 없습니다.

튜닝 및 최적화가 필요한 경우 다음 단계를 시도합니다.

1단계: 기준 설정

다른 CE 구성을 사용하거나 다른 시스템(하드웨어 사양)을 사용하여 SQL Server의 다른 빌드에서 동일한 데이터 집합으로 동일한 쿼리를 실행할 수 있는지 확인합니다. 성능 튜닝의 지침 원칙은 "기준이 없으면 성능 문제가 없습니다."입니다. 따라서 동일한 쿼리에 대한 기준을 설정하는 것이 중요합니다.

2단계: 최적화 프로그램 제한 시간으로 이어지는 "숨겨진" 조건 찾기

쿼리를 자세히 검토하여 복잡성을 확인합니다. 초기 검사 시 쿼리가 복잡하고 많은 조인을 포함하는 것은 분명하지 않을 수 있습니다. 여기서 일반적인 시나리오는 뷰 또는 테이블 반환 함수가 관련되어 있다는 것입니다. 예를 들어 표면에서 쿼리는 두 뷰를 조인하므로 단순해 보일 수 있습니다. 그러나 뷰 내의 쿼리를 검사할 때 각 보기가 7개의 테이블을 조인하는 것을 확인할 수 있습니다. 따라서 두 뷰가 조인되면 결국 14개 테이블 조인으로 끝납니다. 쿼리에서 다음 개체를 사용하는 경우 각 개체를 드릴다운하여 내부 쿼리의 모양을 확인합니다.

이러한 모든 시나리오에서 가장 일반적인 해결 방법은 쿼리를 다시 작성하고 여러 쿼리로 분할하는 것입니다. 자세한 내용은 7단계: 쿼리 구체화를 참조하세요.

하위 쿼리 또는 파생 테이블

다음 쿼리는 각각에 4-5개의 조인을 사용하여 두 개의 개별 쿼리 집합(파생 테이블)을 조인하는 예제입니다. 그러나 SQL Server에서 구문 분석한 후에는 8개의 테이블이 조인된 단일 쿼리로 컴파일됩니다.

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

CTE(공통 테이블 식)

여러 개의 공통 테이블 식(CTE)을 사용하는 것은 쿼리를 단순화하고 최적화 프로그램 시간 제한을 방지하기 위한 적절한 솔루션이 아닙니다. 여러 CTE는 쿼리의 복잡성만 증가합니다. 따라서 최적화 프로그램 시간 제한을 해결할 때 CTE를 사용하는 것은 비생산적입니다. CTE는 쿼리를 논리적으로 중단하는 것처럼 보이지만 단일 쿼리로 결합되고 테이블의 단일 큰 조인으로 최적화됩니다.

다음은 많은 조인이 있는 단일 쿼리로 컴파일되는 CTE의 예입니다. my_cte 대한 쿼리가 2개 개체 단순 조인 것처럼 보일 수 있지만 실제로 CTE에는 7개의 다른 테이블이 조인되어 있습니다.

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

보기

뷰 정의를 확인하고 관련된 모든 테이블을 얻었는지 확인합니다. CTE 및 파생 테이블과 마찬가지로 조인은 보기 내에서 숨겨질 수 있습니다. 예를 들어 두 뷰 간의 조인은 궁극적으로 8개의 테이블이 포함된 단일 쿼리일 수 있습니다.

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

TVF(테이블 반환 함수)

일부 조인은 TFV 내에 숨겨져 있을 수 있습니다. 다음 샘플에서는 두 TFV 간의 조인으로 표시되는 내용을 보여 줍니다. 테이블은 9개 테이블 조인일 수 있습니다.

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Union

공용 구조체 연산자는 여러 쿼리의 결과를 단일 결과 집합으로 결합합니다. 또한 여러 쿼리를 단일 쿼리로 결합합니다. 그런 다음, 복잡한 단일 쿼리를 가져올 수 있습니다. 다음 예제에서는 12개의 테이블을 포함하는 단일 쿼리 계획으로 끝납니다.

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

3단계: 더 빠르게 실행되는 기준 쿼리가 있는 경우 해당 쿼리 계획을 사용합니다.

1단계에서 가져오는 특정 기준 계획이 테스트를 통해 쿼리에 더 적합한 것으로 확인되면 다음 옵션 중 하나를 사용하여 QO가 해당 계획을 선택하도록 강제합니다.

4단계: 계획 선택 줄이기

최적화 프로그램 제한 시간을 줄이려면 계획을 선택할 때 QO에서 고려해야 하는 가능성을 줄이십시오. 이 프로세스에는 다양한 힌트 옵션을 사용하여 쿼리를 테스트하는 작업이 포함됩니다. QO를 사용하는 대부분의 결정과 마찬가지로 고려해야 할 다양한 요소가 있기 때문에 선택 사항이 항상 표면에서 결정적이지는 않습니다. 따라서 단일 보장된 성공적인 전략이 없으며 선택한 계획이 선택한 쿼리의 성능을 향상시키거나 줄일 수 있습니다.

JOIN 주문 강제 적용

순서 순열을 제거하는 데 사용합니다 OPTION (FORCE ORDER) .

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

JOIN 가능성 줄이기

다른 대안이 도움이 되지 않는 경우 조인 힌트를 사용하여 실제 조인 연산자의 선택을 제한하여 쿼리 계획 조합을 줄이십시오. 예: OPTION (HASH JOIN, MERGE JOIN)또는 OPTION (HASH JOIN, LOOP JOIN) OPTION (MERGE JOIN).

참고: 이러한 힌트를 사용할 때는 주의해야 합니다.

경우에 따라 조인 선택 수가 적은 최적화 관리자를 제한하면 최상의 조인 옵션을 사용할 수 없게 되고 실제로 쿼리 속도가 느려질 수 있습니다. 또한 경우에 따라 최적화 프로그램에서 특정 조인(예 : 행 목표)이 필요하며 해당 조인이 옵션이 아닌 경우 쿼리가 계획을 생성하지 못할 수 있습니다. 따라서 특정 쿼리에 대한 조인 힌트를 대상으로 지정한 후 더 나은 성능을 제공하고 최적화 프로그램 시간 제한을 제거하는 조합을 찾을 수 있는지 확인합니다.

이러한 힌트를 사용하는 방법에 대한 두 가지 예는 다음과 같습니다.

해시 및 루프 조인만 허용하고 쿼리에서 병합 조인을 방지하는 데 사용합니다 OPTION (HASH JOIN, LOOP JOIN) .
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

다음 두 테이블 간에 특정 조인을 적용합니다.

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

5단계: CE 구성 변경

레거시 CE와 새 CE 간에 전환하여 CE 구성을 변경해 보세요. CE 구성을 변경하면 SQL Server가 쿼리 계획을 평가하고 만들 때 QO가 다른 경로를 선택할 수 있습니다. 따라서 최적화 프로그램 시간 제한 문제가 발생하더라도 대체 CE 구성을 사용하여 선택한 계획보다 더 최적으로 수행되는 계획으로 끝날 수 있습니다. 자세한 내용은 최상의 쿼리 계획(카디널리티 추정)을 활성화하는 방법을 참조하세요.

6단계: 최적화 프로그램 수정 사용

쿼리 최적화 프로그램 수정을 사용하도록 설정하지 않은 경우 다음 두 가지 방법 중 하나를 사용하여 사용하도록 설정하는 것이 좋습니다.

서버 수준: 추적 플래그 T4199를 사용합니다.
데이터베이스 수준: SQL Server 2016 이상 버전의 데이터베이스 호환성 수준을 사용 ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON 하거나 변경합니다.

QO 수정으로 인해 최적화 프로그램이 계획 탐색에서 다른 경로를 취할 수 있습니다. 따라서 더 최적의 쿼리 계획을 선택할 수 있습니다. 자세한 내용은 SQL Server 쿼리 최적화 프로그램 핫픽스 추적 플래그 4199 서비스 모델을 참조하세요.

7단계: 쿼리 구체화

임시 테이블을 사용하여 단일 다중 테이블 쿼리를 여러 개의 개별 쿼리로 분할하는 것이 좋습니다. 쿼리를 분리하는 것은 최적화 프로그램의 작업을 간소화하는 방법 중 하나일 뿐입니다. 다음 예제를 참조하십시오.

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

쿼리를 최적화하려면 조인 결과의 일부를 임시 테이블에 삽입하여 단일 쿼리를 두 개의 쿼리로 분할합니다.

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

다음을 통해 공유