Share via

SQL Server 2012년 이전에서 2014년 이상으로 업그레이드한 후 쿼리 성능 저하

  • 아티클

2012년 또는 이전 버전에서 2014 이상 버전으로 SQL Server 업그레이드한 후 다음과 같은 문제가 발생할 수 있습니다. 대부분의 원래 쿼리는 잘 실행되지만 일부 쿼리는 이전 버전보다 느리게 실행됩니다. 가능한 원인과 기여 요인이 많지만 비교적 일반적인 원인 중 하나는 업그레이드 후 CE( 카디널리티 추정 ) 모델의 변경입니다. 2014년 SQL Server CE 모델에 상당한 변화가 도입되었습니다.

이 문서에서는 기본 CE를 사용할 때 발생하지만 레거시 CE를 사용할 때는 발생하지 않는 쿼리 성능 문제에 대한 문제 해결 단계 및 해결을 제공합니다.

참고

업그레이드 후 모든 쿼리가 느리게 실행되는 경우 이 문서에 도입된 문제 해결 단계는 상황에 적용되지 않을 수 있습니다.

문제 해결: CE 변경 내용이 문제인지 확인하고 그 이유를 확인합니다.

1단계: 기본 CE가 사용되는지 확인

  1. 업그레이드 후 느리게 실행되는 쿼리를 선택합니다.
  2. 쿼리를 실행하고 실행 계획을 수집합니다.
  3. 실행 계획 속성 창 CardinalityEstimationModelVersion을 검사. 실행 계획 속성 창 CE 모델 버전을 찾습니다.
  4. 값이 70이면 레거시 CE가 표시되고 값이 120 이상이면 기본 CE가 사용됨을 나타냅니다.

레거시 CE를 사용하는 경우 CE 변경은 성능 문제의 원인이 아닙니다. 기본 CE를 사용하는 경우 다음 단계로 이동합니다.

2단계: 쿼리 최적화 프로그램이 레거시 CE를 사용하여 더 나은 계획을 생성할 수 있는지 확인

레거시 CE를 사용하여 쿼리를 실행합니다. 기본 CE를 사용하는 것보다 성능이 더 좋은 경우 다음 단계로 이동합니다. 성능이 향상되지 않으면 CE 변경이 원인이 아닙니다.

3단계: 레거시 CE를 사용하여 쿼리가 더 잘 수행되는 이유 알아보기

쿼리에 대한 다양한 CE 관련 쿼리 힌트를 테스트합니다 . SQL Server 2014의 경우 해당 추적 플래그 4137, 9472 및 4139를 사용하여 쿼리를 테스트합니다. 이러한 테스트를 기반으로 성능에 긍정적인 영향을 미치는 힌트 또는 추적 플래그를 결정합니다.

해결 방법

이 문제를 resolve 다음 방법 중 하나를 시도합니다.

  • 쿼리를 최적화합니다.

    당연히 쿼리를 다시 작성하는 것이 항상 가능한 것은 아니지만, 특히 다시 작성할 수 있는 쿼리가 몇 개뿐인 경우 이 방법이 첫 번째 선택이어야 합니다. 최적으로 작성된 쿼리는 CE 버전에 관계없이 더 잘 수행됩니다.

  • 3단계에서 식별된 쿼리 힌트를 사용합니다.

    이 대상 접근 방식을 사용하면 다른 워크로드가 기본 CE 가정 및 개선 사항을 활용할 수 있습니다. 또한 계획 가이드를 만드는 것보다 더 강력한 옵션입니다. 또한 계획 강제 적용(가장 강력한 옵션)과 달리 QDS(쿼리 저장소)가 필요하지 않습니다.

  • 좋은 계획을 강제로 적용합니다.

    이는 유리한 옵션이며 특정 쿼리를 대상으로 하는 데 사용할 수 있습니다. 계획 가이드 또는 QDS를 사용하여 계획 강제 적용을 수행할 수 있습니다. QDS는 일반적으로 사용하기 쉽습니다.

  • 데이터베이스 범위 구성을 사용하여 레거시 CE를 강제로 적용합니다.

    이는 데이터베이스 전체 설정이며 이 데이터베이스에 대한 모든 쿼리에 적용되므로 덜 선호되는 방법입니다. 그러나 대상 접근 방식이 실현 가능하지 않을 때도 필요합니다. 구현하는 것이 가장 쉬운 옵션입니다.

  • 추적 플래그 9841을 사용하여 레거시 CE를 전역적으로 강제 적용합니다. 이렇게 하려면 DBCC TRACEON 을 사용하거나 추적 플래그를 시작 매개 변수로 설정합니다.

    이는 대상이 가장 적은 접근 방식이며 다른 옵션을 적용할 수 없는 경우에만 임시 완화 방법으로 사용해야 합니다.

레거시 CE를 사용하도록 설정하는 옵션

쿼리 수준: 쿼리 힌트 또는 QUERYTRACEON 옵션 사용

  • SQL Server 2016 SP1 이상 버전의 경우 쿼리에 대한 힌트 FORCE_LEGACY_CARDINALITY_ESTIMATION 를 사용합니다. 예를 들면 다음과 같습니다.

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

  • 추적 플래그 9481을 사용하도록 설정하여 레거시 CE 계획을 강제로 적용합니다. 다음은 예입니다.

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (QUERYTRACEON 9481)

데이터베이스 수준: 범위가 지정된 구성 또는 호환성 수준 설정

  • SQL Server 2016 이상 버전의 경우 데이터베이스 범위 구성을 변경합니다.

      --Force a specific database to use legacy CE
  ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

  -- Validate what databases use legacy CE
  SELECT name, value
      FROM sys.database_scoped_configurations 
  WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

  • 데이터베이스의 호환성 수준을 변경합니다. SQL Server 2014에 사용할 수 있는 유일한 데이터베이스 수준 옵션입니다. 이 변경 내용은 CE 이상의 영향을 줍니다. 호환성 수준 변경의 영향을 확인하려면 ALTER DATABASE 호환성 수준(Transact-SQL) 으로 이동하여 해당 테이블의 "차이점" 테이블을 검사합니다.

    ALTER DATABASE <YourDatabase>
SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level

참고

이 변경 내용은 재정의 추적 플래그 또는 쿼리 힌트를 사용하지 않는 한 구성이 변경되는 데이터베이스의 컨텍스트 내에서 실행되는 모든 쿼리에 영향을 줍니다. 기본 CE로 인해 더 잘 수행되는 쿼리는 회귀할 수 있습니다.

서버 수준: 추적 플래그 사용

추적 플래그 9481을 사용하여 서버 전체 레거시 CE를 강제 적용합니다.

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

참고

이 변경 내용은 재정의 추적 플래그 또는 쿼리 힌트를 사용하지 않는 한 SQL Server instance 컨텍스트 내에서 실행되는 모든 쿼리에 영향을 줍니다. 기본 CE로 인해 더 잘 수행되는 쿼리는 회귀할 수 있습니다.

질문과 대답

낮은 호환성 수준에서 실행되는 기존 데이터베이스의 경우 쿼리 프로세서를 더 높은 호환성 수준으로 업그레이드하는 데 권장되는 워크플로는 데이터베이스 호환성 모드 변경 및 쿼리 저장소 및 쿼리 저장소 사용 시나리오 사용에 자세히 설명되어 있습니다. 문서에 도입된 방법론은 SQL Server 및 Azure SQL Database의 경우 130 이상으로 이동하는 데 적용됩니다.

Q2: CE 변경 내용을 테스트할 시간이 없습니다. 이 경우 어떻게 해야 하나요?

기존 애플리케이션 및 워크로드의 경우 충분한 회귀 테스트가 수행될 때까지 기본 CE로 이동하지 않는 것이 좋습니다. 여전히 문제가 있는 경우 SQL Server 업그레이드하고 사용 가능한 최신 호환성 수준으로 이동하는 것이 좋습니다. 또한 2014년 SQL Server 추적 플래그 9481을 사용하도록 설정하거나 테스트할 기회가 있을 때까지 SQL Server 2016 이상 버전에 대한 LEGACY_CARDINALITY_ESTIMATION 데이터베이스 범위 구성ON을 구성합니다.

Q3: 레거시 CE를 영구적으로 사용할 때의 단점이 있나요?

향후 카디널리티 예측 도구 관련 개선 사항 및 수정 사항은 최신 버전을 중심으로 합니다. 버전 70은 허용 가능한 중간 상태입니다. 그러나 신중한 테스트 후에는 최신 CE 수정 사항을 활용하기 위해 최종적으로 최신 CE 버전으로 이동하는 것이 좋습니다. 레거시 CE에서 이동할 때 쿼리 계획 변경 가능성이 높으므로 프로덕션 시스템을 변경하기 전에 테스트합니다. 이러한 변경으로 많은 경우에 쿼리 성능이 향상될 수 있지만 경우에 따라 쿼리 성능이 저하될 수 있습니다.

중요

기본 CE는 장기적으로 향후 투자 및 심층 테스트 범위를 받을 수 있는 기본 코드 경로이므로 레거시 CE를 무기한 사용할 계획이 없습니다.

Q4: 수천 개의 데이터베이스가 있으며 각각에 대해 LEGACY_CARDINALITY_ESTIMATION 수동으로 켜고 싶지 않습니다. 대체 방법이 있나요?

SQL Server 2014의 경우 호환성 수준에 관계없이 모든 데이터베이스에 레거시 CE를 사용하도록 추적 플래그 9481을 사용하도록 설정합니다. SQL Server 2016 이상 버전의 경우 다음 쿼리를 실행하여 데이터베이스를 반복합니다. 데이터베이스가 복원되거나 다른 서버에 연결된 경우에도 설정이 사용하도록 설정됩니다.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Azure SQL Database의 경우 서버 수준이 아닌 구독 수준에서 이 추적 플래그를 사용하도록 설정하는 지원 티켓을 만들 수 있습니다.

Q5: 레거시 CE를 사용하여 실행하면 새 기능에 액세스할 수 없나요?

LEGACY_CARDINALITY_ESTIMATION 사용하도록 설정되어 있더라도 SQL Server 버전 및 관련 데이터베이스 호환성 수준에 포함된 최신 기능에 계속 액세스할 수 있습니다. 예를 들어 SQL Server 2017의 데이터베이스 호환성 수준 140에서 실행되는 LEGACY_CARDINALITY_ESTIMATION 사용하도록 설정된 데이터베이스는 적응형 쿼리 처리 기능 제품군의 이점을 계속 활용할 수 있습니다.

Q6: 레거시 CE는 언제 지원이 중단되나요?

현재 레거시 CE 지원을 중단할 계획은 없습니다. 그러나 향후 카디널리티 예측 도구 관련 개선 사항 및 수정 사항은 최신 버전의 CE를 중심으로 합니다.

Q7: 기본 CE를 사용하여 몇 개의 쿼리만 회귀하지만 대부분의 쿼리 성능은 동일하거나 개선되었습니다. 어떻게 해야 하나요?

서버 범위 추적 플래그 9481 또는 LEGACY_CARDINALITY_ESTIMATION 데이터베이스 범위 구성에 대한 보다 세부적인 대안은 쿼리 범위 USE HINT 구문을 사용하는 것입니다. 자세한 내용은 SQL Server 2016의 USE HINT 쿼리 힌트 인수 및 USE HINT를 참조하세요.

참고

추적 플래그가 9481인 옵션도 QUERYTRACEON 있지만 의미 체계적으로 더 명확하고 특별한 권한이 필요하지 않으므로 대신 를 사용하는 USE HINT 것이 좋습니다.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION 를 사용하면 데이터베이스의 호환성 수준에 관계없이 쿼리 최적화 프로그램 CE 모델을 버전 70으로 설정할 수 있습니다. 쿼리 수준: 쿼리 힌트 또는 QUERYTRACEON 옵션 사용을 참조하세요.

또는 기본 CE에 문제가 있는 쿼리가 하나만 있는 경우 쿼리 저장소 저장된 레거시 CE 계획을 강제로 적용하거나 계획 가이드와 함께 사용할 FORCE_LEGACY_CARDINALITY_ESTIMATION 수 있습니다.

CE는 복잡한 문제이며 알고리즘은 테이블 및 인덱스에 대한 통계와 같이 예측에 사용할 수 있는 완벽하지 않은 데이터를 사용합니다. 많은 가정(예: 조건자와 열의 상관 관계 또는 독립성, 균일한 데이터 분포, 포함 등)을 기반으로 하는 TVF(테이블 반환 함수) 및 모델과 같은 일부 모델 외 구문에 대한 정보는 없습니다.

고객 스키마, 데이터 및 워크로드의 무제한 조합을 감안할 때 모든 경우에 작동하는 모델을 선택하는 것은 거의 불가능합니다. 기본 CE의 일부 변경 내용에는 버그가 포함될 수 있으며(다른 소프트웨어와 마찬가지로) 수정될 수 있지만 모델 변경으로 인해 다른 문제가 발생합니다.

CE 버전, 특히 70에서 120으로 변경되는 경우 사용되는 모델에 대한 다양한 선택이 포함됩니다. 예를 들어 필터를 추정할 때 실제로 이러한 상관 관계가 자주 존재하고 CE 모델 70이 이러한 경우 결과를 과소 평가하기 때문에 조건자 간의 상관 관계를 어느 정도 가정합니다. 이러한 변경 내용은 많은 워크로드에 대해 테스트되고 많은 쿼리가 향상되었지만 일부 다른 쿼리의 경우 레거시 CE가 더 잘 일치하므로 기본 CE에서는 성능 회귀가 관찰될 수 있습니다.

아쉽게도 버그로 간주되지 않습니다. 이러한 상황에서는 쿼리 성능이 허용되지 않거나 이전 CE 모델 또는 특정 실행 계획을 강제로 적용하는 경우 레거시 CE와 함께 수행해야 하는 것처럼 쿼리 튜닝과 같은 해결 방법을 사용합니다.

Q9: 기본 CE의 카디널리티 변경 내용 및 쿼리 성능 영향에 대한 세부 정보를 배울 수 있는 리소스가 있나요?

자세한 내용은 SQL Server 2014 카디널리티 추정기를 사용하여 쿼리 계획 최적화를 참조하고 "SQL Server 2014에서 변경된 내용?" 섹션을 참조하세요.