변수 사용에 따른 카디널리티 추정 이슈

1. 사전구성

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

• 아래의 방법을 고려해볼 수 있다.
  1. Histogram을 활용할 수 있도록 저장 프로시저를 사용하는 방법
     • 저장 프로시저는 매개변수로 전달된 값을 가공하지 않는다면 Histogram을 참조할 수 있다.
     • 통계를 활용할 수 있기 때문에 [gid]=1 에 해당하는 데이터가 1건이라는 것을 확인하여 더 나은 실행 계획을 생성한다.

     • 

  2. UNION ALL 연산을 통해 바인드 값에 따라 다른 구문으로 처리될 수 있도록 변경하는 방법
     • [gid] 컬럼의 데이터 분포도에 따라 다른 처리 방식으로 수행될 수 있도록 구문을 변경한다.
     • UNION ALL 기준으로 상단 구문은 카디널리티가 다시 계산되면서 약 37건의 행을 예상하여 인덱스 탐색을 선택하고, 하단 구문은 약 27만 건의 데이터를 예상하여 테이블 전체 스캔을 선택한다.

     • 

  3. [txt] 컬럼을 인덱스에 포함시켜 RID Lookup이 발생되지 않도록 하는 방법.

     •