sys.dm_db_xtp_hash_index_stats(Transact-SQL)
적용 대상:
SQL ServerAzure SQL Database Azure SQL Managed Instance
이러한 통계는 메모리 최적화 테이블의 해시 인덱스에 대한 버킷 수를 이해하고 조정하는 데 유용합니다. 인덱스 키에 중복 항목이 많은 경우를 검색하는 데 사용할 수도 있습니다.
평균 체인 길이가 크면 많은 행이 동일한 버킷에 해시됨을 나타냅니다. 이 오류는 다음과 같은 이유로 발생할 수 있습니다.
빈 버킷 수가 적거나 평균 및 최대 체인 길이가 비슷한 경우 총 버킷 수가 너무 낮을 수 있습니다. 이렇게 하면 여러 인덱스 키가 동일한 버킷에 해시됩니다.
빈 버킷 수가 높거나 평균 체인 길이를 기준으로 최대 체인 길이가 높은 경우 두 가지 설명이 있을 수 있습니다. 중복 인덱스 키 값이 있는 행이 많거나 키 값에 오차가 있습니다. 두 경우 모두 인덱스 키 값이 동일한 모든 행이 동일한 버킷에 해시되어 해당 버킷의 긴 체인 길이로 이어집니다.
긴 체인 길이는 포함 및 INSERT을 포함하여 SELECT 개별 행에 대한 모든 DML 작업의 성능에 크게 영향을 줄 수 있습니다. 빈 버킷 수가 높은 짧은 체인 길이는 너무 높은 bucket_count 나타냅니다. 이렇게 하면 인덱스 검색의 성능이 저하됩니다.
Warning
이 DMV는 전체 테이블을 검색합니다. 따라서 데이터베이스 sys.dm_db_xtp_hash_index_stats 에 큰 테이블이 있는 경우 시간이 오래 걸릴 수 있습니다.
자세한 내용은 메모리 최적화 테이블에 대한 해시 인덱스를 참조 하세요.
| 열 이름 | Type | 설명 |
|---|---|---|
| object_id | int | 부모 테이블의 개체 ID입니다. |
| xtp_object_id | bigint | 메모리 최적화 테이블의 ID입니다. |
| index_id | int | 인덱스 ID입니다. |
| total_bucket_count | bigint | 인덱스의 총 해시 버킷 수입니다. |
| empty_bucket_count | bigint | 인덱스의 빈 해시 버킷 수입니다. |
| avg_chain_length | bigint | 인덱스에서 모든 해시 버킷의 낮은 체인 평균 길이입니다. |
| max_chain_length | bigint | 해시 버킷에 있는 행 체인의 최대 길이입니다. |
| xtp_object_id | bigint | 메모리 최적화 테이블에 해당하는 메모리 내 OLTP 개체 ID입니다. |
사용 권한
데이터베이스에 대한 VIEW DATABASE STATE 권한이 필요합니다.
SQL Server 2022 이상에 대한 권한
데이터베이스에 대한 VIEW DATABASE PERFORMANCE STATE 권한이 필요합니다.
예제
A. 해시 인덱스 버킷 수 문제 해결
다음 쿼리를 사용하여 기존 테이블의 해시 인덱스 버킷 수 문제를 해결할 수 있습니다. 쿼리는 사용자 테이블의 모든 해시 인덱스에 대해 빈 버킷의 백분율 및 체인 길이에 대한 통계를 반환합니다.
SELECT
QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],
i.name as [index],
h.total_bucket_count,
h.empty_bucket_count,
FLOOR((
CAST(h.empty_bucket_count as float) /
h.total_bucket_count) * 100)
as [empty_bucket_percent],
h.avg_chain_length,
h.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats as h
INNER JOIN sys.indexes as i
ON h.object_id = i.object_id
AND h.index_id = i.index_id
INNER JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
INNER JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];
이 쿼리의 결과를 해석하는 방법에 대한 자세한 내용은 메모리 최적화 테이블에 대한 해시 인덱스 문제 해결을 참조 하세요.
B. 내부 테이블에 대한 해시 인덱스 통계
특정 기능은 해시 인덱스를 사용하는 내부 테이블(예: 메모리 최적화 테이블의 columnstore 인덱스)을 사용합니다. 다음 쿼리는 사용자 테이블에 연결된 내부 테이블의 해시 인덱스에 대한 통계를 반환합니다.
SELECT
QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [user_table],
ia.type_desc as [internal_table_type],
i.name as [index],
h.total_bucket_count,
h.empty_bucket_count,
h.avg_chain_length,
h.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats as h
INNER JOIN sys.indexes as i
ON h.object_id = i.object_id
AND h.index_id = i.index_id
INNER JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
INNER JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type!=1
ORDER BY [user_table], [internal_table_type], [index];
내부 테이블의 인덱스 버킷 수는 변경할 수 없으므로 이 쿼리의 출력은 정보로만 간주되어야 합니다. 사용자가 조치할 필요는 없습니다.
내부 테이블에서 해시 인덱스를 사용하는 기능을 사용하지 않는 한 이 쿼리는 행을 반환하지 않을 것으로 예상됩니다. 다음 메모리 최적화 테이블에는 columnstore 인덱스가 포함되어 있습니다. 이 테이블을 만든 후 내부 테이블에 해시 인덱스가 표시됩니다.
CREATE TABLE dbo.table_columnstore
(
c1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
INDEX ix_columnstore CLUSTERED COLUMNSTORE
) WITH (MEMORY_OPTIMIZED=ON);