sys.dm_db_stats_histogram(Transact-SQL)

아티클
07/12/2024

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스 Azure 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_id 는 int입니다.

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`에 대한 `DISTINCT_RANGE_ROWS > 0`)입니다.

설명

sys.dm_db_stats_histogram 반환 정보에 대한 결과 집합은 DBCC SHOW_STATISTICS WITH HISTOGRAM과 유사하며 또한 object_id, stats_id 및 step_number를 포함합니다.

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

히스토그램

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

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

다음 다이어그램에서는 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를 컴퓨팅하며 샘플링된 값은 저장하지 않습니다.

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

사용 권한

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

예제

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

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

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)

다음을 통해 공유