[MSSQL]최적화 단계에서 인덱스 선택(2)

*통계 정보 업데이트의 중요성 
Non-Clustered Index 가 걸린 컬럼으로 조회 시,
최적화 단계에서 통계 정보의 분포도에 따라 실행계획을 수립하게 되는데
이때 통계정보가 업데이트가 안되어 있을 경우 성능 저하를 초래할 수 있다.

테스트용으로,

SELECT * INTO p from tbillmst where payymd between '20040101' and '20101231'
--p테이블에 160000만건 삽입


CREATE  CLUSTERED INDEX cx ON p (UserNo) 

CREATE INDEX ix ON p (payymd)
 --인덱스를 각각 생성하고

DBCC SHOW_STATISTICS(p, ix) 
--통계정보를 확인해보면 

p 테이블의 payymd에 대한 밀도는 0.0005333333

분포도 = 밀도 * 총 행수 = 0.0005333333 * 165120 = 88.06

예상 결과 행수 = 88 이 되고,

이는 전체 행수의 0.43% 미만이기 때문에 인덱스를 사용하도록 옵티마이저는 실행 계획을 수립한다.

select * from p where payymd = '20100301'

 
=>Index seek 사용

통계 정보는 테이블 데이타 행수의 20% 변화가 있을때 자동 업데이트가 발생한다.

하지만 극단적이지만 다음과 같은 업데이트가 일어난다면

UPDATE p

  SET payymd = '20100301'

  WHERE userno <= 5304072

GO
 (위는 20% 미만인 행수가 업데이트 되었다.)

select * from p where payymd = '20100301'
실행시, Index seek 사용
'p' 테이블. 스캔 수 1, 논리적 읽기 수 215494, 물리적 읽기 수 0, 미리 읽기 수 0.

 

옵티마이저는 업데이트 안되어 있는 통계정보를 참조하여 여전히 같은 Non-clustered index를 선택하여
비효율적으로 실행을 하게 한다.


만약 주기적으로 통계정보를 업데이트 해준다면 이 문제는 해결되는 것입니다.
UPDATE STATISTICS p

select * from p where payymd = '20100301'
실행시, Clustered Index Scan 사용
'p' 테이블. 스캔 수 1, 논리적 읽기 수 3409, 물리적 읽기 수 0, 미리 읽기 수 0.