새 카디널리티 추정기에서 조인 포함 가정은 쿼리 성능을 저하합니다.

이 문서는 새 카디널리티 추정기를 사용하여 쿼리를 컴파일할 때 SQL Server 2014 이상 버전에서 발생할 수 있는 성능 문제를 해결하는 데 도움이 됩니다.

원래 제품 버전: SQL Server
원래 KB 번호: 3189675

증상

다음 시나리오를 고려하세요.

• SQL Server 2014 이상 버전을 사용하고 있습니다.
• 조인 및 비 조인 필터 조건자가 포함된 쿼리를 실행합니다.
• 새 카디널리티 예측(SQL Server)(새 CE) 을 사용하여 쿼리를 컴파일합니다.

이 시나리오에서는 쿼리 성능 저하가 발생합니다.

레거시 CE를 사용하여 쿼리를 컴파일하는 경우에는 이 문제가 발생하지 않습니다.

원인

SQL Server 2014부터 데이터베이스 호환성 수준 120 이상을 위해 새 CE(카디널리티 추정기)가 도입되었습니다. 새 CE는 다른 연산자와 조건자에 대한 카디널리티를 예측할 때 쿼리 최적화 프로그램에서 사용하는 모델의 레거시 CE에서 여러 가정을 변경합니다.

이러한 변경 내용 중 하나는 조인 포함 가정과 관련이 있습니다.

레거시 CE 모델은 사용자가 항상 존재하는 데이터를 쿼리한다고 가정합니다. 즉, 두 테이블에 대한 평등 연산을 포함하는 조인 조건자의 경우 조인된 열이 조인의 양쪽에 존재합니다. 조인 테이블에 대한 추가 비 조인 필터 조건자가 있는 경우 레거시 CE는 조인 조건자와 비 조인 필터 조건자에 대해 어느 정도의 상관 관계를 가정합니다. 이 암시적 상관 관계를 단순 포함이라고합니다.

또는 새 CE는 기본 포함을 상관 관계로 사용합니다. 새 CE 모델은 사용자가 존재하지 않는 데이터를 쿼리할 수 있다고 가정합니다. 즉, 별도의 테이블의 필터 조건자는 서로 상관 관계가 없을 수 있습니다. 따라서 확률적 접근 방식을 사용합니다.

많은 실제 시나리오에서 기본 포함 가정을 사용하면 더 나은 추정치가 생성됩니다. 그러면 더 효율적인 쿼리 계획 선택 항목이 만들어집니다. 그러나 일부 상황에서는 단순 포함 가정을 사용하면 더 나은 결과를 제공할 수 있습니다. 이 경우 레거시 CE 대신 새 CE를 사용할 때 덜 효율적인 쿼리 계획 선택이 발생할 수 있습니다.

새 CE와 관련된 문제를 해결하는 방법에 대한 자세한 내용은 SQL Server 2012 또는 이전 버전에서 2014 이상으로 업그레이드한 후 쿼리 성능 저하를 참조하세요.

해결

SQL Server 2014 이상 버전에서는 추적 플래그 9476을 사용하여 SQL Server가 기본 기본 포함 가정 대신 단순 포함 가정을 사용하도록 강제할 수 있습니다. 애플리케이션 쿼리를 수정할 수 있는 경우 SQL Server 2016(13.x) SP1 이후 쿼리 힌트 ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS 를 사용하는 것이 좋습니다. 자세한 내용은 USE HINT를 참조하세요. 예시:

SELECT * FROM Table1 t1
JOIN Table2 t2
ON t1.Col1 = t2.Col1
WHERE Col1 = 10
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

이 추적 플래그를 사용하도록 설정하거나 쿼리 힌트를 사용하면 다음 조건이 충족되는 경우 레거시 CE 모델로 완전히 되돌릴 필요 없이 쿼리 계획 선택이 향상될 수 있습니다.

• 조인 및 비 조인 필터 조건자가 포함된 쿼리의 성능이 전반적으로 저하되는 최적이 아닌 쿼리 계획 선택이 발생합니다.
• "조인 카디널리티" 예측에서 상당한 부정확성을 확인할 수 있습니다(즉, 실제 행 수와 예상 행 수가 크게 다릅니다).
• 레거시 CE를 사용하여 쿼리를 컴파일할 때는 이 부정확성이 존재하지 않습니다.

이 추적 플래그를 전역적으로, 세션 수준 또는 쿼리 수준에서 사용하도록 설정할 수 있습니다.