카디널리티 예측(SQL Server)

카디널리티 추정기라고 하는 카디널리티 추정 논리는 쿼리 계획의 품질을 개선하고 쿼리 성능을 향상시키기 위해 SQL Server 2014에서 다시 설계되었습니다. 새로운 카디널리티 추정기는 최신 OLTP 및 데이터 웨어하우징 워크로드에서 잘 작동하는 가정 및 알고리즘을 통합합니다. 최신 워크로드에 대한 심층 카디널리티 추정 연구와 SQL Server 카디널리티 추정기를 개선한 지난 15년간의 학습을 기반으로 합니다. 고객의 피드백에 따르면 대부분의 쿼리는 변경의 이점을 활용하거나 변경되지 않은 상태로 유지되지만 이전 카디널리티 예측 도구에 비해 회귀가 적은 수로 표시될 수 있습니다.

성능 테스트 및 튜닝 권장 사항

SQL Server 2014에서 만든 모든 새 데이터베이스에 대해 새 카디널리티 추정기가 사용하도록 설정됩니다. 그러나 SQL Server 2014로 업그레이드해도 기존 데이터베이스에서 새 카디널리티 추정기가 활성화되지는 않습니다.

최상의 쿼리 성능을 보장하려면 프로덕션 시스템에서 사용하도록 설정하기 전에 이러한 권장 사항을 사용하여 새 카디널리티 예측 도구로 워크로드를 테스트합니다.

1. 새 카디널리티 추정기를 사용하도록 모든 기존 데이터베이스를 업그레이드합니다. 이렇게 하려면 ALTER DATABASE 호환성 수준(Transact-SQL) 을 사용하여 데이터베이스 호환성 수준을 120으로 설정합니다.

2. 새 카디널리티 추정기를 사용하여 테스트 워크로드를 실행한 다음, 현재 성능 문제를 해결하는 것과 동일한 방식으로 새 성능 문제를 해결합니다.

3. 워크로드가 새 카디널리티 예측 도구(데이터베이스 호환성 수준 120(SQL Server 2014))로 실행되고 특정 쿼리가 회귀되면 추적 플래그 9481로 쿼리를 실행하여 SQL Server 2012 이하에서 사용된 카디널리티 예측 도구 버전을 사용할 수 있습니다. 추적 플래그를 사용하여 쿼리를 실행하려면 특정 쿼리 수준에서 다양한 추적 플래그로 제어할 수 있는 계획에 영향을 미치는 SQL Server 쿼리 최적화 프로그램의 동작을 활성화하는 방법에 대한 KB 문서를 참조하세요.

4. 새 카디널리티 추정기를 사용하도록 모든 데이터베이스를 한 번에 변경할 수 없는 경우 ALTER DATABASE 호환성 수준(Transact-SQL) 을 사용하여 모든 데이터베이스에 대해 이전 카디널리티 추정기를 사용하여 데이터베이스 호환성 수준을 110으로 설정할 수 있습니다.

5. 워크로드가 데이터베이스 호환성 수준 110으로 실행 중이고 새 카디널리티 추정기를 사용하여 특정 쿼리를 테스트하거나 실행하려는 경우 추적 플래그 2312를 사용하여 쿼리를 실행하여 카디널리티 추정기의 SQL Server 2014 버전을 사용할 수 있습니다. 추적 플래그를 사용하여 쿼리를 실행하려면 특정 쿼리 수준에서 다양한 추적 플래그로 제어할 수 있는 계획에 영향을 주는 SQL Server 쿼리 최적화 동작을 활성화하는 방법에 대한 KB 문서를 참조하세요.

새 XEvents

새 쿼리 계획을 지원하기 위한 두 가지 새로운 query_optimizer_estimate_cardinality XEvent가 있습니다.

• query_optimizer_estimate_cardinality 쿼리 최적화 프로그램에서 관계형 식의 카디널리티를 추정할 때 발생합니다.

• query_optimizer_force_both_cardinality_estimation_behaviors는 추적 플래그 2312와 9481이 모두 활성화될 때 발생하며, 이는 이전과 새로운 카디널리티 예측 동작을 동시에 강제 적용하려고 시도하는 경우입니다.

예시

다음 예제에서는 새 카디널리티 예상치의 일부 변경 내용을 보여 줍니다. 카디널리티를 예측하기 위한 코드를 다시 작성했습니다. 논리는 복잡하며 모든 변경 내용의 전체 목록을 제공할 수 없습니다.

예제 A. 새 카디널리티 예측은 최근에 추가된 오름차순 데이터에 평균 카디널리티를 사용합니다.

이 예제에서는 새 카디널리티 예측 도구가 최신 통계 업데이트 중에 테이블의 최대값을 초과하는 오름차순 데이터에 대한 카디널리티 예상치를 개선하는 방법을 보여 줍니다.

SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';  

이 예제에서는 매일 새 행이 Sales 테이블에 추가되고, 쿼리는 2013년 12월 19일에 발생한 판매를 요청하며, 통계는 2013년 12월 18일에 마지막으로 업데이트되었습니다. 이전 카디널리티 추정기는 날짜가 최대 날짜를 초과하고 통계가 2013년 12월 19일 값을 포함하도록 업데이트되지 않았기 때문에 2013년 12월 19일 값이 존재하지 않는다고 가정합니다. 오름차순 키 문제로 알려진 이 상황은 하루 동안 데이터를 로드한 다음 통계가 업데이트되기 전에 데이터에 대해 쿼리를 실행하는 경우에 발생합니다.

이 동작이 변경되었습니다. 이제 마지막 통계 업데이트 이후 추가된 가장 최근의 오름차순 데이터에 대한 통계가 업데이트되지 않은 경우에도 새 카디널리티 추정기는 값이 있다고 가정하고 열의 각 값에 대한 평균 카디널리티를 카디널리티 추정값으로 사용합니다.

예제 B. 새 카디널리티 예측에서는 동일한 테이블의 필터링된 조건자가 몇 가지 상관 관계가 있다고 가정합니다.

이 예제에서는 자동차 테이블을 1,000행으로 가정하고, Make에는 '혼다'에 대해 200개의 일치 항목이 있고, 모델은 'Civic'에 대해 50개의 일치 항목을 가지고 있으며, 모든 시빅은 혼다라고 가정합니다. 따라서 메이크 열에 있는 값 중 20개% '혼다', 모델 열의 값 중 5%는 'Civic'이고, 혼다 시빅의 실제 수는 50입니다. 이전 카디널리티 예측에서는 Make 및 Model 열의 값이 서로 독립적이라고 가정합니다. 이전 쿼리 최적화 프로그램은 10개의 Honda Civics(.05 * .20 * 1000개 행 = 10개 행)가 있다고 추정합니다.

SELECT year, purchase_price FROM dbo.Cars WHERE Make = 'Honda' AND Model = 'Civic';  

이 동작이 변경되었습니다. 이제 새 카디널리티 추정에서는 Make 및 Model 열에 약간의 상관 관계가 있다고 가정합니다. 쿼리 최적화 프로그램은 예측 수식에 지수 구성 요소를 추가하여 더 높은 카디널리티를 예측합니다. 이제 쿼리 최적화 프로그램은 22.36개 행(.05 * SQRT(.20) * 1000개 행 = 22.36개 행)이 조건자와 일치하는 것으로 추정합니다. 이 시나리오 및 특정 데이터 배포의 경우 22.36개의 행이 쿼리가 반환하는 실제 50개 행에 가깝습니다.

새 카디널리티 추정기 논리는 조건자 선택성을 정렬하고 지수를 증가합니다. 예를 들어 조건자 선택도가 .05, .20 및 .25인 경우 카디널리티 예상치는 (.05 * SQRT(.20) * SQRT(SQRT(.25)))입니다.

예제 C. 새 카디널리티 예측은 서로 다른 테이블의 필터링된 조건자가 독립적이라고 가정합니다.

이 예제의 경우 이전 카디널리티 추정기는 조건자 필터 s.type 및 r.date의 상관 관계가 있다고 가정합니다. 그러나 최신 워크로드에 대한 테스트 결과에 따르면 서로 다른 테이블의 열에 대한 조건자 필터는 일반적으로 서로 상관 관계가 없는 것으로 나타났습니다.

SELECT s.ticket, s.customer, r.store FROM dbo.Sales AS s CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND s.type = 'toy' AND r.date = '2013-12-19';  

이 동작이 변경되었습니다. 이제 새 카디널리티 추정기 논리는 s.type이 r.date와 상관 관계가 없다고 가정합니다. 실질적으로 장난감은 특정 날뿐만 아니라 매일 반환된다는 가정이 있습니다. 이 경우 새 카디널리티 추정치는 이전 카디널리티 예상치보다 작은 수입니다.

또한 참조하십시오

성능 모니터링 및 튜닝