MS-SQL 실행 계획 확인하기 : Index Seek, Index Scan

늘 약했던 DB, 실행 계획을 조금 더 이해하고 능동적인 개선을 실행하자.

 

Scouter 보고 있던 중 실행 계획, 바인딩 변수, 정적 실행에 대해 이야기 나누던 중 SSMS(Microsoft SQL Server Management Studio) 사용방법에 대해 코칭을 받게 되었다.

기본적으로 WHERE 절과, scala query가 포함된 SQL 구문으로 이야기가 이어졌다.

1. 바인딩 변수

   실행계획 cost 계산 시 해당 컬럼의 분포도만 가지고 실행 계획을 산정한다.
   
   

2. 정적인 변수
   
   컬럼에 해당 값이 어느정도 있는지 확인 후 cost 계산을 산정한다.

 

1번 테이블을 기준으로 2번 테이블을 출력하는 과정이며,

조회하고자 하는 컬럼은 1번 테이블의 인덱스 설정이 되어있는 1_SEQ 컬럼과 스칼라 서브쿼리의 컬럼이라고 설정한다.

 

SELECT TOP 1 1_F_SEQ, (SELECT 2_COL

                       FROM 2_TABLE

                       WHERE 2_SEQ = 1_JOIN_COL)

FROM 1_TABLE

WHERE 1_A = :1

AND 1_JOIN_COL = :2

AND 1_S_IDX = :3

ORDER BY 1_F_SEQ DESC

 

1_TABLE에서 WHERE절을 통해 가져온 값으로

2_TABLE의 INDEX SEEK을 하게 되는데 이번 내용은 Index의 scan과 seek이다.

Index Scan: 해당 테이블의 컬럼을 모두 살펴본다.

Index Seek: 해당 인덱스를 바로 찾아간다.

 

기본 설명만 봐도 seek으로 표현하는 것이 비용이 적게드는 것을 알 수 있다.

또 각각의 바인딩 변수는 분포도 정도의 기준으로 비용(cost)을 계산한다. 

실제 B 테이블에서 indexing column은 2개의 쌍을 하나로 설정한 상태라 두 인덱스를 같이 조회하는 것이 assert, stream aggregate를 제외한 조회가 가능하였다.

스칼라 서브쿼리에서의 해당 예제)

WHERE 2_SEQ = 1_JOIN_COL AND 2_F_IDX = 'VALUE' 

2번 테이블의 index_keys를 조회하면 두 가지가 묶여서 선언되어 있다.

2_SEQ, 2_F_IDX

두 개로 묶어 조회한 결과

테이블 '2_TABLE'. 검색 수 0, 논리적 읽기 수 2
테이블 '1_TABLE'. 검색 수 1, 논리적 읽기 수 4

 

※ 레거시 코드 중 index로 선언되어 있던 컬럼 2_SEQ를 1_JOIN_COL과 타입을 맞추려는 목적으로 CONVERT(varchar, 2_SEQ) = 1_JOIN_COL 형식으로 구현하고 경우 파라미터는 binding의 경우 1~3ms 소요될 쿼리가 95~100 ms 소요된 사례가 있었다.

 아래 내용을 보면 Stream Aggregate에서 비용이 4% 발생한 것을 확인할 수 있는데 그 이전에 인덱스를 전부 스캔한 뒤 행 그룹에 대한 요약값을 계산하는 데 소비된 비용이다.

Index Column 타입변경 후 실행계획

 

테이블 '1_TABLE'. 검색 수 1, 논리적 읽기 수 4
테이블 '2_TABLE'. 검색 수 1, 논리적 읽기 수 4

 

두 내용을 비교해보면 1번 테이블의 검색 수와 논리적 읽기 수가 줄어든 것을 확인할 수 있다.

위 쿼리는 top 1으로 하나의 ROW만 가져오는 쿼리기 때문에 0, 1로 확인 되지만 ROW가 많을수록 이 값도 증가한다.(당연한 얘기)

 

• index 설정한 컬럼을 같이 엮어서 조회하면 실행계획에서 단계를 줄일 수 있다.
• index 설정된 컬럼의 타입을 변경하면 인덱스의 효과를 얻지 못한다.(인덱스를 스캔함)

쿼리 실행 시 타입을 맞춰줘야 한다는 초점에서 실행계획에 개선이 될 수 있는 타입 변경으로 시야를 넓히고,

이번 기회를 통해 ssms의 간단한 조작으로 실행 계획을 쉽게 볼 수 있었구나 깨닫게 되었다.