카디널리티 추정(SQL Server)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server 쿼리 최적화 프로그램은 비용 기반 쿼리 최적화 프로그램입니다. 즉, 가장 낮은 예상 처리 비용으로 실행할 수 있는 쿼리 계획을 선택합니다. 쿼리 최적화 프로그램은 두 가지 기본 요인에 따라 쿼리 계획을 실행하는 비용을 결정합니다.

• 쿼리 계획의 각 수준에서 처리된 총 행 수(계획의 카드 불가)입니다.
• 쿼리에 사용되는 연산자가 지정한 알고리즘의 비용 모델입니다.

첫 번째 요소인 카드inality는 두 번째 요소인 비용 모델의 입력 매개 변수로 사용됩니다. 따라서 카드 개선되면 예상 비용이 향상되고 실행 계획이 빨라집니다.

SQL Server의 CE(카디널리티 추정)는 주로 인덱스 또는 통계를 만들 때 수동으로 또는 자동으로 생성되는 히스토그램에서 파생됩니다. 경우에 따라 SQL Server는 쿼리의 제약 조건 정보 및 논리적 재작성도 사용하여 카드 비인간성을 확인합니다.

다음 경우에는 SQL Server에서 카디널리티를 정확하게 계산할 수 없습니다. 이로 인해 쿼리 계획이 최적이 아닐 수 있는 부정확한 비용 계산이 발생합니다. 쿼리에서 이러한 구문을 방지하면 쿼리 성능이 향상될 수 있습니다. 경우에 따라 대체 쿼리 공식 또는 기타 측정값이 가능하며 다음과 같이 지적됩니다.

• 동일한 테이블의 서로 다른 열 간에 비교 연산자를 사용하는 조건자가 있는 쿼리입니다.
• 연산자를 사용하는 조건자와 다음 중 하나를 사용하는 쿼리는 true입니다.
  • 연산자의 양쪽에 관련된 열에 대한 통계는 없습니다.
  • 통계의 값 분포가 균일하지 않지만 쿼리가 매우 선택적인 값 집합을 찾습니다. 연산자가 등호(=)가 아닐 때 특히 이 경우에 해당할 수 있습니다.
  • 조건자는 (!=) 비교 연산자 또는 논리 연산자와 같지 않은 연산자를 NOT 사용합니다.
• SQL Server 기본 제공 함수 또는 인수가 상수 값이 아닌 사용자 정의 스칼라 반환 함수를 사용하는 쿼리.
• 산술 연산자 또는 문자열 연결 연산자를 통해 열을 조인하는 쿼리입니다.
• 쿼리가 컴파일되고 최적화될 때 알 수 없는 값을 가진 변수를 비교하는 쿼리.

이 문서에서는 시스템에 가장 적합한 CE 구성을 평가하고 선택하는 방법을 보여 줍니다. 대부분의 시스템에서는 가장 정확한 최신 CE를 활용합니다. CE는 쿼리가 반환할 가능성이 있는 행 수를 예측합니다. 카드 예측은 쿼리 최적화 프로그램에서 최적의 쿼리 계획을 생성하는 데 사용됩니다. 보다 정확한 추정을 통해 쿼리 최적화 프로그램은 일반적으로 최적의 쿼리 계획을 생성하는 작업을 더 잘 수행할 수 있습니다.

애플리케이션 시스템에는 전체 버전의 CE 변경으로 인해 계획이 더 느린 계획으로 변경되는 중요한 쿼리가 있을 수 있습니다. CE 문제로 인해 느리게 수행되는 쿼리를 식별하는 기술과 도구가 있습니다. 또한 성능 문제를 해결하는 방법에 대한 옵션도 있습니다.

CE 버전

1998년 CE의 주요 업데이트는 호환성 수준이 70인 SQL Server 7.0의 일부였습니다. 이 버전의 CE 모델은 다음 네 가지 기본 가정으로 설정됩니다.

• 독립성: 상관 관계 정보를 사용할 수 있고 사용할 수 없는 한 서로 다른 열의 데이터 배포는 서로 독립적인 것으로 간주됩니다.

• 균일성: 고유 값은 간격이 일정하고 빈도가 동일합니다. 보다 정확하게 말하면 각 히스토그램 단계 내에서 고유 값이 균등하게 분산되고 각 값의 빈도가 동일합니다.

• 포함(단순): 사용자가 존재하는 데이터를 쿼리합니다. 예를 들어 두 테이블 간의 같음 조인의 경우 히스토그램을 조인하여 조인 선택성을 예측하기 전에 각 입력 히스토그램의 조건자 선택도¹ 을 인수합니다.

• 포함: 필터 조건자의 Column = Constant경우 연결된 열에 대해 상수가 실제로 존재하는 것으로 간주됩니다. 해당 히스토그램 단계가 비어 있지 않은 경우 단계의 고유 값 중 하나가 조건자의 값과 일치하는 것으로 간주됩니다.

  ^{조건자를 충족하는 1} 개 행 수입니다.

이후 업데이트는 SQL Server 2014(12.x)로 시작되었으며, 이는 호환성 수준 120 이상을 의미합니다. 수준 120 이상에 대한 CE 업데이트는 최신 데이터 웨어하우징 및 OLTP 워크로드에서 잘 작동하는 업데이트된 가정 및 알고리즘을 통합합니다. CE 70 가정에서 CE 120부터 다음 모델 가정이 변경되었습니다.

• 독립 성 상관 관계가 됩니다. 서로 다른 열 값의 조합이 반드시 독립적인 것은 아닙니다. 이는 실제 데이터 쿼리와 유사할 수 있습니다.
• 단순 포함 은 기본 포함이 됩니다. 사용자가 존재하지 않는 데이터를 쿼리할 수 있습니다. 예를 들어 두 테이블 간의 동등 조인의 경우 기본 테이블 히스토그램을 사용하여 조인 선택도를 예측한 다음, 조건자 선택도의 요소를 예측합니다.

쿼리 저장소를 사용하여 CE 버전 평가

SQL Server 2016(13.x)부터 쿼리 저장소 쿼리 성능을 검사하는 데 편리한 도구입니다. 쿼리 저장소 사용하도록 설정되면 실행 계획이 변경되더라도 시간이 지남에 따라 쿼리 성능을 추적하기 시작합니다. 높은 비용 또는 회귀 쿼리 성능에 대한 쿼리 저장소 모니터링합니다. 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.

SQL Server로 업그레이드를 준비하거나 SQL Server 플랫폼에서 데이터베이스 호환성 수준을 승격하는 경우 쿼리 튜닝 도우미를 사용하여 데이터베이스를 업그레이드하는 것이 좋습니다. 그러면 두 가지 호환성 수준에서 쿼리 성능을 비교할 수 있습니다.

확장 이벤트를 사용하여 CE 버전 평가

카드inality 추정 프로세스를 추적하는 또 다른 옵션은 명명query_optimizer_estimate_cardinality된 확장 이벤트를 사용하는 것입니다. 다음 Transact-SQL 코드 샘플은 SQL Server에서 실행됩니다. 경로를 변경할 수 있지만 .xel 파일을 C:\Temp\ 씁니다. Management Studio에서 .xel 파일을 열면 세부 정보가 사용자에게 친숙한 방식으로 표시됩니다.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

SQL Database에 맞게 조정된 확장 이벤트에 대한 자세한 내용은 SQL Database의 확장 이벤트를 참조하세요.

CE 버전을 평가하는 단계

다음은 가장 중요한 쿼리가 최신 CE에서 더 나쁜 성능을 발휘하는지 여부를 평가하는 데 사용할 수 있는 단계입니다. 일부 단계는 이전 섹션에 제시된 코드 샘플을 실행하여 수행됩니다.

1. SSMS(SQL Server Management Studio)를 엽니다. SQL Server 데이터베이스가 가장 높은 호환성 수준으로 설정되어 있는지 확인합니다.

2. 다음 예비 단계를 수행합니다.

   1. SSMS(SQL Server Management Studio)를 엽니다.

   2. Transact-SQL을 실행하여 SQL Server 데이터베이스가 사용 가능한 가장 높은 호환성 수준으로 설정되었는지 확인합니다.

   3. 데이터베이스의 구성이 LEGACY_CARDINALITY_ESTIMATION 꺼져 있는지 확인합니다.

   4. 쿼리 저장소 지우세요. 데이터베이스에서 쿼리 저장소 ON인지 확인합니다.

   5. SET NOCOUNT OFF; 문을 실행합니다.

3. SET STATISTICS XML ON; 문을 실행합니다.

4. 중요한 쿼리를 실행합니다.

5. 결과 창의 메시지 탭에서 영향을 받는 행의 실제 수를 확인합니다.

6. 결과 창의 결과 탭에서 XML 형식의 통계가 포함된 셀을 두 번 클릭합니다. 그래픽 쿼리 계획이 표시됩니다.

7. 그래픽 쿼리 계획의 첫 번째 상자를 마우스 오른쪽 단추로 클릭하고 속성을 선택합니다.

8. 나중에 다른 구성과 비교하려면 다음 속성에 대한 값을 확인합니다.

   • CardinalityEstimationModelVersion.

   • 예상 행 수입니다.

   • 예상 I/O 비용 및 행 개수 예측이 아닌 실제 성능을 포함하는 몇 가지 유사한 예상 속성입니다.

   • 논리 연산 및 물리적 연산입니다. 병렬 처리 는 좋은 값입니다.

   • 실제 실행 모드입니다. 행 보다 일괄 처리로 설정하는 것이 좋습니다.

9. 예상 행 수와 실제 행 수를 비교합니다. CE가 1%(높거나 낮음) 또는 10% 정확하지 않은가요?

10. SET STATISTICS XML OFF;를 실행합니다.

11. Transact-SQL을 실행하여 데이터베이스 호환성 수준을 한 수준(예: 130 -> 120) 낮춥니다.

12. 예비 단계가 아닌 모든 단계를 다시 실행합니다.

13. 두 실행의 CE 속성 값을 비교합니다.

    • 최신 CE의 부정확성 비율이 이전 CE보다 작습니까?

14. 마지막으로 두 실행의 다양한 성능 속성 값을 비교합니다.

    • 쿼리가 서로 다른 두 CE 예측에 따라 다른 계획을 사용했나요?

    • 최신 CE에서 쿼리 실행 속도가 느려지나요?

    • 이전 CE의 다른 계획을 사용할 때 쿼리 실행률이 더 향상되지 않는 한 대부분의 경우 최신 CE를 사용하는 것이 좋습니다.

    • 그러나 쿼리가 이전 CE에서 더 빠른 계획으로 실행되는 경우 시스템에서 더 빠른 계획을 사용하고 CE를 무시하도록 강제하는 것이 좋습니다. 이러한 방식으로 모든 쿼리에 최신 CE를 사용하면서 필요한 경우 더 빠른 계획을 유지할 수 있습니다.

최상의 쿼리 계획을 활성화하는 방법

CE 120 이상인 경우 쿼리에 대해 더 느린 쿼리 계획이 생성된다고 가정합니다. 다음은 가장 큰 범위에서 가장 작은 범위로 정렬된 더 나은 계획을 활성화해야 하는 몇 가지 옵션입니다.

• 전체 데이터베이스에 대해 데이터베이스 호환성 수준을 사용 가능한 최신 값보다 낮게 설정할 수 있습니다.

  • 예를 들어 호환성 수준 110 이하를 설정하면 CE 70이 활성화되지만 모든 쿼리는 이전 CE 모델의 적용을 받습니다.

  • 뿐만 아니라 더 낮은 호환성 수준으로 설정할 경우 최신 버전에 대한 쿼리 최적화 프로그램의 다양한 개선 사항이 누락되고 데이터베이스에 대한 모든 쿼리에 영향을 주게 됩니다.

• 데이터베이스 범위 구성 옵션을 사용하여 LEGACY_CARDINALITY_ESTIMATION 쿼리 최적화 프로그램의 다른 향상된 기능을 유지하면서 전체 데이터베이스에서 이전 CE를 사용하도록 할 수 있습니다.

• 쿼리 힌트를 사용하여 LEGACY_CARDINALITY_ESTIMATION 쿼리 최적화 프로그램의 다른 개선 사항을 유지하면서 단일 쿼리가 이전 CE를 사용하도록 할 수 있습니다.

• 쿼리 저장소 힌트 기능을 통해 LEGACY_CARDINALITY_ESTIMATION을 적용하여 쿼리를 변경하지 않고 단일 쿼리에서 이전 CE를 사용하도록 할 수 있습니다.

• 쿼리 저장소를 사용하여 다른 계획을 강제 적용합니다.

데이터베이스 호환성 수준

COMPATIBILITY_LEVEL에 대해 다음 Transact-SQL 코드를 사용하여 데이터베이스가 특정 수준에 있는지 확인할 수 있습니다.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO  

더 낮은 호환성 수준에서 실행되는 기존 데이터베이스의 경우 애플리케이션이 더 높은 데이터베이스 호환성 수준에서만 사용할 수 있는 향상된 기능을 사용할 필요가 없으면 이전 데이터베이스 호환성 수준을 기본 수 있는 유효한 방법입니다. 새로운 개발 작업을 수행하는 경우 또는 기존 애플리케이션에 지능형 쿼리 처리 및 새로운 Transact-SQL 같은 새 기능을 사용해야 하는 경우, 데이터베이스 호환성 수준을 사용 가능한 최신 수준으로 업그레이드하세요. 자세한 내용은 호환성 수준 및 데이터베이스 엔진 업그레이드를 참조하세요.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO  

호환성 수준 120 이상으로 설정된 SQL Server 데이터베이스의 경우 추적 플래그 9481을 활성화하면 시스템에서 CE 버전 70이 사용됩니다.

레거시 카디널리티 예측 도구

호환성 수준 120 이상에서 설정된 SQL Server 데이터베이스의 경우 ALTER DATABASE SCOPED CONFIGURATION을 사용하여 레거시 카드INALITY Estimator(CE 버전 70)를 데이터베이스 수준에서 활성화할 수 있습니다.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

힌트를 사용하도록 쿼리 수정

SQL Server 2016(13.x) SP1부터 쿼리 힌트를 사용하도록 쿼리를 수정합니다(USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')).

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

쿼리 저장소 힌트 설정

쿼리는 쿼리 저장소 힌트를 사용하여 쿼리를 수정하지 않고 레거시 카드inality 추정기를 사용하도록 강제할 수 있습니다.

1. sys.query_store_query_text 및 sys.query_store_query 쿼리 저장소 카탈로그 뷰에서 쿼리를 식별합니다. 예를 들어 텍스트 조각별로 실행된 쿼리를 검색합니다.

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt 
   INNER JOIN sys.query_store_query q ON 
   qt.query_text_id = q.query_text_id 
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
   AND query_sql_text not like N'%query_store%';
   

2. 다음 예제에서는 쿼리를 수정하지 않고 레거시 카드inality estimator를 39에 query_id 강제로 적용하는 쿼리 저장소 힌트를 적용합니다.

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

특정 쿼리 계획을 강제로 적용하는 방법

최상의 제어를 위해 테스트 중 시스템에서 CE 70을 사용하여 생성된 계획을 사용하도록 적용할 수 있습니다. 기본 계획을 고정한 후에는 최신 호환성 수준 및 CE를 사용하도록 전체 데이터베이스를 설정할 수 있습니다. 옵션은 다음에 자세하게 설명합니다.

이 쿼리 저장소 시스템에서 특정 쿼리 계획을 사용하도록 강제 적용할 수 있는 다양한 방법을 제공합니다.

• sys.sp_query_store_force_plan를 실행합니다.

• SSMS(SQL Server Management Studio)에서 쿼리 저장소 노드를 확장하고 리소스를 가장 많이 사용하는 노드를 마우스 오른쪽 단추로 클릭한 다음 리소스를 가장 많이 사용하는 노드 보기를 선택합니다. 계획 강제 적용 및 계획 강제 적용 해제라는 레이블이 있는 단추가 표시됩니다.

쿼리 저장소 대한 자세한 내용은 쿼리 저장소 사용하여 성능 모니터링을 참조하세요.

카디널리티 예측 중 상수 접기 및 식 평가

데이터베이스 엔진 쿼리 성능을 향상시키기 위해 일부 상수 식을 일찍 평가합니다. 이를 상수 접기라고 합니다. 상수는 3, 'ABC', '2005-12-31', 1.0e3 또는 0x12345678 같은 Transact-SQL 리터럴입니다. 자세한 내용은 상수 접기(Constant Folding)를 참조 하세요.

뿐만 아니라 상수 폴딩형은 아니지만 해당 인수를 컴파일 시간에 알 수 있는 일부 식은 최적화 중에 쿼리 최적화 프로그램의 일부인 결과 집합 크기(카디널리티) 예측 도구에서 평가되며, 이때 인수가 매개 변수인지 또는 상수인지는 상관없습니다. 자세한 내용은 식 평가를 참조하세요.

모범 사례: 최적의 쿼리 계획을 생성하기 위해 상수 폴딩 및 컴파일 시간 식 평가 사용

최적의 쿼리 계획을 생성하려면 쿼리 최적화 프로그램에서 해당 데이터 배포에 대한 통계를 기준으로 쿼리 내 조건 선택도를 정확하게 예측할 수 있도록 쿼리, 저장 프로시저, 일괄 처리를 설계하는 것이 가장 좋습니다. 그렇지 않으면 선택성을 예측할 때 쿼리 최적화 프로그램에서 기본 예상치를 사용해야 합니다.

쿼리 최적화 프로그램의 카디널리티 예측 도구가 적절한 예상치를 제공하는지 확인하려면 먼저 AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS 데이터베이스 SET 옵션이 ON(기본 설정)인지 확인하거나 쿼리 조건에서 참조되는 모든 열에 대한 통계를 수동으로 만들어야 합니다. 그런 다음 쿼리에서 조건을 디자인할 때 다음을 수행합니다.

• 쿼리에서 지역 변수를 사용하지 않도록 합니다. 대신 쿼리에서 매개 변수, 리터럴 또는 식을 사용합니다.

• 매개 변수가 포함된 쿼리에 포함된 연산자와 함수의 사용을 카디널리티 예측에 대한 컴파일 시간 식 평가 아래에 나열된 연산자 및 함수의 사용을 제한합니다.

• 쿼리 조건의 상수 전용 식이 상수 폴딩 가능 또는 컴파일 시간에 계산될 수 있는지 확인합니다.

• 로컬 변수를 사용하여 쿼리에 사용할 식을 평가해야 하는 경우 쿼리와 다른 범위에서 계산하는 것이 좋습니다. 예를 들어 다음 옵션 중 하나를 수행하는 것이 유용할 수 있습니다.

  • 평가하려는 쿼리가 포함된 저장 프로시저에 변수 값을 전달하고 쿼리에서 지역 변수 대신 프로시저 매개 변수를 사용하도록 합니다.

  • 지역 변수의 값에 부분적으로 기반한 쿼리를 포함하는 문자열을 생성한 다음 동적 SQL(EXEC 또는 바람직하게는)을 사용하여 문자열을 실행합니다 sp_executesql.

  • 쿼리를 매개 변수화하고, 변수 sp_executesql값을 쿼리에 매개 변수로 전달하여 실행합니다.

CE 개선 사례

이 섹션에서는 최근 릴리스에서 CE에서 구현된 향상된 기능을 활용하는 예제 쿼리에 대해 설명합니다. 사용자가 특정 작업을 수행할 필요가 없는 배경 정보입니다.

예제 A. CE는 통계가 마지막으로 수집되었을 때보다 최대값이 높을 수 있음을 이해합니다.

최대 OrderAddedDate 값이 었으면 on에 2016-04-30대한 OrderTable 통계가 마지막으로 수집되었다고 2016-04-30가정해 보겠습니다. CE 120(이상 버전)에서는 오름차순 데이터를 가진 OrderTable의 열에 통계에 의해 기록된 최대값보다 더 큰 값이 있을 수 있다는 것을 이해합니다. 이러한 인식은 다음과 같은 Transact-SQL SELECT 문에 대한 쿼리 계획을 향상시킵니다.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';  

예제 B. CE는 동일한 테이블의 필터링된 조건자가 종종 상관 관계가 있음을 이해합니다.

다음 SELECT에서는 Model 및 ModelVariant에 대한 필터링된 예측이 있습니다. Xbox에 One이라는 변형이 있다면 Model이 'Xbox'일 때 ModelVariant가 'One'일 가능성이 있음을 직관적으로 이해할 수 있습니다.

CE 120부터 SQL Server는 동일한 테이블에 ModelModelVariant있는 두 열 간에 상관 관계가 있을 수 있음을 이해합니다. CE는 쿼리에서 반환할 행 수를 보다 정확하게 추정하고 쿼리 최적화 프로그램은 보다 최적의 계획을 생성합니다.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';  

예제 C. CE는 더 이상 다른 테이블에서 필터링된 조건자 간의 상관 관계를 가정하지 않습니다.

최신 워크로드 및 실제 비즈니스 데이터에 대한 광범위하고 새로운 연구 결과, 서로 다른 테이블의 예측 필터는 보통 서로 상호 연결되지 않습니다. 다음 쿼리에서 CE는 s.type 및 r.date 간에 상관 관계가 없다고 간주합니다. 따라서 CE는 반환되는 행 수를 더 낮게 예측합니다.

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 = '2016-05-11';  

관련 콘텐츠

• CE(카디널리티 추정) 피드백
• 성능 모니터링 및 튜닝
• SQL Server 2014 카디널리티 추정기로 쿼리 계획 최적화
• 힌트(Transact-SQL) - 쿼리
• USE HINT 쿼리 힌트
• 쿼리 튜닝 도우미를 사용하여 데이터베이스 업그레이드
• 쿼리 저장소를 사용하여 성능 모니터링
• 쿼리 처리 아키텍처 가이드
• 쿼리 저장소 힌트
• SQL 데이터베이스의 인텔리전트 쿼리 처리
• DBCC TRACEON - 추적 플래그(Transact-SQL)
• sys.query_store_query_hints