SELECT CASE WHEN seq = 1 THEN 1
WHEN seq <= 11 THEN 2
WHEN seq <= 111 THEN 3
WHEN seq <= 1111 THEN 4
WHEN seq <= 11111 THEN 5
WHEN seq <= 111111 THEN 6
WHEN seq <= 1111111 THEN 7
END AS gid
, NEWID() AS txt
, GETDATE() AS date
INTO TB_Hist
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY a.number) seq
FROM master..spt_values a, (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 1000) b
WHERE a.type = 'P'
AND a.number BETWEEN 1 AND 1200) c
WHERE seq BETWEEN 1 AND 1111111;
CREATE INDEX IDX01_Hist ON TB_Hist (gid);
table_name
index_name
index_description
Is_Unique
indexed_columns
included_columns
TB_Hist
IDX01_Hist
nonclustered
0
gid
[NULL]
All density
Average Length
Columns
0.1428571492
4
gid
RANGE_HI_KEY
RANGE_ROWS
EQ_ROWS
DISTINCT_RANGE_ROWS
AVG_RANGE_ROWS
1
0
1
0
1
2
0
10
0
1
3
0
100
0
1
4
0
1,000
0
1
5
0
10,000
0
1
6
0
100,000
0
1
7
0
1,000,000
0
1
2. AS-IS
DECLARE @int INT = 1
;
SELECT gid, txt
FROM TB_Hist
WHERE gid = @int;
논리적 읽기수 5097
[gid] 컬럼이 키로 구성된 인덱스가 존재하며 [gid]=1 에 해당하는 행은 1건이지만 테이블 전체를 스캔하는 실행 계획으로 수행되었다.
바인드 변수가 사용되어 Histogram 데이터를 활용하지 못 하고 카디널리티으로 계산된 약 15만 건의 결과 집합이 생성될 것이라 예상되었다.
인덱스에 포함되지 않은 [txt] 컬럼을 출력하기 위해 RID Lookup이 15만 번 수행될 것으로 예상하여 테이블 전체를 스캔했다.
3. TO_BE
// 1) Histogram을 활용할 수 있도록 저장 프로시저를 사용하는 방법
CREATE PROC USP_Hist @int INT
WITH RECOMPILE
AS
SELECT gid, txt
FROM TB_Hist
WHERE gid = @int
;
EXEC USP_Hist 1;
// 2) UNION ALL 연산을 통해 바인드 값에 따라 다른 구문으로 처리될 수 있도록 변경하는 방법
DECLARE @int INT = 1
;
SELECT gid, txt
FROM TB_Hist
WHERE gid IN (1,2,3) AND gid = @int
UNION ALL
SELECT gid, txt
FROM TB_Hist
WHERE gid NOT IN (1,2,3) AND gid = @int;
// 3) [txt] 컬럼을 인덱스에 포함시켜 RID Lookup이 발생되지 않도록 하는 방법
CREATE INDEX NIDX02_Hist ON TB_Hist (gid) INCLUDE (txt);
아래의 방법을 고려해볼 수 있다.
Histogram을 활용할 수 있도록 저장 프로시저를 사용하는 방법
저장 프로시저는 매개변수로 전달된 값을 가공하지 않는다면 Histogram을 참조할 수 있다.
통계를 활용할 수 있기 때문에 [gid]=1 에 해당하는 데이터가 1건이라는 것을 확인하여 더 나은 실행 계획을 생성한다.
UNION ALL 연산을 통해 바인드 값에 따라 다른 구문으로 처리될 수 있도록 변경하는 방법
[gid] 컬럼의 데이터 분포도에 따라 다른 처리 방식으로 수행될 수 있도록 구문을 변경한다.
UNION ALL 기준으로 상단 구문은 카디널리티가 다시 계산되면서 약 37건의 행을 예상하여 인덱스 탐색을 선택하고, 하단 구문은 약 27만 건의 데이터를 예상하여 테이블 전체 스캔을 선택한다.
댓글