1 개요

2 선택도에 따른 실행계획의 변경 예제

3 선택도에 따른 실행계획의 분리

4 결론

MSSQL SERVER의 인덱스는 클러스터드 인덱스와 논클러스터드 인덱스 이렇게 2가지가 있다. 클러스터드 인덱스는 테이블당 1개만 존재할 수 있으며, 논클러스터드 인덱스는 256개 까지 만들 수 있다. 데이터베이스 개체에는 모든 테이블, 뷰, 저장 프로시저, 확장 저장 프로시저, 트리거, 규칙, 기본값, 제약 조건 등이 포함된다. 한 데이터베이스에서 이러한 모든 개체 수의 합계는 2,147,483,647을 넘을 수 없다.

SQL 튜닝 관점에서 중요한 것은 선택도에 따른 옵티마이저의 실행계획 수립이다. 논클러스터드 인덱스의 경우 index seek의 예상비용이 scan 예상비용보다 적으면 seek한다. 또한 병렬쿼리 인가와 직렬쿼리이냐에 따라서도 다르게 실행계획이 세워진다. SQL의 작성에 따라서 인덱스의 사용여부가 결정되는 것은 당연하다. 선택도는 다음과 같이 얻을 수 있다.

아래는 해당 컬럼에 따른 중복된 행 수를 반환한 예이다. 전체 행수는 54097개로 ITEMGROUP이 ‘BODYPLY1’인 경우는 선택도가 0.12다.

SELECT 6588.000 / 54097.000
 
--------------------- 
.121781244801
 
(1개 행 적용됨)

full scan 비용보다 index seek의 비용이 높을 것이므로 옵티마이저는 full scan을 선택할 것이다.

SELECT * FROM ITEMSITEMASTER
WHERE ITEMGROUP = 'BODYPLY1'

선택도가 좋은 ‘BUT’의 경우는 다른 실행계획을 세워 결과를 리턴 할 것이다.

SELECT * FROM ITEMSITEMASTER
WHERE ITEMGROUP = 'BUT'

위의 예와 같은 경우 특정 몇 종류만이 선택도가 좋지 못하고, 다른 것들은 선택도가 좋다. 대부분의 경우는 MSSQL SERVER의 자동매개변수화에 의해 INDEX SEEK를 하는 실행계획을 세울 것이다. 하지만 index seek보다 full scan이 유리한 (index seek와 full scan의 손익분기점 이상) 조건이 들어왔을 때는 풀스캔이 유리함에도 불구하고 INDEX SEEK를 한다. 아래의 경우가 그런 경우이다.

-- 1
DECLARE @ITEMGROUP VARCHAR(20)
SET @ITEMGROUP = 'BUT'
SELECT * FROM ITEMSITEMASTER
WHERE ITEMGROUP = @ITEMGROUP
 
-- 2
DECLARE @ITEMGROUP VARCHAR(20)
SET @ITEMGROUP = 'BODYPLY1'
SELECT * FROM ITEMSITEMASTER
WHERE ITEMGROUP = @ITEMGROUP

1의 경우는 INDEX SEEK를 한다. MSSQL SERVER는 실행계획을 재사용하여 2의 경우에도 INDEX SEEK를 한다. 논리적 읽기 수를 잘 봐두기 바란다.

'ITEMSITEMASTER' 테이블. 스캔 수 1, 논리적 읽기 수 8, 물리적 읽기 수 0, 미리 읽기 수 0.

'ITEMSITEMASTER' 테이블. 스캔 수 1, 논리적 읽기 수 20683, 물리적 읽기 수 0, 미리 읽기 수 0.

그러므로 이런 경우는 UNION ALL을 이용하여 실행계획을 분리하여 사용하게 유도하면 된다.

DECLARE @ITEMGROUP VARCHAR(20)
SET @ITEMGROUP = 'BODYPLY1' --'BUT'
 
SELECT * FROM ITEMSITEMASTER
WHERE ITEMGROUP = @ITEMGROUP
AND ITEMGROUP = 'BUT'
UNION ALL
SELECT * FROM ITEMSITEMASTER (INDEX = 1)
WHERE ITEMGROUP = @ITEMGROUP
AND ITEMGROUP = 'BODYPLY1'

'ITEMSITEMASTER' 테이블. 스캔 수 2, 논리적 읽기 수 4319, 물리적 읽기 수 0, 미리 읽기 수 0.

입/출력의 양을 비교해보면 다음과 같이 약 5배의 차이가 나는 것을 알 수 있다.

SELECT 20683.000 / 4319.000
 
--------------------- 
4.78884000926

요즘은 이러한 실행계획의 재사용으로 인해 실제 현실적으로 풀스캔이 유리함에도 불구하고 Index Seek를 하는 현상을 '매개변수 스니핑'이라고 부르더라. 저장 프로시저에서 매개변수 스니핑을 없애려면 저장 프로시저 호출시 매번 컴파일을 하게 하면 된다. 하지만 매번 저장 프로시저를 재컴파일하는 것은 비용이 많이 드는 일이므로 적당히 실행계획에 따라서 SQL을 분리하는 것이 유리할 것이다. 프로시저 재컴파일에 관련해서는 다음의 내용을 참고하라.

일반적인 프로시저 재컴파일의 원인(http://support.microsoft.com/kb/308737/ko)

• 스키마 변경
  
• 통계 변경
  
• 지연된 컴파일
  
• SET 옵션 변경
  
• 임시 테이블 변경
  
• 원격 행 집합 변경
  
• 찾아보기 권한 변경
  
• 쿼리 알림 환경 변경
  
• 분할 뷰 변경
  
• 커서 옵션 변경
  
• 옵션(recompile) 요청