메모리 액세스에 최적화된 해시 인덱스의 일반적인 성능 문제 해결
이 항목에서는 해시 인덱스의 일반적인 문제 해결을 집중적으로 다룹니다.
검색에 해시 인덱스 키 열의 하위 집합 필요
문제: 해시 인덱스에는 해시 값을 계산하고 해시 테이블에서 해당 행을 찾기 위해 모든 인덱스 키 열에 대한 값이 필요합니다. 따라서 쿼리에 WHERE 절의 인덱스 키 하위 집합에 대해서만 같음 조건자가 포함된 경우 SQL Server 인덱스 검색을 사용하여 WHERE 절의 조건자 에 해당하는 행을 찾을 수 없습니다.
반대로 디스크 기반 비클러스터형 인덱스 및 메모리 최적화 비클러스터형 인덱스와 같은 정렬된 인덱스는 인덱스 키 열의 하위 집합에서 Index Seek를 지원합니다(해당 열이 인덱스의 선행 열인 한에서).
증상: 따라서 SQL Server 일반적으로 더 빠른 작업인 인덱스 검색이 아닌 전체 테이블 검사를 실행해야 하므로 성능이 저하됩니다.
문제 해결 방법: 성능 저하 외에도 쿼리 계획을 검사하면 인덱스 검색 대신 검사가 표시됩니다. 쿼리가 매우 간단한 경우 쿼리 텍스트와 인덱스 정의를 검사해도 검색에 인덱스 키 열의 하위 집합이 필요한지 여부가 표시됩니다.
다음 테이블과 쿼리를 살펴 보십시오.
CREATE TABLE [dbo].[od]
(
o_id INT NOT NULL,
od_id INT NOT NULL,
p_id INT NOT NULL,
CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id, od_id) WITH (BUCKET_COUNT = 10000)
)
WITH (MEMORY_OPTIMIZED = ON)
SELECT p_id
FROM dbo.od
WHERE o_id=1
테이블에는 두 열(o_id, od_id)에 해시 인덱스가 있지만 쿼리에는 (o_id)에 같음 조건자가 있습니다. 쿼리에는 인덱스 키 열의 하위 집합에만 같음 조건자가 있으므로 SQL Server PK_od 사용하여 인덱스 검색 작업을 수행할 수 없습니다. 대신 SQL Server 전체 인덱스 검사로 되돌리기 합니다.
대안: 여러 가지 가능한 해결 방법이 있습니다. 예를 들면 다음과 같습니다.
비클러스터형 해시 대신 비클러스터형 유형으로 인덱스를 다시 만듭니다. 메모리 최적화 비클러스터형 인덱스는 순서가 지정되므로 SQL Server 선행 인덱스 키 열에서 인덱스 검색을 수행할 수 있습니다. 결과적으로 이 예의 기본 키 정의는
constraint PK_od primary key nonclustered가 됩니다.현재 인덱스 키를 WHERE 절의 열과 일치하도록 변경합니다.
쿼리의 WHERE 절에 있는 열과 일치하는 새 해시 인덱스를 추가합니다. 이 예에서는 결과 테이블 정의가 다음과 같은 모양이 됩니다.
CREATE TABLE dbo.od ( o_id INT NOT NULL, od_id INT NOT NULL, p_id INT NOT NULL, CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id,od_id) WITH (BUCKET_COUNT=10000), INDEX ix_o_id NONCLUSTERED HASH (o_id) WITH (BUCKET_COUNT=10000) ) WITH (MEMORY_OPTIMIZED=ON)
메모리 최적화 해시 인덱스는 지정된 인덱스 키 값에 대해 중복 행이 많은 경우 최적으로 수행되지 않습니다. 예제에서 열 o_id 고유 값 수가 테이블의 행 수보다 훨씬 작은 경우 (o_id)에 인덱스를 추가하는 것이 최적이 아닙니다. 대신 인덱스 PK_od 형식을 해시에서 비클러스터형으로 변경하는 것이 더 나은 솔루션입니다. 자세한 내용은 Determining the Correct Bucket Count for Hash Indexes을 참조하세요.