sys.dm_db_stats_histogram (Transact-SQL)

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed Instance

현재 SQL Server 데이터베이스에서 지정된 데이터베이스 개체(테이블 또는 인덱싱된 뷰)에 대한 통계 히스토그램을 반환합니다. DBCC SHOW_STATISTICS WITH HISTOGRAM와 비슷합니다.

참고 항목

이 DMF는 SQL Server 2016(13.x) SP1 CU2부터 사용할 수 있습니다.

구문

sys.dm_db_stats_histogram (object_id, stats_id)  

인수

object_id
현재 데이터베이스에서 해당 통계 중 하나의 속성이 요청되는 개체의 ID입니다. object_idint입니다.

stats_id
지정된 object_id 대한 통계 ID입니다. 통계 ID는 sys.stats 동적 관리 뷰에서 가져올 수 있습니다 . stats_id int입니다.

반환된 테이블

열 이름 데이터 형식 설명
object_id int 통계 개체의 속성을 반환할 개체(테이블 또는 인덱싱된 뷰)의 ID입니다.
stats_id int 통계 개체의 ID입니다. 테이블 또는 인덱싱된 뷰 내에서 고유합니다. 자세한 내용은 sys.stats(Transact-SQL)를 참조하세요.
step_number int 히스토그램의 단계 수입니다.
range_high_key sql_variant 히스토그램 단계의 상한 열 값입니다. 열 값은 키 값이라고도 합니다.
range_rows real 상한을 제외한 히스토그램 단계 내에 열 값이 있는 예상 행 수입니다.
equal_rows real 히스토그램 단계에서 상한과 열 값이 동일한 예상 행 수입니다.
distinct_range_rows bigint 상한을 제외한 히스토그램 단계 내에 고유한 열 값이 있는 예상 행 수입니다.
average_range_rows real 히스토그램 단계 내에 중복 열 값이 있는 평균 행 수(상한(RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0)를 제외합니다.

설명

반환 정보에 대한 sys.dm_db_stats_histogram 결과 집합은 다음과 유사하며 DBCC SHOW_STATISTICS WITH HISTOGRAM , 또한 object_id, stats_idstep_number.

range_high_key 은 sql_variant 데이터 형식이므로 조건자가 문자열이 아닌 상수와 비교하는 경우 사용해야 CASTCONVERT 할 수 있습니다.

히스토그램

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

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

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

Image of how a histogram is calculated from sampled column values.

각 히스토그램 단계를 살펴보면 다음과 같습니다.

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

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

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

쿼리 최적화 프로그램은 통계적 중요성에 따라 히스토그램 단계를 정의합니다. 또한 히스토그램의 단계 수를 최소화하면서 경계 값 간의 차이를 최대화하기 위해 최대 차이 알고리즘을 사용합니다. 최대 단계 수는 200개입니다. 히스토그램 단계 수는 경계 지점이 200개 미만인 열에서도 고유 값의 개수보다 적을 수 있습니다. 예를 들어 100개의 고유 값을 가진 열의 히스토그램에 100개 미만의 경계 지점이 있을 수 있습니다.

사용 권한

사용자에게 통계 열에 대한 선택 권한이 있거나 사용자가 테이블을 소유하거나 사용자가 고정 서버 역할, db_owner 고정 데이터베이스 역할 또는 db_ddladmin 고정 데이터베이스 역할의 sysadmin 멤버인지를 지정해야 합니다.

A. 간단한 예

다음 예제에서는 간단한 테이블을 만들고 채웁니다. 그런 다음 열에 대한 통계를 Country_Name 만듭니다.

CREATE TABLE Country
(Country_ID int IDENTITY PRIMARY KEY,
Country_Name varchar(120) NOT NULL);
INSERT Country (Country_Name) VALUES ('Canada'), ('Denmark'), ('Iceland'), ('Peru');

CREATE STATISTICS Country_Stats  
    ON Country (Country_Name) ;  

기본 키는 숫자 1을 stat_id 차지하므로 2번을 호출 sys.dm_db_stats_histogram 하여 테이블에 대한 stat_idCountry 통계 히스토그램을 반환합니다.

SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('Country'), 2);

B. 유용한 쿼리:

SELECT hist.step_number, hist.range_high_key, hist.range_rows, 
    hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'<statistic_name>';

C. 유용한 쿼리:

다음 예제에서는 열Country_Name에 조건자가 있는 테이블에서 Country 선택합니다.

SELECT * FROM Country 
WHERE Country_Name = 'Canada';

다음 예제에서는 위의 쿼리에서 Country 조건자와 일치하는 히스토그램 단계에 대해 테이블 및 열 Country_Name 에 대해 이전에 만든 통계를 확인합니다.

SELECT ss.name, ss.stats_id, shr.steps, shr.rows, shr.rows_sampled, 
    shr.modification_counter, shr.last_updated, sh.range_rows, sh.equal_rows
FROM sys.stats ss
INNER JOIN sys.stats_columns sc 
    ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac 
    ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
CROSS APPLY sys.dm_db_stats_histogram(ss.object_id, ss.stats_id) sh
WHERE ss.[object_id] = OBJECT_ID('Country') 
    AND ac.name = 'Country_Name'
    AND sh.range_high_key = CAST('Canada' AS CHAR(8));

다음 단계

DBCC SHOW_STATISTICS(Transact-SQL)
개체 관련 동적 관리 뷰 및 함수(Transact-SQL)
sys.dm_db_stats_properties(Transact-SQL)