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

  • 아티클

적용 대상: SQL Server 2022(16.x) 이상

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

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

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

카드 CE(비인도 추정) 피드백 이해

CE(카디널리티 추정)는 쿼리 최적화 프로그램에서 쿼리 계획의 각 수준에서 처리된 총 행 수를 예측할 수 있는 방법입니다. SQL Server의 카디널리티 추정은 수동 또는 자동으로 인덱스나 통계를 만들 때 생성되는 히스토그램에서 주로 파생됩니다. 경우에 따라 SQL Server는 쿼리의 제약 조건 정보 및 논리적 재작성도 사용하여 카드 비인간성을 확인합니다.

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

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

CE(카디널리티 추정) 피드백은 시간에 따라 최적의 CE 모델 가정을 학습한 다음, 역사적으로 가장 올바른 가정을 적용합니다.

  1. CE 피드백은 모델 관련 가정을 식별하고 쿼리를 반복하기에 적절한지 평가합니다.

  2. 가정이 올바르지 않다면 영향을 주는 CE 모델 가정을 조정하고 도움이 되는지 확인하는 쿼리 계획을 이용해 동일한 쿼리의 후속 실행을 테스트합니다. 계획 연산자에서 실제 행과 예상 행을 확인하여 잘못을 식별합니다. CE 피드백에서 사용할 수 있는 모델 변형으로 모든 오류를 수정할 수 있는 것은 아닙니다.

  3. 계획 품질이 개선되면 이전 쿼리 계획이 쿼리 저장소 힌트 메커니즘을 통해 구현된 예측 모델을 조정하는 적절한 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에 예상할 수 있는 행 수(행 목표)를 지정하거나 EXISTS 키워드(keyword) IN 쿼리 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 피드백이 사용되는 모든 데이터베이스에 대해 READ_WRITE 모드에서 쿼리 저장소 사용하도록 설정해야 합니다.

  • 데이터베이스 수준에서 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 피드백은 해당 쿼리에 사용하지 않습니다. 자세한 내용은 힌트(Transact-SQL) - 쿼리 및 쿼리 저장소 힌트를 참조하세요.

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

카드 CE(비인도 추정) 피드백에 대한 지속성

적용 대상: SQL Server(SQL Server 2022(16.x)부터)

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월 해결 방법 사용

알려진 문제 세부 정보

특정 조건에서 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_CACHEDBCC FREESYSTEMCACHE는 데 사용하는 계획 캐시 개체의 수가 꾸준히 증가하거나 DBCC FREEPROCCACHE 지원을 제공하지 않는 것을 알 수 있습니다. 이 동작은 소수의 고객만 관찰했습니다.

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

증상: 사용 중인 계획 캐시 항목의 수가 SQL 계획 또는 개체 계획에서 더티 표시되면 시간이 지남에 따라 50,000개 이상으로 증가합니다. 예기치 않은 CPU 사용률 증가와 함께 이 수준에 접근하기 시작하는 계획 캐시 항목을 관찰하는 경우 시스템에서 이 문제가 발생할 수 있습니다. SQL Server 2022(16.x) 누적 업데이트 9에서 관련 수정 사항이 제공되었습니다. KB5030731 참조하세요. CE(카디널리티 추정) 피드백이 연결된 프로필을 가져오려고 할 때 계획 캐시 항목이 제거되어 메모리가 손상되는 문제를 해결하기 위해 수정되었습니다. 이 문제에 대한 추가 수정 사항은 향후 누적 업데이트에서 사용할 수 있습니다.

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

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개에 근접하는 수준까지의 개수 증가) 및 컴파일 수/초의 증가에 비해 예상보다 낮은 Batch 요청/초 비율을 확인합니다.

--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' 
);

해결 방법: 시스템에서 이전에 설명한 증상이 발생하는 경우 추가 수정 사항을 사용할 수 있을 때까지 데이터베이스 수준에서 CE 피드백 기능을 사용하지 않도록 설정할 수 있습니다. 이 문제로 인해 수행된 계획 캐시 메모리를 회수하려면 SQL Server 인스턴스를 다시 시작해야 합니다. CE 피드백 기능을 사용하지 않도록 설정한 후에 이 다시 시작 작업을 수행할 수 있습니다. 데이터베이스 수준에서 CE 피드백을 사용하지 않도록 설정하려면 데이터베이스 범위 구성을 CE_FEEDBACK사용합니다. 예를 들어 사용자 데이터베이스에서 다음을 수행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

피드백 및 보고 문제

피드백 또는 질문, 전자 메일 CEFfeedback@microsoft.com

관련 콘텐츠