새 카디널리티 추정기에서 조인 포함 가정은 쿼리 성능을 저하합니다.
이 문서는 새 카디널리티 예측 도구를 사용하여 쿼리를 컴파일할 때 SQL Server 2014 이상 버전에서 발생할 수 있는 성능 문제를 resolve 데 도움이 됩니다.
원래 제품 버전: SQL Server
원래 KB 번호: 3189675
증상
다음과 같은 경우를 생각해볼 수 있습니다.
- SQL Server 2014 이상 버전을 사용하고 있습니다.
- 조인 및 비 조인 필터 조건자가 포함된 쿼리를 실행합니다.
- 새 SQL SERVER(카디널리티 추정)(새 CE)을 사용하여 쿼리를 컴파일합니다.
이 시나리오에서는 쿼리 성능이 저하됩니다.
레거시 CE를 사용하여 쿼리를 컴파일하는 경우에는 이 문제가 발생하지 않습니다.
원인
2014년 SQL Server 데이터베이스 호환성 수준 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를 사용하여 쿼리를 컴파일할 때는 이 부정확성이 존재하지 않습니다.
이 추적 플래그를 전역적으로, 세션 수준 또는 쿼리 수준에서 사용하도록 설정할 수 있습니다.
참고
추적 플래그를 잘못 사용하면 워크로드 성능이 저하됩니다. 자세한 내용은 힌트(Transact-SQL) - 쿼리를 참조하세요.