CE(카디널리티 추정) 피드백

아티클
06/24/2024

적용 대상: 적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL Database, Azure SQL Managed Instance.

SQL Server 2022(16.x)부터 카디널리티 추정(CE) 피드백은 지능형 쿼리 처리 기능 모음의 일부이며, 이러한 문제가 잘못된 CE 모델 가정 때문에 발생하는 경우 쿼리를 반복하기 위한 최적이 아닌 쿼리 실행 계획을 처리합니다. 이 시나리오는 이전 버전의 데이터베이스 엔진 업그레이드할 때 기본 CE와 관련된 회귀 위험을 줄이는 데 도움이 됩니다.

단일 CE 모델 및 가정 집합으로는 방대한 고객 워크로드 및 데이터 배포를 수용할 수 없으므로, CE 피드백은 쿼리 런타임 특성에 맞게 조정 가능한 솔루션을 제공합니다. CE 피드백은 지정된 쿼리 및 데이터 배포에 더 잘 맞는 모델 가정을 식별하고 사용하여 쿼리 실행 계획 품질을 개선합니다. 현재 CE 피드백은 추정된 행 수와 실제 행 수가 매우 다른 계획 연산자를 식별할 수 있습니다. 피드백은 중요한 모델 추정 오류가 발생하고 시도할 수 있는 대체 모델이 있을 때 적용됩니다.

다른 쿼리 피드백 기능은 메모리 부여 피드백 및 DOP(병렬 처리 수준) 피드백을 참조하세요.

CE(카디널리티 추정) 피드백 이해

CE(카디널리티 추정)는 쿼리 계획의 각 수준에서 처리되는 총 행 수를 쿼리 최적화 프로그램에서 추정하는 방법입니다. SQL Server의 카디널리티 추정은 수동 또는 자동으로 인덱스나 통계를 만들 때 생성되는 히스토그램에서 주로 파생됩니다. SQL Server는 제약 조건 정보와 논리적 쿼리 다시 작성을 통해 카디널리티를 결정하는 경우도 있습니다.

다양한 데이터베이스 엔진 버전은 데이터를 배포하고 쿼리하는 방법에 따라 서로 다른 CE 모델 가정을 사용합니다. 자세한 내용은 CE 버전을 참조하세요.

CE(카디널리티 추정) 피드백 구현

CE(카디널리티 추정) 피드백은 시간 경과에 따른 최적의 CE 모델 가정을 학습한 다음 가장 정확한 기존 가정을 적용합니다.

CE 피드백은 모델 관련 가정을 식별하고 쿼리를 반복하기에 적절한지 평가합니다.
가정이 올바르지 않다면 영향을 주는 CE 모델 가정을 조정하고 도움이 되는지 확인하는 쿼리 계획을 이용해 동일한 쿼리의 후속 실행을 테스트합니다. 계획 연산자에서 실제 행과 추정된 행을 확인하여 잘못을 식별합니다. CE 피드백에서 사용할 수 있는 모델 변형으로 모든 오류를 수정할 수 있는 것은 아닙니다.
계획 품질이 개선되면 이전 쿼리 계획이 쿼리 저장소 힌트 메커니즘을 통해 구현된 예측 모델을 조정하는 적절한 USE HINT 쿼리 힌트를 사용하는 쿼리 계획으로 바뀝니다.

확인된 피드백만 유지됩니다. 조정된 모델 가정 때문에 성능 저하가 발생하는 경우 CE 피드백은 관련 쿼리에 사용되지 않습니다. 이 컨텍스트에서는 사용자가 취소한 쿼리도 회귀로 인식됩니다.

CE(카디널리티 추정) 피드백 시나리오

CE(카디널리티 추정) 피드백은 기본 CE(CE120 이상)를 사용할 때 잘못된 CE 모델 가정 때문에 발생하는 인식된 회귀 문제를 해결하며, 다른 모델 가정을 선택적으로 사용할 수 있습니다. 시나리오에는 상관 관계, 조인 포함 및 최적화 프로그램 행 목표가 포함됩니다.

CE(카디널리티 추정) 피드백 상관 관계

지정된 테이블 또는 뷰에서 조건자의 선택도 또는 관련 조건자를 만족하는 행 수를 추정하는 경우, 쿼리 최적화 프로그램에서는 상관 관계 모델 가정을 사용합니다. 이러한 가정에서는 조건자가 다음일 수 있습니다.

완전 독립(CE70의 경우 기본값)인 경우 모든 조건자의 선택도를 곱하여 카디널리티를 계산합니다.
부분 상관 관계(CE120 이상의 경우 기본값)인 경우 카디널리티는 지수 백오프의 변형을 사용하여 계산하며, 선택도를 가장 많은 선택 조건자에서 적은 선택 조건자 순으로 정렬합니다.
완전 상관 관계인 경우 카디널리티는 모든 조건자의 최소 선택기를 사용하여 계산됩니다.

다음 예제에서는 데이터베이스 호환성이 120 이상으로 설정된 경우 부분 상관 관계를 사용합니다.

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

데이터베이스 호환성이 160으로 설정되고 기본 상관 관계를 사용하는 경우, CE 피드백은 예상 카디널리티가 실제 행 수에 비해 과소 평가되었는지 과대 평가되었는지에 따라 한 번에 한 단계씩 올바른 방향으로 상관 관계를 옮기려 합니다. 실제 행 수가 예상 카디널리티보다 큰 경우 완전 상관 관계를 사용합니다. 실제 행 수가 예상 카디널리티보다 작은 경우 완전 독립을 사용합니다.

자세한 내용은 CE 버전을 참조하세요.

CE(카디널리티 추정) 피드백 조인 포함

쿼리 최적화 프로그램에서 조인 조건자와 적용 가능한 필터 조건자의 선택도를 추정하는 경우에는 포함 모델 가정을 사용합니다. 이러한 추정은 다음으로 분류됩니다.

단순 포함(CE70의 경우 기본값)에서는 조인 조건자가 완전히 상관 관계가 있다고 가정하며, 필터 선택도를 먼저 계산한 다음 조인 선택도를 고려합니다.
기본 포함(CE120 이상의 경우 기본값)은 조인 조건자와 다운스트림 필터 간에 상관 관계가 없다고 가정하며, 조인 선택도가 먼저 계산된 다음 필터 선택도가 고려됩니다.

다음 예제에서는 데이터베이스 호환성이 120 이상으로 설정된 경우 기본 포함을 사용합니다.

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

자세한 내용은 CE 버전을 참조하세요.

CE(카디널리티 추정) 피드백 및 쿼리 최적화 프로그램 행 목표

실행 계획의 카디널리티를 예상할 때 쿼리 최적화 프로그램에서는 일반적으로 모든 테이블의 모든 정규화된 행을 처리해야 한다고 가정합니다. 그러나 일부 쿼리 패턴의 경우 쿼리 최적화 프로그램은 I/O를 줄이기 위해 더 적은 수의 행을 반환하는 계획을 검색합니다. 쿼리가 런타임에 TOP, IN 또는 EXISTS 키워드나 FAST 쿼리 힌트 또는 SET ROWCOUNT 문을 사용하여 예상할 수 있는 대상 행 수(행 목표)를 지정하는 경우, 이러한 행 목표는 다음 예제처럼 쿼리 최적화 프로세스의 일부로 사용됩니다.

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

행 목표 계획이 적용되면, 쿼리 최적화 프로그램에서 행 목표에 도달하기 위해 더 적은 수의 행을 처리해야 한다고 가정하므로 쿼리 계획의 예상 행 수가 줄어듭니다.

행 목표는 특정 쿼리 패턴에는 유익한 최적화 전략이지만, 데이터가 균일하게 분산되지 않으면 예상보다 더 많은 페이지를 검색하기 때문에 행 목표가 비효율적인 목표가 됩니다. CE 피드백은 행 목표 검색을 사용하지 않도록 설정하고 이러한 비효율성이 감지되면 검색을 사용하도록 설정할 수 있습니다.

실행 계획에는 CE 피드백과 관련된 특성이 없지만 쿼리 저장소 힌트에 대한 특성이 나열됩니다. QueryStoreStatementHintSource가 CE feedback인 것을 찾습니다.

CE(카디널리티 추정) 피드백에 대한 고려 사항

CE(카디널리티 추정) 피드백을 사용하도록 설정하려면 쿼리를 실행할 때 연결된 데이터베이스의 데이터베이스 호환성 수준 160을 사용하도록 설정합니다. CE 피드백을 사용하는 모든 데이터베이스에 대해 쿼리 저장소를 활성화하고 읽기/쓰기 모드로 설정해야 합니다.
데이터베이스 수준에서 CE 피드백을 사용하지 않도록 설정하려면 CE_FEEDBACK 데이터베이스 범위 구성을 사용합니다. 예를 들어 사용자 데이터베이스에서 다음을 실행합니다.
```
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
```
쿼리 수준에서 CE 피드백을 사용하지 않도록 설정하려면 DISABLE_CE_FEEDBACK 쿼리 힌트를 사용합니다.

CE 피드백 활동은 query_feedback_analysis 및 query_feedback_validation XEvents를 통해 볼 수 있습니다.

CE 피드백에서 설정한 힌트는 sys.query_store_query_hints 카탈로그 뷰를 사용하여 추적할 수 있습니다.

피드백 정보는 sys.query_store_plan_feedback 카탈로그 뷰를 사용하여 추적할 수 있습니다.

쿼리에 쿼리 저장소 통해 강제 적용하는 쿼리 계획이 있다면, CE 피드백은 해당 쿼리에 사용하지 않습니다.

쿼리가 하드 코딩된 쿼리 힌트를 사용하거나 사용자가 설정한 쿼리 저장소 힌트를 사용하는 경우, CE 피드백은 해당 쿼리에 사용하지 않습니다. 자세한 내용은 쿼리 힌트 및 쿼리 저장소 힌트를 참조하세요.

SQL Server 2022(16.x)부터 보조 복제본의 쿼리 저장소를 사용하도록 설정할 경우 CE 피드백도 가용성 그룹의 보조 복제본에 대한 복제본을 인식하지 못합니다. CE 피드백은 현재 주 복제본 혜택만 있습니다. 장애 조치 시 주 복제본 또는 보조 복제본에 적용된 피드백이 손실됩니다. 자세한 내용은 보조 복제본을 위한 쿼리 저장소를 참조하세요.

CE(카디널리티 추정) 피드백의 지속성

적용 대상: 적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL Database, Azure SQL Managed Instance.

CE(카디널리티 추정) 피드백은 행 목표 최적화를 유지해야 하는 시나리오를 감지하고 쿼리 저장소 힌트의 형태로 쿼리 저장소 유지하여 이 변경을 유지할 수 있습니다. 새 최적화는 나중에 쿼리를 실행하는 데 사용됩니다. CE 피드백은 피드백 시나리오에 자세히 설명된 대로 행 목표 최적화 쿼리 패턴 이외의 다른 시나리오에서도 지속됩니다. 현재 CE 피드백은 CE의 상관 관계 모델에서 사용되는 조건자 선택 시나리오와 CE의 포함 모델에서 처리되는 조인 조건자 시나리오를 처리합니다.

이 기능은 SQL Server 2022(16.x)에서 도입되지만 이 성능 개선은 데이터베이스 호환성 수준 160 이상 또는 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 힌트 160 이상에서 작동하는 쿼리에 대해, 쿼리 저장소가 데이터베이스에서 사용되도록 설정하고 "읽기 쓰기" 상태인 경우에 사용할 수 있습니다.

CE(카디널리티 추정) 피드백의 알려진 문제

문제	발견된 날짜	상태	해결된 날짜
특정 조건에서 SQL Server 2022(16.x)용 누적 업데이트 8을 적용한 후 SQL Server 성능이 저하됩니다. CE 피드백을 활성화할 경우 CPU 사용률이 예기치 않게 증가하면서 계획 캐시 메모리 사용률이 크게 증가할 수 있습니다.	2023년 12월	해결됨	2024년 4월 22일(CU 12)

알려진 문제 세부 정보

특정 조건에서 SQL Server 2022용 누적 업데이트 8을 적용한 후 SQL Server 성능 저하

SQL Server 2022(16.x) 누적 업데이트 8부터 SQL Server는 CPU 및 메모리 사용률이 예기치 않게 증가할 수 있습니다. 또한 RESOURCE_SEMAPHORE_QUERY_COMPILE 대기의 증가도 관찰될 수 있습니다. 또한 사용 중인 계획 캐시 개체의 수가 꾸준히 증가하여 계획 캐시 한도에 근접하는 것을 확인할 수 있으며 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE 또는 DBCC FREEPROCCACHE와 같은 기술을 사용하여 수동으로 계획 캐시를 지우는 것은 도움이 되지 않습니다. 이 동작은 소수의 고객만 관찰했습니다.

이 문제는 모든 워크로드에 영향을 주지 않으며, 생성된 다양한 계획의 수와 CE 피드백 기능을 사용할 수 있는 계획의 수에 따라 달라집니다. CE 피드백이 잘못 추정된 심각한 모델의 계획 연산자를 분석하는 동안 이 분석 단계에서 참조된 계획을 역참조할 수 있는 시나리오가 있습니다. 이 경우 일반적인 최소 사용(LRU) 알고리즘을 사용하여 계획이 메모리에서 제거되지 않습니다. SQL Server에서 계획 제거 정책을 적용하는 한 가지 방법인 LRU 메커니즘입니다. 또한 시스템이 메모리 압력을 받고 있는 경우 SQL Server는 메모리에서 계획을 제거합니다. SQL Server가 잘못 역참조된 계획을 제거하려고 하면 계획 캐시에서 해당 계획을 제거할 수 없으므로 캐시가 계속 증가합니다. 캐시가 증가하면 추가 컴파일이 시작되어 궁극적으로 더 많은 CPU와 메모리를 사용할 수 있습니다. 자세한 내용은 계획 캐시 내부를 참조하세요.

증상: 사용 중인 계획 캐시 항목의 수가 SQL 계획 또는 개체 계획에서 더티로 표시되면 시간이 지남에 따라 50,000개 이상으로 증가합니다. 계획 캐시 항목이 이 수준에 근접하기 시작하고 CPU 사용률이 예기치 않게 증가한다면 시스템에서 이 문제가 발생하고 있는 것일 수 있습니다. SQL Server 2022(16.x) 누적 업데이트 12에서 수정 사항이 제공됩니다. KB5033663을 참조하십시오.

시스템에서 사용 중인 계획 캐시 항목의 수를 모니터링하려면 다음 예를 사용하여 존재하는 계획 캐시 항목의 수를 특정 시점 보기로 볼 수 있습니다. 예를 들어 시간이 지남에 따라 더티로 표시된 계획 캐시 항목 수를 주기적으로 관찰하는 것도 이 현상을 모니터링하는 한 가지 방법입니다.

SELECT
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END AS PlanType,
  COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp
  ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
AND ecp.bucketid is NULL
GROUP BY
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END;

이전 예제와 동일한 정보를 제공하면서 추가 성능 메트릭을 관찰할 수 있는 또 다른 쿼리 세트도 있습니다. 계획 캐시 적중 비율은 감소하고 초당 일괄 처리 요청 수와 관련하여 컴파일 수가 감소합니다. 다음 쿼리를 사용하여 시간이 지남에 따라 시스템을 모니터링할 수 있습니다. 캐시 적중률(예상치 못한 급락), 사용 중인 캐시 개체(감소하지 않고 50,000개에 근접하는 수준까지의 개수 증가) 및 초당 컴파일 수의 증가에 비해 예상보다 낮은 초당 배치 요청 비율을 확인합니다.

--SQL Plan (Adhoc and Prepared plans)
SELECT
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (SQL Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value
        FROM sys.dm_os_performance_counters WHERE
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (Object Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value
        FROM sys.dm_os_performance_counters WHERE
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT
    CASE
        WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
        WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
    END AS [SQLServer:SQL Statistics],
    FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec'
);

해결 방법

누적 업데이트 12 KB5033663을 적용한 후에도 시스템에서 앞서 설명한 증상이 계속 발생하는 경우 데이터베이스 수준에서 CE 피드백 기능을 비활성화할 수 있습니다.

이 문제로 인해 수행된 계획 캐시 메모리를 회수하려면 SQL Server 인스턴스를 다시 시작해야 합니다. 이 재시작 작업은 CE 피드백 기능을 비활성화한 후에 수행할 수 있습니다. 데이터베이스 수준에서 CE 피드백을 사용하지 않도록 설정하려면 CE_FEEDBACK 데이터베이스 범위 구성을 사용합니다. 예를 들어 사용자 데이터베이스에서 다음을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

피드백 및 문제 보고

피드백이나 질문이 있다면 CEFfeedback@microsoft.com으로 이메일을 보내주세요

다음을 통해 공유

CE(카디널리티 추정) 피드백

CE(카디널리티 추정) 피드백 이해

CE(카디널리티 추정) 피드백 구현

CE(카디널리티 추정) 피드백 시나리오

CE(카디널리티 추정) 피드백 상관 관계

CE(카디널리티 추정) 피드백 조인 포함

CE(카디널리티 추정) 피드백 및 쿼리 최적화 프로그램 행 목표

CE(카디널리티 추정) 피드백에 대한 고려 사항

CE(카디널리티 추정) 피드백의 지속성

CE(카디널리티 추정) 피드백의 알려진 문제

알려진 문제 세부 정보

특정 조건에서 SQL Server 2022용 누적 업데이트 8을 적용한 후 SQL Server 성능 저하

해결 방법

피드백 및 문제 보고

피드백

추가 리소스

다음을 통해 공유

CE(카디널리티 추정) 피드백

CE(카디널리티 추정) 피드백 이해

CE(카디널리티 추정) 피드백 구현

CE(카디널리티 추정) 피드백 시나리오

CE(카디널리티 추정) 피드백 상관 관계

CE(카디널리티 추정) 피드백 조인 포함

CE(카디널리티 추정) 피드백 및 쿼리 최적화 프로그램 행 목표

CE(카디널리티 추정) 피드백에 대한 고려 사항

CE(카디널리티 추정) 피드백의 지속성

CE(카디널리티 추정) 피드백의 알려진 문제

알려진 문제 세부 정보

특정 조건에서 SQL Server 2022용 누적 업데이트 8을 적용한 후 SQL Server 성능 저하

해결 방법

피드백 및 문제 보고

관련 콘텐츠

피드백

추가 리소스