Ref.
SQL SERVER 튜닝가이드 실습 3

인덱스를 사용하지 못하는 경우

• 조건절에 사용된 컬럼을 키로 하는 인덱스가 존재함에도 테이블을 전체 스캔하여 과다한 I/O를 사용한 사례

SELECT A.*,
	   B.NUM,
	   B.NO
  FROM TB_DATA01 A
INNER JOIN TB_DATA02 B
	ON A.NUM = B.NUM AND
	   B.NUM < 30

인덱스 정보

Table_Name	Index_Name	Type_Desc	Is_Unique	Key_List	Include_List
TB_Data02	NIDX01_Data02	NONCLUSTERED	0	num	-

실행계획

1. Table Scan이 실행된 이유: Select에 no컬럼을 표출해야하기 때문.
   • Index를 활용한 Index탐색을 실행할 경우 no컬럼으로 생성된 Index가 없기 때문에 RID Lookup 연산이 추가적으로 필요한 상황. → 옵티마이저가 Table Scan이 더 효율적이라고 판단하여 실행됨.

튜닝 포인트

no컬럼으로 인한 Lookup을 제거하고 인덱스 탐색으로 수행되도록 하기 위해 인덱스 포괄(Include)열로 추가하거나 클러스터형 인덱스로 생성한다.

1.
DROP INDEX NIDX01_DATA02 ON TB_DATA02
CREATE INDEX NIDX01_DATA02 ON TB_DATA02(NUM) INCLUDE(NO)

2.
DROP INDEX NIDX_DATA02 ON TB_DATA02
CREATE INDEX NIDX01_DATA02 ON TB_DATA02(NUM)
CREATE CLUSTERED INDEX CIDX_DATA02 ON TB_DATA02 (NO)

CROSS APPLY를 이용한 부분 범위 처리

• 조인 조건으로 전달받는 값마다 상위 N개의 데이터를 출력할 때 많은 스캔 범위로 인하여 불필요한 I/O가 발생되는 사례

SELECT ID,
	   CODE,
	   DATE
  FROM (SELECT F.ID,
			   S.CODE,
			   S.DATE,
			   ROW_NUMBER() OVER (PARTITION BY S.ID ORDER BY S.DATE DESC) AS RN
		  FROM TB_CAFIRST F
	INNER JOIN TB_CASECOND S
			ON F.ID = S.ID
		 WHERE F.GID = 1
				) A
	WHERE A.RN <= 5

인덱스 정보

Table_Name	Index_Name	Type_Desc	Is_Unique	Key_List	Include_List
TB_CAFirst	NIDX01_CAFirst	NONCLUSTERED	0	GID	ID
TB_CAFirst	PK_CAFirst	CLUSTERD	1 (중복 x)	ID	-
TB_CASecond	NIDX01_CASecond	NONCLUSTERED	0	ID,DATE	CODE

외래 키 정보

Table_Name	Constraint_Type	Constraint_Name	Constraint_Keys
TB_CASecond	FOREIGN_KEY	FK_CASecond	id(REFERENCES Tuning.dbo.TB_CAFirst(id))

튜닝 포인트

CROSS APPLY 조인 방식을 사용하여 TOP N 쿼리를 통해 상위 5건만 읽어낼 수 있도록 구문을 변경한다.

SELECT ID,
	   CODE,
	   DATE
  FROM TB_CAFIRST F
CROSS APPLY ( SELECT TOP 5 
					 CODE, 
					 DATE
				FROM TB_CASECOND S
			   WHERE F.ID = S.ID
			ORDER BY DATE DESC 
			) S
 WHERE F.GID = 1