통계

적용 대상: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

쿼리 최적화 프로그램은 통계를 사용하여 쿼리 성능을 향상시키는 쿼리 계획을 만듭니다. 대부분의 쿼리에서 쿼리 최적화 프로그램은 고품질의 쿼리 계획에 필요한 통계를 이미 생성하므로 경우에 따라서 최상의 결과를 위해 추가 통계를 만들거나 쿼리 설계를 수정해야 합니다. 이 문서에서는 통계 개념에 대해 설명하고 쿼리 최적화 통계를 효과적으로 사용하기 위한 지침을 제공합니다.

구성 요소 및 개념

통계

쿼리 최적화에 대한 통계는 테이블 또는 인덱싱된 뷰의 하나 이상의 열에 있는 값의 분포에 대한 통계 정보를 포함하는 BLOB(Binary Large Objects)입니다. 쿼리 최적화 프로그램은 이러한 통계를 사용하여 쿼리 결과에서 카디널리티 또는 행 수를 계산합니다. 쿼리 최적화 프로그램은 이러한 카디널리티 예상치를 통해 고품질의 쿼리 계획을 만듭니다. 예를 들어 조건자에 따라 쿼리 최적화 프로그램은 카디널리티 예상치를 사용하여 리소스를 많이 사용하는 인덱스 검색 연산자 대신 인덱스 검색 연산자를 선택할 수 있습니다. 이렇게 하면 쿼리 성능이 향상됩니다.

각 통계 개체는 하나 이상의 테이블 열 목록에 만들어지고 첫 번째 열의 값 분포를 표시하는 히스토그램을 포함합니다. 여러 열의 통계 개체는 열 간의 값 상관 관계에 대한 통계 정보도 저장합니다. 이러한 상관 관계 통계 또는 밀도는 열 값의 고유 행 수에서 파생됩니다.

히스토그램

히스토그램은 데이터 집합의 각 고유 값에 대한 발생 빈도를 측정합니다. 쿼리 최적화 프로그램은 통계 개체의 첫 번째 키 열에 있는 열 값에 대한 히스토그램을 계산하고, 행을 통계적으로 샘플링하거나 테이블 또는 뷰의 모든 행에 대한 전체 검사를 수행하여 열 값을 선택합니다. 샘플링된 행 집합으로 히스토그램을 만드는 경우 저장된 행 수의 합계와 고유 값의 수는 예상치이며 정수일 필요가 없습니다.

참고 항목

SQL Server의 히스토그램은 통계 개체의 키 열 집합에 있는 첫 번째 열인 단일 열에 대해서만 빌드됩니다.

히스토그램을 만들기 위해 쿼리 최적화 프로그램은 열 값을 정렬하고 각 고유 열 값과 일치하는 값 수를 계산한 다음 열 값을 최대 200개의 연속 히스토그램 단계로 집계합니다. 각 히스토그램 단계에는 열 값 범위와 상한 열 값이 포함됩니다. 범위는 경계 값 자체를 제외하고 경계 값 사이의 모든 가능한 열 값을 포함합니다. 정렬된 열 값 중 가장 낮은 값은 첫 번째 히스토그램 단계의 상한 값입니다.

SQL Server는 세 단계로 정렬된 열 값 집합에서 히스토그램을 만듭니다.

• 히스토그램 초기화: 첫 번째 단계에서는 정렬된 집합의 시작 부분에서 시작하는 값 시퀀스가 처리되고 최대 200개의 range_high_key, equal_rows, range_rows 및 distinct_range_rows 값이 수집됩니다(이 단계에서는 항상 0이 range_rows 및 distinct_range_rows). 첫 번째 단계는 모든 입력이 소진되거나 200개의 값이 발견되면 종료됩니다.
• 버킷 병합으로 스캔: 통계 키의 맨 앞줄에 있는 각 추가 값은 두 번째 단계에서 정렬된 순서로 처리됩니다. 각 연속 값은 마지막 범위에 추가되거나 끝에 있는 새 범위가 작성됩니다(입력 값이 정렬되어 있기 때문에 가능합니다). 새 범위가 만들어지면 한 쌍의 기존 인접 범위가 단일 범위로 축소됩니다. 이러한 범위 쌍은 정보 손실을 최소화하기 위해 선택됩니다. 이 메서드는 최대 차이 알고리즘을 사용하여 경계 값 간의 차이를 극대화하면서 히스토그램의 단계 수를 최소화합니다. 범위를 축소한 후의 단계 수는 이 단계 전체에서 200으로 유지됩니다.
• 히스토그램 통합: 세 번째 단계에서는 상당한 양의 정보가 손실되지 않으면 더 많은 범위가 축소될 수 있습니다. 히스토그램 단계 수는 경계 지점이 200개 미만인 열에서도 고유 값의 개수보다 적을 수 있습니다. 따라서 열에 200개 이상의 고유 값이 있더라도 히스토그램에는 200개 미만의 단계가 있을 수 있습니다. 고유한 값으로만 구성된 열의 경우 통합된 히스토그램에는 최소 3단계가 있습니다.

참고 항목

히스토그램이 fullscan이 아닌 샘플을 사용하여 빌드된 경우 equal_rows, range_rows 및 distinct_range_rows 및 average_range_rows 값이 추정되므로 전체 정수일 필요는 없습니다.

다음 다이어그램에서는 6단계의 히스토그램을 보여 줍니다. 첫 번째 상한 값 왼쪽의 영역이 1단계입니다.

위의 각 히스토그램 단계:

• 굵은 선은 상한 값(range_high_key) 및 발생 횟수(equal_rows)를 나타냅니다.

• range_high_key 왼쪽의 단색 영역은 열 값의 범위와 각 열 값이 발생하는 평균 횟수(average_range_rows)를 나타냅니다. 첫 번째 히스토그램 단계의 average_range_rows는 항상 0입니다.

• 점선은 범위(distinct_range_rows)의 총 고유 값 수와 범위(range_rows)의 총 값 수를 예측하는 데 사용되는 샘플링된 값을 나타냅니다. 쿼리 최적화 프로그램은 range_rows 및 distinct_range_rows 사용하여 average_range_rows 계산하고 샘플링된 값을 저장하지 않습니다.

밀도 벡터

밀도 는 지정된 열의 중복 수 또는 열 조합에 대한 정보이며 1/(고유 값 수)로 계산됩니다. 쿼리 최적화 프로그램은 같은 테이블 또는 인덱싱된 뷰에서 여러 열을 반환하는 쿼리의 카디널리티 예상치 정확도를 높이기 위해 밀도를 사용합니다. 밀도가 감소하면 값의 선택도가 증가합니다. 예를 들어 자동차를 나타내는 테이블에서 많은 자동차에는 제조업체가 동일하지만 각 자동차에는 고유한 VIN(차량 식별 번호)이 있습니다. VIN의 인덱스는 제조업체보다 밀도가 낮기 때문에 제조업체의 인덱스보다 더 선택적입니다.

참고 항목

빈도는 통계 개체의 첫 번째 키 열에 있는 각 고유 값의 발생에 대한 정보이며 행 수 * 밀도로 계산됩니다. 최대 빈도 1은 고유한 값을 가진 열에서 찾을 수 있습니다.

밀도 벡터는 통계 개체에 있는 각 열 접두사당 한 개의 밀도를 포함합니다. 예를 들어 통계 개체에 CustomerId, ItemId, Price 키 열이 있는 경우 다음의 각 열 접두사에 대해 밀도가 계산됩니다.

열 접두사	밀도 계산 기준
(CustomerId)	일치하는 값이 있는 행 CustomerId
(CustomerId, ItemId)	일치하는 값 CustomerId 이 있는 행 및 ItemId
(CustomerId, ItemId, Price)	에 대한 CustomerIdItemId값이 일치하는 행 및Price

필터링된 통계

필터링된 통계는 잘 정의된 데이터의 하위 집합에서 선택하는 쿼리에 대한 쿼리 성능을 높일 수 있습니다. 필터링된 통계는 필터 조건자를 사용하여 통계에 포함된 데이터의 하위 집합을 선택합니다. 잘 디자인된 필터링된 통계는 전체 테이블 통계와 비교하여 쿼리 실행 계획을 개선할 수 있습니다. 필터 조건자에 대한 자세한 내용은 CREATE STATISTICS(Transact-SQL)를 참조하세요. 필터링된 통계를 만드는 시기에 대한 자세한 내용은 이 문서의 통계 생성 시기 섹션을 참조하세요.

통계 옵션

통계가 작성되고 업데이트되는 시기 및 방법에 영향을 주는 몇 가지 옵션이 있습니다. 이러한 옵션은 데이터베이스 수준에서만 구성할 수 있습니다.

AUTO_CREATE_STATISTICS 옵션

자동 통계 만들기 옵션인 AUTO_CREATE_STATISTICS ON이면 쿼리 최적화 프로그램은 필요에 따라 쿼리 조건자의 개별 열에 대한 통계를 만들어 쿼리 계획에 대한 카디널리티 예상치를 향상시킵니다. 이러한 단일 열 통계는 기존 통계 개체에 히스토그램이 없는 열에 대해 작성됩니다. AUTO_CREATE_STATISTICS 옵션은 인덱스에 대한 통계를 만들지 여부를 결정하지 않습니다. 이 옵션은 또한 필터링된 통계도 생성하지 않으며 전체 테이블에 대한 단일 열 통계에 엄격하게 적용됩니다.

쿼리 최적화 프로그램에서 AUTO_CREATE_STATISTICS 옵션을 사용한 결과로 통계를 만들면 통계 이름이 로 _WA시작됩니다. 다음 쿼리를 사용하여 쿼리 최적화 프로그램이 쿼리 조건자 열에 대한 통계를 작성했는지 확인할 수 있습니다.

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

AUTO_UPDATE_STATISTICS 옵션

자동 업데이트 통계 옵션인 AUTO_UPDATE_STATISTICS ON이면 쿼리 최적화 프로그램에서 통계가 만료된 시기를 확인한 다음 쿼리에서 통계를 사용할 때 업데이트합니다. 이 작업을 통계 다시 컴파일이라고도 합니다. 삽입, 업데이트, 삭제 또는 병합 작업을 수정하면 테이블 또는 인덱싱된 뷰의 데이터 분포가 변경된 후 통계가 만료됩니다. 쿼리 최적화 프로그램은 마지막 통계 업데이트 이후의 행 수정 횟수를 계산하고 행 수정 횟수를 임계값과 비교하여 통계가 만료될 수 있는 시기를 결정합니다. 임계값은 테이블 카디널리티를 기반으로 하며 테이블 또는 인덱싱된 뷰의 행 수로 정의할 수 있습니다.

AUTO_UPDATE_STATISTICS 옵션이 OFF인 경우에도 행 수정에 따라 통계를 만료된 것으로 표시합니다. AUTO_UPDATE STATISTICS 옵션이 OFF이면 통계가 만료된 것으로 표시된 경우에도 업데이트되지 않습니다. 계획이 만료된 통계 개체를 계속 사용합니다. AUTO_UPDATE_STATISTICS를 OFF로 설정하면 최적이 아닌 쿼리 계획을 사용하게 되어 쿼리 성능이 저하될 수 있습니다. AUTO_UPDATE STATISTICS 옵션을 ON으로 설정하는 것이 좋습니다.

• SQL Server 2014(12.x)까지 데이터베이스 엔진은 통계를 평가할 때 테이블 또는 인덱싱된 뷰의 행 수에 따라 다시 컴파일 임계값을 사용합니다. 임계값은 테이블이 일시적이든 영구적이든 다릅니다.

  테이블 유형	테이블 카디널리티(n)	다시 컴파일 임계값(# 수정)
  임시	n< 6	6
  임시	6 <= n<= 500	500
  영구	n<= 500	500
  임시 또는 영구	n> 500	500 + (0.20 * n)

  예를 들어 테이블에 2만 행이 포함되어 있는 경우 계산은 500 + (0.2 * 20,000) = 4,500이고 4,500번 수정마다 통계가 업데이트됩니다.

• SQL Server 2016(13.x)부터 데이터베이스 호환성 수준 130에서 데이터베이스 엔진은 통계가 평가될 때 테이블 카디널리티에 따라 조정되는 감소하는 동적 통계 다시 컴파일 임계값도 사용합니다. 이러한 변경으로 대규모 테이블에 대한 통계가 더욱 자주 업데이트됩니다. 그러나 데이터베이스의 호환성 수준이 130 미만인 경우 SQL Server 2014(12.x) 임계값이 적용됩니다.

  테이블 유형	테이블 카디널리티(n)	다시 컴파일 임계값(# 수정)
  임시	n< 6	6
  임시	6 <= n<= 500	500
  영구	n<= 500	500
  임시 또는 영구	n> 500	MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

  예를 들어 테이블에 2백만 개의 행이 있으면 계산은 500 + (0.20 * 2,000,000) = 400,500 및 SQRT(1,000 * 2,000,000) = 44,721의 최솟값입니다. 즉, 통계는 44,721개 수정될 때마다 업데이트됩니다.

Important

SQL Server 2008 R2(10.50.x)에서 SQL Server 2014(12.x) 또는 SQL Server 2016(13.x) 이상에서 데이터베이스 호환성 수준 120 이하에서 추적 플래그 2371을 사용하도록 설정하여 SQL Server에서 감소하는 동적 통계 업데이트 임계값을 사용합니다.

모든 시나리오에 대해 권장되지만 추적 플래그 2371 사용은 선택 사항입니다. 그러나 SQL Server 2016(13.x) 이전 환경에서 추적 플래그 2371을 사용하도록 설정하려면 다음 지침을 사용할 수 있습니다.

• SAP 시스템을 기반으로 하는 경우 이 추적을 사용하도록 설정합니다. 자세한 내용은 추적 플래그 2371에 대한 블로그를 참조하세요.
• 현재 자동 업데이트가 충분히 자주 트리거되지 않아 주로 야간 작업으로 통계를 업데이트해야 하는 경우 추적 플래그 2371을 사용하도록 설정하여 임계값을 테이블 카디널리티로 조정하는 것이 좋습니다.

쿼리 최적화 프로그램은 쿼리를 컴파일하기 전과 캐시된 쿼리 계획을 실행하기 전에 오래된 통계를 확인합니다. 쿼리를 컴파일하기 전에 쿼리 최적화 프로그램은 쿼리 조건자의 열, 테이블 및 인덱싱된 뷰를 사용하여 오래된 통계를 결정합니다. 캐시된 쿼리 계획을 실행하기 전에 데이터베이스 엔진은 쿼리 계획이 최신 통계를 참조하는지 확인합니다.

AUTO_UPDATE_STATISTICS 옵션은 인덱스에 대해 생성된 통계 개체, 쿼리 조건자의 단일 열 및 CREATE STATISTICS 문으로 만든 통계에 적용됩니다. 이 옵션은 또한 필터링된 통계에도 적용됩니다.

sys.dm_db_stats_properties 사용하여 테이블에서 변경된 행 수를 정확하게 추적하고 통계를 수동으로 업데이트할지 여부를 결정할 수 있습니다.

AUTO_UPDATE_STATISTICS 항상 메모리 최적화 테이블의 경우 OFF입니다.

AUTO_UPDATE_STATISTICS_ASYNC

비동기 통계 업데이트 옵션인 AUTO_UPDATE_STATISTICS_ASYNC는 쿼리 최적화 프로그램이 동기 또는 비동기 통계 업데이트를 사용하는지를 결정합니다. 기본적으로 비동기 통계 업데이트 옵션은 OFF이며 쿼리 최적화 프로그램은 통계를 동기적으로 업데이트합니다. AUTO_UPDATE_STATISTICS_ASYNC 옵션은 인덱스에 대해 만든 통계 개체, 쿼리 조건자의 단일 열 및 CREATE STATISTICS 문으로 만든 통계에 적용됩니다.

참고 항목

SQL Server Management Studio에서 비동기 통계 업데이트 옵션을 설정하려면 데이터베이스 속성 창의 옵션 페이지에서 통계 자동 업데이트 및 통계 자동 업데이트 옵션을 True로 설정해야 합니다.

통계 업데이트는 동기(기본값) 또는 비동기일 수 있습니다.

• 동기 통계 업데이트를 사용하면 항상 최신 통계를 사용하여 쿼리가 컴파일되고 실행됩니다. 통계가 오래되면 쿼리 최적화 프로그램은 쿼리를 컴파일하고 실행하기 전에 업데이트된 통계를 기다립니다.

• 비동기 통계 업데이트를 사용하면 기존 통계가 최신 상태가 아닌 경우에도 기존 통계를 사용하여 쿼리가 컴파일됩니다. 쿼리를 컴파일할 때 통계가 최신 상태가 아니면 쿼리 최적화 프로그램에서 최적이 아닙니다. 통계는 일반적으로 곧 업데이트됩니다. 통계 업데이트가 완료된 후 컴파일되는 쿼리는 업데이트된 통계를 사용하면 도움이 됩니다.

테이블 잘림 또는 많은 행의 대량 업데이트 수행과 같이 데이터 분포를 변경하는 작업을 수행할 때 동기 통계를 사용하는 것이 좋습니다. 작업을 완료한 후 통계를 수동으로 업데이트하지 않은 경우 동기 통계를 사용하면 변경된 데이터에 대해 쿼리가 실행되기 전에 통계를 최신 상태로 유지할 수 있습니다.

다음과 같은 시나리오에서 보다 예상 가능한 쿼리 응답 시간을 얻으려면 비동기 통계를 사용하십시오.

• 애플리케이션은 동일한 쿼리, 유사한 쿼리 또는 유사한 캐시된 쿼리 계획을 자주 실행합니다. 쿼리 최적화 프로그램은 최신 통계를 기다리지 않고 들어오는 쿼리를 실행할 수 있으므로 동기 통계 업데이트보다 비동기 통계 업데이트로 쿼리 응답 시간을 더 예측할 수 있습니다. 이렇게 하면 일부 쿼리가 지연되고 다른 쿼리는 지연되지 않습니다.

• 애플리케이션에서 업데이트된 통계를 기다리는 하나 이상의 쿼리로 인해 클라이언트 요청 시간 초과가 발생했습니다. 동기 통계를 기다리는 경우 엄격한 시간 제한이 있는 애플리케이션은 실패할 수 있습니다.

참고 항목

로컬 임시 테이블에 대한 통계는 항상 AUTO_UPDATE_STATISTICS_ASYNC 옵션과 관계없이 동기적으로 업데이트됩니다. 전역 임시 테이블에 대한 통계는 사용자 데이터베이스에 대해 설정된 AUTO_UPDATE_STATISTICS_ASYNC 옵션에 따라 동기적 또는 비동기적으로 업데이트됩니다.

비동기 통계 업데이트는 백그라운드 요청에 의해 수행됩니다. 업데이트된 통계를 데이터베이스에 쓸 준비가 된 요청은 통계 메타데이터 개체에 대한 스키마 수정 잠금을 획득하려고 시도합니다. 다른 세션이 동일한 개체에 대한 잠금을 이미 보유하고 있는 경우 스키마 수정 잠금을 획득할 수 있을 때까지 비동기 통계 업데이트가 차단됩니다. 마찬가지로 쿼리를 컴파일하기 위해 통계 메타데이터 개체에 대한 스키마 안정성(Sch-S) 잠금을 획득해야 하는 세션은 이미 스키마 수정 잠금을 획득하기 위해 대기 중인 비동기 통계 업데이트 백그라운드 세션에 의해 차단될 수 있습니다. 따라서 쿼리 컴파일이 매우 빈번하고 통계가 자주 업데이트되는 워크로드의 경우 비동기 통계를 사용하면 잠금 차단으로 인해 동시성 문제가 발생할 가능성이 높아질 수 있습니다.

Azure SQL Database, Azure SQL Managed Instance 및 SQL Server 2022(16.x)부터는 ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY 데이터베이스 범위 구성을 사용하도록 설정하는 경우 비동기 통계 업데이트를 사용하여 잠재적 동시성 문제를 방지할 수 있습니다. 이 구성을 사용하도록 설정하면 백그라운드 요청은 우선 순위가 낮은 별도의 큐에서 스키마 수정(Sch-M) 잠금을 획득하고 업데이트된 통계를 유지할 때까지 기다리며 다른 요청이 기존 통계를 사용하여 쿼리를 계속 컴파일하도록 허용합니다. 통계 메타데이터 개체에 대한 잠금을 보유하고 있는 다른 세션이 없으면 백그라운드 요청은 해당 스키마 수정 잠금을 획득하고 통계를 업데이트합니다. 가능성은 낮지만 백그라운드 요청이 몇 분의 시간 제한 기간 내에 잠금을 획득할 수 없는 경우 비동기 통계 업데이트가 중단되고, 다른 자동 통계 업데이트가 트리거되거나 통계가 수동으로 업데이트될 때까지 통계가 업데이트되지 않습니다.

참고 항목

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY 데이터베이스 범위 구성 옵션은 Azure SQL Database, Azure SQL Managed Instance 및 SQL Server 2022(16.x)부터 사용할 수 있습니다.

AUTO_DROP 옵션

적용 대상*: Azure SQL Database, Azure SQL Managed Instance 및 SQL Server 2022(16.x)부터

SQL Server 2022(16.x) 이전의 SQL Server에서 사용자 데이터베이스의 사용자 또는 타사 도구에서 통계를 수동으로 만드는 경우 이러한 통계 개체는 고객이 원하는 스키마 변경을 차단하거나 방해할 수 있습니다.

SQL Server 2022(16.x)부터 자동 삭제 옵션은 기본적으로 모든 새 데이터베이스 및 마이그레이션된 데이터베이스에서 사용하도록 설정됩니다. AUTO_DROP을 사용하면 통계로 인해 스키마 변경이 차단되지 않고 대신 통계 개체가 삭제되도록 통계 개체를 만들 수 있습니다. 이러한 방식으로 자동 삭제를 사용하도록 설정된 수동으로 만든 통계는 자동 생성된 통계처럼 동작합니다.

참고 항목

자동 생성 통계에서 자동 삭제 속성을 설정하거나 설정 해제하려고 하면 오류가 발생할 수 있습니다. 자동 생성된 통계는 항상 자동 삭제를 사용합니다. 복원된 일부 백업은 다음에 통계 개체가 업데이트될 때까지(수동 또는 자동) 이 속성이 잘못 설정될 수 있습니다. 그러나 자동 생성 통계는 항상 자동 삭제 통계처럼 동작합니다. 이전 버전에서 SQL Server 2022(16.x)로 데이터베이스를 복원하는 경우 데이터베이스에서 sp_updatestats를 실행하여 통계 자동 드롭 기능에 대한 적절한 메타데이터를 설정하는 것이 좋습니다.

예를 들어 테이블에 통계 개체를 수동으로 만들려면 다음을 dbo.DatabaseLog 수행합니다.

CREATE STATISTICS [mystats] ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser]) WITH AUTO_DROP = ON;

예를 들어 테이블의 통계 개체 자동 삭제 설정을 업데이트하려면 다음을 수행 dbo.DatabaseLog 합니다.

UPDATE STATISTICS [dbo].[DatabaseLog] [mystats] WITH AUTO_DROP = ON;

기존 통계에 대한 자동 삭제 설정을 평가하려면 다음의 auto_drop 열을 sys.stats사용합니다.

SELECT object_id, [name], auto_drop
FROM sys.stats;

자세한 내용은 CREATE STATISTICS(Transact-SQL)를 참조하세요 .

INCREMENTAL

적용 대상: SQL Server 2014(12.x) 이상

CREATE STATISTICS의 INCREMENTAL 옵션이 ON이면 파티션 통계별로 통계가 작성됩니다. OFF로 설정된 경우 통계 트리가 삭제되고 SQL Server에서 통계를 다시 계산합니다. 기본값은 OFF입니다. 이 설정은 데이터베이스 수준 INCREMENTAL 속성을 재정의합니다. 증분 통계 만들기에 대한 자세한 내용은 CREATE STATISTICS(Transact-SQL)을 참조하세요. 자동으로 파티션별 통계를 만드는 방법은 데이터베이스 속성 (옵션 페이지) 및 ALTER DATABASE SET 옵션 (Transact-SQL)을 참조하세요.

새 파티션이 큰 테이블에 추가되면 새 파티션을 포함하도록 통계를 업데이트해야 합니다. 하지만 전체 테이블 검색((FULLSCAN 또는 SAMPLE 옵션)에 걸리는 시간이 꽤 길 수 있습니다. 또한 새 파티션에 대한 통계만 필요할 수 있으므로 전체 테이블을 검사할 필요가 없습니다. 증분 옵션은 파티션별로 통계를 만들고 저장하며, 업데이트되는 경우 새 통계가 필요한 파티션에 대한 통계만 새로 고칩니다.

파티션별 통계가 지원되지 않는 경우에는 이 옵션이 무시되고 경고가 생성됩니다. 다음 통계 유형에 대해서는 증분 통계가 지원되지 않습니다.

• 기본 테이블을 기준으로 파티션 정렬되지 않은 인덱스를 사용하여 작성된 통계입니다.
• Always On 읽기 가능한 보조 데이터베이스에 대해 작성된 통계입니다.
• 읽기 전용 데이터베이스에 대해 작성된 통계입니다.
• 필터링된 인덱스에 대해 작성된 통계입니다.
• 뷰에 대해 작성된 통계입니다.
• 내부 테이블에 대해 작성된 통계입니다.
• 공간 인덱스 또는 XML 인덱스를 사용하여 작성된 통계입니다.

통계를 만드는 경우

쿼리 최적화 프로그램은 다음과 같은 방법으로 이미 통계를 만듭니다.

1. 인덱스가 만들어진 경우 쿼리 최적화 프로그램에서 테이블 또는 뷰의 인덱스에 대한 통계를 작성합니다. 이러한 통계는 인덱스의 키 열에 생성됩니다. 인덱스가 필터링된 인덱스인 경우 쿼리 최적화 프로그램은 필터링된 인덱스에 대해 지정된 행의 동일한 하위 집합에 필터링된 통계를 만듭니다. 필터링된 인덱스에 대한 자세한 내용은 필터링된 인덱스 만들기 및 CREATE INDEX(Transact-SQL)를 참조하세요.

   참고 항목

   SQL Server 2014(12.x)부터는 분할된 인덱스를 만들거나 다시 작성할 때 테이블의 모든 행을 검사하여 통계가 생성되지 않습니다. 대신, 쿼리 최적화 프로그램에서 기본 샘플링 알고리즘을 사용하여 통계를 생성합니다. 분할된 인덱스로 데이터베이스를 업그레이드한 후 인덱스에 대한 히스토그램 데이터가 달라집니다. 이러한 동작 변경은 쿼리 성능에 영향을 미치지 않을 수 있습니다. 테이블의 모든 행을 검사하여 분할된 인덱스에 대한 통계를 얻으려면 FULLSCAN 절에서 CREATE STATISTICS 또는 UPDATE STATISTICS를 사용합니다.

2. 쿼리 최적화 프로그램은 AUTO_CREATE_STATISTICS 있을 때 쿼리 조건자의 단일 열에 대한 통계를 만듭니다.

대부분의 쿼리에서 통계를 만드는 두 가지 방법은 고품질 쿼리 계획을 보장합니다. 몇 가지 경우 CREATE STATISTICS 문을 사용하여 추가 통계를 만들어 쿼리 계획을 개선할 수 있습니다 . 이러한 추가 통계는 쿼리 최적화 프로그램에서 인덱스 또는 단일 열에 대한 통계를 만들 때 고려하지 않는 통계 상관 관계를 캡처할 수 있습니다. 애플리케이션에는 통계 개체로 계산되는 경우 쿼리 최적화 프로그램에서 쿼리 계획을 개선할 수 있는 추가 통계 상관 관계가 테이블 데이터에 있을 수 있습니다. 예를 들어 데이터 행의 하위 집합에 대한 필터링된 통계 또는 쿼리 조건자 열에 대한 여러 열 통계는 쿼리 계획을 향상시킬 수 있습니다.

CREATE STATISTICS 문으로 통계를 만들 때 쿼리 최적화 프로그램에서 쿼리 조건자 열에 대한 단일 열 통계를 계속해서 정기적으로 작성할 수 있도록 AUTO_CREATE_STATISTICS 옵션을 ON으로 유지하는 것이 좋습니다. 쿼리 조건자에 대한 자세한 내용은 검색 조건(Transact-SQL)을 참조하세요.

다음 중 한 가지가 적용되는 경우 CREATE STATISTICS 문으로 통계를 작성하십시오.

• 데이터베이스 엔진 튜닝 관리자는 통계 만들기를 제안합니다.
• 쿼리 조건자는 동일한 인덱스에 아직 없는 여러 상관 관계가 있는 열을 포함합니다.
• 쿼리는 데이터의 하위 집합에서 선택합니다.
• 쿼리에 누락된 통계가 있습니다.

참고 항목

메모리 내 OLTP 관련 테이블 및 통계에 대한 자세한 내용은 메모리 액세스에 최적화된 테이블에 대한 통계를 참조하세요.

쿼리 조건자는 여러 상관 관계가 있는 열을 포함합니다.

쿼리 조건자가 열 간 관계 및 종속성이 있는 여러 열을 포함하는 경우 여러 열에 대한 통계로 쿼리 계획이 향상될 수 있습니다. 여러 열에 대한 통계에는 단일 열 통계에서 사용할 수 없는 밀도라고 하는 열 간 상관 관계 통계가 포함됩니다. 밀도는 쿼리 결과가 여러 열 간의 데이터 관계에 따라 달라지는 경우 카디널리티 예상치를 향상시킬 수 있습니다.

열이 동일한 인덱스에 이미 있는 경우 다중 열 통계 개체가 이미 존재하며 수동으로 만들 필요가 없습니다. 열이 동일한 인덱스에 아직 없는 경우 열에 인덱스를 만들거나 CREATE STATISTICS 문을 사용하여 여러 열 통계를 만들 수 있습니다. 통계 개체보다는 인덱스를 유지하는 데 더 많은 시스템 리소스가 필요합니다. 애플리케이션에 다중 열 인덱스가 필요하지 않은 경우 인덱스를 만들지 않고 통계 개체를 만들어 시스템 리소스를 경제화할 수 있습니다.

여러 열 통계를 작성할 때 통계 개체 정의에서 열 순서는 카디널리티 예상치를 만들기 위한 밀도 효율성에 영향을 줍니다. 통계 개체는 주요 열의 각 접두사에 대한 밀도를 통계 개체 정의에 저장합니다. 밀도에 대한 자세한 내용은 이 페이지의 밀도 섹션을 참조하세요.

카디널리티 예측에 유용한 밀도를 만들려면 쿼리 조건자의 열이 통계 개체 정의에 있는 열의 접두사 중 하나와 일치해야 합니다. 예를 들어 다음 예에서는 LastName, MiddleName 및 FirstName 열에 대한 다중 열 통계 개체를 만듭니다.

USE AdventureWorks2022;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = 'LastFirst'
    AND object_ID = OBJECT_ID ('Person.Person'))
DROP STATISTICS Person.Person.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);
GO

이 예제에서 통계 개체 LastFirst 에는 다음 열 접두(LastName)(LastName, MiddleName)(LastName, MiddleName, FirstName)사에 대한 밀도가 있습니다. 밀도는 .에 (LastName, FirstName)사용할 수 없습니다. 쿼리가 사용 LastName 중이고 FirstName 사용하지 MiddleName않으면 카디널리티 예측에 밀도를 사용할 수 없습니다.

데이터 하위 집합에서 쿼리 선택

쿼리 최적화 프로그램은 단일 열 및 인덱스에 대한 통계를 만들 때 모든 행의 값에 대한 통계를 만듭니다. 쿼리가 행의 하위 집합에서 선택하고 행의 해당 하위 집합에서 데이터 분포가 고유한 경우 필터링된 통계는 쿼리 계획을 향상시킬 수 있습니다. CREATE STATISTICS 문을 WHERE 절과 함께 사용하여 필터링된 통계를 만들어 필터 조건자 식을 정의할 수 있습니다.

예를 들어 AdventureWorks2022를 사용하면 테이블의 각 제품이 Production.Product 표의 4가지 범주 Production.ProductCategory 중 하나인 자전거, 구성 요소, 의류 및 액세서리 중 하나에 속합니다. 각 범주에는 가중치에 대한 다른 데이터 분포가 있습니다. 자전거 가중치는 13.77에서 30.0까지, 구성 요소 가중치는 2.12에서 1050.00까지 다양하며 일부 NULL 값은 의류 가중치가 모두 NULL이고 액세서리 가중치도 NULL입니다.

예를 들어 Bikes를 사용하여 모든 자전거 가중치에 대한 필터링된 통계는 쿼리 최적화 도구에 보다 정확한 통계를 제공하고 전체 테이블 통계 또는 가중치 열의 존재하지 않는 통계에 비해 쿼리 계획 품질을 향상시킬 수 있습니다. 자전거 가중치 열은 필터링된 통계에 적합한 후보이지만 가중치 조회 수가 상대적으로 작은 경우 필터링된 인덱스에 적합한 후보는 아닙니다. 필터링된 인덱스에서 제공하는 조회 성능의 향상은 장점이지만 필터링된 인덱스를 데이터베이스에 추가하는 것으로 인한 추가 유지 관리 및 스토리지 비용은 부담이 될 수 있습니다.

다음 문에서는 Bikes의 모든 하위 범주에 대해 BikeWeights 로 필터링된 통계를 작성합니다. 필터링된 조건자 식에서는 Production.ProductSubcategoryID IN (1,2,3)비교를 통해 모든 자전거 하위 범주를 열거하는 방법으로 자전거를 정의합니다. 조건자는 Production.ProductCategory 테이블에 저장되고 필터 식의 모든 열이 동일한 테이블에 있어야 하므로 Bikes 범주 이름을 사용할 수 없습니다.

USE AdventureWorks2022;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

쿼리 최적화 프로그램은 필터링된 통계를 사용하여 BikeWeights 무게를 초과하는 25모든 자전거를 선택하는 다음 쿼리에 대한 쿼리 계획을 개선할 수 있습니다.

SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
    JOIN Production.ProductSubcategory AS S
    ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25
ORDER BY P.Weight;
GO

쿼리는 누락된 통계를 식별합니다.

오류 또는 기타 이벤트로 인해 쿼리 최적화 프로그램에서 통계를 만들지 못하는 경우 쿼리 최적화 프로그램은 통계를 사용하지 않고 쿼리 계획을 만듭니다. 쿼리 최적화 프로그램은 통계를 누락된 것으로 표시하고 다음에 쿼리가 실행될 때 통계를 다시 생성하려고 시도합니다.

누락된 통계는 SQL Server Management Studio를 사용하여 쿼리 실행 계획이 그래픽으로 표시될 때 경고(빨간색 텍스트의 테이블 이름)로 표시됩니다. 또한 SQL Server Profiler를 사용하여 Missing Column Statistics 이벤트 클래스를 모니터링하면 통계가 누락된 경우를 나타냅니다. 자세한 내용은 오류 및 경고 이벤트 범주(데이터베이스 엔진)를 참조하세요.

통계가 누락된 경우 다음 단계를 수행합니다.

• AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS가 ON으로 설정되었는지 확인합니다.
• 데이터베이스가 읽기 전용이 아닌지 확인합니다. 데이터베이스가 읽기 전용이면 새 통계 개체를 저장할 수 없습니다.
• CREATE STATISTICS 문을 사용하여 누락된 통계를 만듭니다 .

읽기 전용 데이터베이스 또는 읽기 전용 스냅샷에 대한 통계가 없거나 부실한 경우 데이터베이스 엔진은 임시 통계를 만들고 유지 관리합니다 tempdb. 데이터베이스 엔진이 임시 통계를 만들 때 임시 통계와 영구 통계를 구분하기 위해 접미사 _readonly_database_statistic 통계 이름이 추가됩니다. 접미사 _readonly_database_statistic은 SQL Server에서 생성하는 통계용으로 예약되어 있습니다. 읽기/쓰기 데이터베이스에서 임시 통계에 대한 스크립트를 만들어 재현할 수 있습니다. 스크립트될 때 Management Studio는 통계 이름의 접미사를 _readonly_database_statistic _readonly_database_statistic_scripted변경합니다.

SQL Server만 임시 통계를 만들고 업데이트할 수 있습니다. 그러나 임시 통계를 삭제하고 통계 속성을 모니터링하는 데는 영구적 통계에 사용하는 것과 동일한 도구를 사용할 수 있습니다.

• DROP STATISTICS 문을 사용하여 임시 통계를 삭제합니다.
• sys.stats 및 sys.stats_columns 카탈로그 뷰를 사용하여 통계를 모니터링합니다. sys.stats 시스템 카탈로그 보기에는 영구적 통계와 임시 통계를 나타내는 is_temporary 열이 포함되어 있습니다.

임시 통계가 저장 tempdb되므로 SQL Server 서비스를 다시 시작하면 모든 임시 통계가 사라집니다.

통계를 업데이트하는 경우

쿼리 최적화 프로그램은 통계가 최신 상태가 아닐 수 있는 시기를 확인한 다음 쿼리 계획에 필요할 때 업데이트합니다. 경우에 따라 AUTO_UPDATE_STATISTICS를 ON으로 설정할 때보다 더 자주 통계를 업데이트하여 쿼리 계획을 향상하고 쿼리 성능을 높일 수 있습니다. UPDATE STATISTICS 문 또는 sp_updatestats 저장 프로시저를 사용하여 통계를 업데이트할 수 있습니다.

통계를 업데이트하면 쿼리가 최신 통계로 컴파일되지만 임의의 프로세스를 통해 통계를 업데이트하면 쿼리 계획이 자동으로 다시 컴파일될 수 있습니다. 쿼리 계획 향상과 쿼리 재컴파일 소요 시간 간의 성능 균형을 유지해야 하므로 너무 자주 수동으로 통계를 업데이트하지 않는 것이 좋습니다. 구체적인 성능 균형 유지의 정도는 애플리케이션에 따라 달라집니다.

UPDATE STATISTICS 또는 sp_updatestats를 사용하여 통계를 업데이트할 때 AUTO_UPDATE_STATISTICS를 ON으로 유지하여 쿼리 최적화 프로그램에서 정기적으로 통계를 업데이트하도록 하는 것이 좋습니다.

• 열, 인덱스, 테이블 또는 인덱싱된 뷰에 대한 통계를 업데이트하는 방법은 UPDATE STATISTICS(Transact-SQL)를 참조하세요.

• 데이터베이스의 모든 사용자 정의 테이블 및 내부 테이블에 대한 통계를 업데이트하는 방법은 저장 프로시저 sp_updatestats(Transact-SQL)를 참조하세요.

• 자동 통계 업데이트 임계값에 대한 자세한 내용은 AUTO_UPDATE_STATISTICS 옵션을 참조하세요.

AUTO_UPDATE_STATISTICS가 OFF로 설정되면 다양한 이유로 계획 다시 컴파일이 계속 발생할 수 있지만 만료된 통계 업데이트로 인해 자동으로 실행되지는 않습니다. AUTO_UPDATE_STATISTICS가 OFF로 설정되면 통계 업데이트는 유지 관리 계획과 같은 다른 수동 예약 프로세스를 통해서만 실행됩니다. 그러므로 AUTO_UPDATE_STATISTICS를 OFF로 설정하면 최적이 아닌 쿼리 계획을 사용하게 되어 쿼리 성능이 저하될 수 있습니다.

오래된 통계 검색

통계가 마지막으로 업데이트된 시기를 확인하려면 sys.dm_db_stats_properties 또는 STATS_DATE 함수를 사용합니다.

다음 조건에 대한 통계를 업데이트하는 것이 좋습니다.

• 쿼리 실행 시간이 느린 경우
• 삽입 작업은 오름차순 또는 내림차순 키 열에서 발생합니다.
• 유지 관리 작업 후.

통계를 수동으로 업데이트하는 예제는 UPDATE STATISTICS(Transact-SQL)를 참조하세요.

쿼리 실행 시간이 느림

쿼리 응답 시간이 느리거나 예측할 수 없는 경우 추가 문제 해결 단계를 수행하기 전에 쿼리에 최신 통계가 있는지 확인합니다.

삽입 작업은 오름차순 또는 내림차순 키 열에서 발생합니다.

IDENTITY 또는 실시간 타임스탬프 열과 같은 오름차순 또는 내림차순 키 열에 대한 통계에는 쿼리 최적화 프로그램에서 수행하는 것보다 더 자주 통계 업데이트가 필요할 수 있습니다. 삽입 작업은 오름차순 또는 내림차순 열에 새 값을 추가합니다. 추가된 행 수가 너무 작아 통계 업데이트를 트리거할 수 없습니다. 통계가 최신이 아니고 쿼리가 가장 최근에 추가된 행 중에서 선택하는 경우 현재 통계에는 이러한 새 값에 대한 카디널리티 예상치가 없습니다. 이로 인해 카디널리티 예측이 정확하지 않으며 쿼리 성능이 저하될 수 있습니다.

예를 들어 가장 최근의 판매 주문 날짜에서 선택하는 쿼리는 통계가 최신 판매 주문 날짜에 대한 카디널리티 예상치를 포함하도록 업데이트되지 않은 경우 부정확한 카디널리티 추정치를 갖게 됩니다.

유지 관리 작업 후

테이블 잘라내기 또는 행의 대량 삽입 수행과 같이 데이터 분포를 변경하는 유지 관리 절차를 수행한 후 통계를 업데이트하는 것이 좋습니다. 이렇게 하면 이후에 쿼리에서 자동 통계 업데이트를 기다리는 동안 쿼리 처리에 지연이 생기는 것을 방지할 수 있습니다.

인덱스 다시 작성, 다시 구성, 조각 모음 등의 작업은 데이터 분포를 변경하지 않습니다. 따라서 ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG 또는 ALTER INDEX REORGANIZE 작업을 수행한 후에는 통계를 업데이트할 필요가 없습니다. 쿼리 최적화 프로그램은 ALTER INDEX REBUILD 또는 DBCC DBREINDEX를 사용하여 테이블 또는 뷰에서 인덱스를 다시 작성할 때 통계를 업데이트합니다. 그러나 이 통계 업데이트는 인덱스를 다시 만드는 부산물입니다. 쿼리 최적화 프로그램은 DBCC INDEXDEFRAG 또는 ALTER INDEX REORGANIZE 작업 후에 통계를 업데이트하지 않습니다.

팁

SQL Server 2016(13.x) SP1 CU4부터 CREATE STATISTICS(Transact-SQL) 또는 UPDATE STATISTICS(Transact-SQL)의 PERSIST_SAMPLE_PERCENT 옵션을 사용하여 샘플링 백분율을 명시적으로 지정하지 않는 후속 통계 업데이트에 대해 특정 샘플링 백분율을 설정하고 유지합니다.

자동 인덱스 및 통계 관리

Adaptive Index Defrag와 같은 솔루션을 사용하여 하나 이상의 데이터베이스에 대한 인덱스 조각 모음 및 통계 업데이트를 자동으로 관리합니다. 이 절차는 다른 매개 변수 사이에서 조각화 수준에 따라 인덱스를 다시 작성하거나 다시 구성할지 여부를 자동으로 선택하고 통계를 선형 임계값으로 업데이트합니다.

통계를 효과적으로 사용하는 쿼리

쿼리 조건자의 로컬 변수 및 복잡한 식과 같은 특정 쿼리 구현은 최적이 않은 쿼리 계획으로 이어질 수 있습니다. 통계를 효과적으로 사용하기 위한 쿼리 디자인 지침을 따르면 이를 방지하는 데 도움이 될 수 있습니다. 쿼리 조건자에 대한 자세한 내용은 검색 조건(Transact-SQL)을 참조하세요.

쿼리 조건자에 사용된 식, 변수 및 함수에 대한 카디널리티 예상치 정확도를 높이기 위해 효율적으로 통계를 사용하는 쿼리 설계 지침을 적용하여 쿼리 계획을 향상시킬 수 있습니다. 쿼리 최적화 프로그램에서 식, 변수 또는 함수의 값을 모르는 경우 히스토그램에서 조회할 값을 알지 못하므로 히스토그램에서 최상의 카디널리티 예상치를 검색할 수 없습니다. 대신 쿼리 최적화 프로그램은 히스토그램의 샘플링된 모든 행에 대한 고유 값당 평균 행 수에 대한 카디널리티 예상치를 기반으로 합니다. 이로 인해 카디널리티 예상치가 만족스럽지 못하고 쿼리 성능이 저하될 수 있습니다. 히스토그램에 대한 자세한 내용은 이 페이지 또는 sys.dm_db_stats_histogram 히스토그램 섹션을 참조하세요.

다음 지침에서는 카디널리티 예상치를 개선하여 쿼리 계획을 개선하기 위해 쿼리를 작성하는 방법을 설명합니다.

식에 대한 카디널리티 예상치 개선

식에 대한 카디널리티 예상치 정확도를 높이려면 다음 지침에 따르십시오.

• 가능하면 상수로 식을 단순화합니다. 쿼리 최적화 프로그램은 카디널리티 예상치를 결정하기 전에 상수가 포함된 모든 함수와 식을 평가하지 않습니다. 예를 들어 ABS(-100) 식을 100으로 단순화합니다.
• 식에서 여러 변수를 사용하는 경우 식에 대한 계산 열을 만든 다음 계산 열에 통계 또는 인덱스를 만드는 것이 좋습니다. 예를 들어 식에 대한 Price + Tax계산 열을 만드는 경우 쿼리 조건자의 WHERE PRICE + Tax > 100 카디널리티 추정치가 더 좋을 수 있습니다.

변수 및 함수에 대한 카디널리티 예상치 개선

변수 및 함수에 대한 카디널리티 예상치 정확도를 높이려면 다음 지침에 따르십시오.

• 쿼리 조건자가 지역 변수를 사용하는 경우 로컬 변수 대신 매개 변수를 사용하도록 쿼리를 다시 작성하는 것이 좋습니다. 쿼리 최적화 프로그램에서 쿼리 실행 계획을 만들 때 지역 변수의 값을 알 수 없습니다. 쿼리에서 매개 변수를 사용할 때 쿼리 최적화 프로그램은 저장 프로시저에 전달되는 첫 번째 실제 매개 변수 값에 대한 카디널리티 예상치를 사용합니다.

• 표준 테이블 또는 임시 테이블을 사용하여 다중 문 테이블 반환 함수(mstvf)의 결과를 보유하는 것이 좋습니다. 쿼리 최적화 프로그램은 다중 문 테이블 반환 함수에 대한 통계를 만들지 않습니다. 이 방법을 사용하는 경우 쿼리 최적화 프로그램은 테이블 열에 대한 통계를 작성하고 이러한 통계를 사용하여 더욱 향상된 쿼리 계획을 만들 수 있습니다.

• 표준 테이블 또는 임시 테이블을 테이블 변수 대신 사용하는 것이 좋습니다. 쿼리 최적화 프로그램은 테이블 변수에 대한 통계를 만들지 않습니다. 이 방법을 사용하는 경우 쿼리 최적화 프로그램은 테이블 열에 대한 통계를 작성하고 이러한 통계를 사용하여 더욱 향상된 쿼리 계획을 만들 수 있습니다. 임시 테이블 또는 테이블 변수를 사용할지 여부를 결정하는 데는 장단 사항이 있습니다. 저장 프로시저에 사용되는 테이블 변수는 임시 테이블보다 저장 프로시저를 더 적게 다시 컴파일합니다. 애플리케이션에 따라 테이블 변수 대신 임시 테이블을 사용하면 성능이 향상되지 않을 수 있습니다.

• 저장 프로시저에 전달된 매개 변수를 사용하는 쿼리가 포함된 경우 쿼리에서 사용하기 전에 저장 프로시저 내에서 매개 변수 값을 변경하지 마십시오. 쿼리에 대한 카디널리티 추정치는 업데이트된 값이 아닌 전달된 매개 변수 값을 기반으로 합니다. 매개 변수 값을 변경하지 않도록 쿼리를 다시 작성하여 두 개의 저장 프로시저를 사용할 수 있습니다.

  예를 들어 다음 저장 프로시저 Sales.GetRecentSales 는 NULL인 경우 @date 매개 변수 @date 값을 변경합니다.

  USE AdventureWorks2022;
  GO
  IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetRecentSales;
  GO
  CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
  AS BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END
  GO
  

  저장 프로시저 Sales.GetRecentSales 에 대한 첫 번째 호출이 매개 변수에 대해 @date NULL을 전달하는 경우 쿼리 최적화 프로그램은 쿼리 조건자가 호출되지 않더라도 카디널리티 예상치 @date = NULL 를 @date = NULL사용하여 저장 프로시저를 컴파일합니다. 이 카디널리티 예상치는 실제 쿼리 결과의 행 수와 크게 다를 수 있습니다. 그 결과 쿼리 최적화 프로그램에서 만족스럽지 못한 쿼리 계획을 선택할 수 있습니다. 이를 방지하기 위해 다음과 같이 저장 프로시저를 두 개의 프로시저로 다시 작성할 수 있습니다.

  USE AdventureWorks2022;
  GO
  IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)
  AS BEGIN
      IF @date is NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      EXEC Sales.GetNonNullRecentSales @date;
  END
  GO
  IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNonNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)
  AS BEGIN
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END
  GO
  

쿼리 힌트를 사용하여 카디널리티 예측 개선

지역 변수에 대한 카디널리티 예상치 정확도를 높이기 위해 RECOMPILE과 함께 OPTIMIZE FOR <value> 또는 OPTIMIZE FOR UNKNOWN 쿼리 힌트를 사용할 수 있습니다. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하세요.

일부 애플리케이션의 경우 쿼리를 실행할 때마다 다시 컴파일하는 데 너무 많은 시간이 걸릴 수 있습니다. RECOMPILE 옵션을 사용하지 않는 경우에도 OPTIMIZE FOR 쿼리 힌트가 도움이 될 수 있습니다. 예를 들어 특정 날짜를 지정하기 위해 OPTIMIZE FOR 옵션을 Sales.GetRecentSales 저장 프로시저에 추가할 수 있습니다. 다음 예에서는 OPTIMIZE FOR 옵션을 Sales.GetRecentSales 프로시저에 추가합니다.

USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO

계획 가이드를 사용하여 카디널리티 예측 개선

일부 애플리케이션의 경우 쿼리를 변경할 수 없거나 RECOMPILE 쿼리 힌트로 인해 너무 많은 다시 컴파일이 발생할 수 있으므로 쿼리 디자인 지침이 적용되지 않을 수 있습니다. 애플리케이션 공급업체와 함께 애플리케이션 변경 내용을 조사하는 동안 쿼리 동작을 제어할 수 있도록 계획 지침을 사용하여 USE PLAN과 같은 기타 힌트를 지정할 수 있습니다. 계획 지침에 대한 자세한 내용은 Plan Guides를 참조하십시오.

Azure SQL Database에서 계획 지침 대신 계획을 강제로 적용하려면 쿼리 저장소 힌트를 고려하세요. 자세한 내용은 쿼리 저장소 힌트를 참조하세요.

참고 항목

• 메모리 액세스에 최적화된 테이블에 대한 통계
• CREATE STATISTICS(Transact-SQL)
• UPDATE STATISTICS(Transact-SQL)
• sp_updatestats(Transact-SQL)
• DBCC SHOW_STATISTICS(Transact-SQL)
• ALTER DATABASE SET 옵션(TRANSACT-SQL)
• DROP STATISTICS(Transact-SQL)
• CREATE INDEX(Transact-SQL)
• ALTER INDEX(Transact-SQL)
• 필터링된 인덱스 만들기
• SQL Server의 Autostat(AUTO_UPDATE_STATISTICS) 동작 제어
• STATS_DATE(Transact-SQL)
• sys.dm_db_stats_properties(Transact-SQL)
• sys.dm_db_stats_histogram (Transact-SQL)
• sys.stats
• sys.stats_columns(Transact-SQL)

다음 단계

• Microsoft SQL Server Tiger 팀 도구 상자의 Adaptive Index Defrag