[MSSQL] SQL Server 튜닝 - 실행 계획

목차

시작하며

DB팀과 협업을 하며 자주 이야기하던 키워드가 바로 "실행계획" 이다.

쿼리를 만들고 난 후, 실행계획을 점검해보며 인덱스를 잘 타는지, 쿼리가 잘못되진 않았는지 등등을 체크하곤 하는데, 이번엔 이 부분을 다뤄보고자 한다.

 

그리고, 지난번 인덱스를 공부하며 사용했던 쿼리들의 실행계획도 재확인해볼 생각이다.

실행 계획(Execution Plan) 이란?

실행 계획은 SQL 구문을 처리하기 위해 사용되는 연산 방법이나 오브젝트를 읽어내는 순서 등이 조합된 일련의 처리 절차이다.

하나의 SQL 구문을 처리하는 실행 계획은 많은 경우의 수로 다양하게 생길 수 있지만, 내부적인 규칙이나 비용을 계산하여 가장 최적이라 판단되는 계획이 선택된다.

 

그렇다면 옵티마이저는 항상 최선의 실행 계획을 선택할 수 있을까?

 

결론은 그렇지 않다. SQL의 작성 방법이나 오브젝트의 통계(Statistics) 정보, 혹은 사용자가 설정한 옵션 정보 등의 다양한 이유로 실행 계획이 달라질 수 있기 때문이다.

이때문에 비용이 낮은 계획을 선택하려 노력해도 환경에 따라서는 최선의 계획을 선택하지 못하는 경우가 생기게 된다.

 

실행 계획의 중요성

실행 계획이 잘못되었다는 것은 결국 SQL을 처리하기 위해 불필요한 리소스가 투입되었다는 의미이다.

 

예를 들어, 100만건의 데이터 중 1건의 데이터를 추출하려고 한다.

이는 인덱스를 통해서 1건의 데이터를 탐색하고 RID Lookup을 수행하는 것으로 낮은 비용을 사용하여 처리할 수 있다.

 

하지만, 잘못된 통계 정보로 인해 조건에 해당하는 데이터가 90만건이 존재한다고 계산이 되면 어떻게 될까?

 

실행 계획은 통계 정보를 참조하기 때문에, 90만 건에 대해서 RID Lookup 이 수행될 것을 예상하고 이보다는 테이블 전체를 스캔하는 것이 더 효율적이라고 판단할 것이다.

 

결국, 잘못된 실행 계획이 생성되어 1건의 데이터를 찾기 위해 100만건을 Full Scan 하는 상황이 초래된다.

 

이처럼 실행 계획은 SQL을 처리하는 데 있어서 리소스를 얼마나 사용하는가에 대해 중요한 역할을 하게된다.

 

실행 계획 확인 방법

실행 계획은 예상 실행 계획과 실제 실행 계획이 있다.

 

예상 실행 계획은 SQL을 실행하지 않고 처리될 것으로 예상되는 계획을 보여준다.

실제 실행 계획은 SQL이 실제로 실행된 계획을 보여준다.

 

이는 그래픽과 텍스트 모드로 각각 확인할 수 있는데, 최대한 간단하게 적고 넘어가도록 하겠다.

 

첨부되는 사진은 SSMS와, Azure Data Studio 가 혼합되어있다.

1. 그래픽 예상 실행 계획

예상 실행 계획

예상 실행 계획은 각 연산자의 속성 정보를 통해 예상 비용을 확인할 수 있지만, SQL이 수행되지 않기 때문에 실제 사용된 비용은 확인할 수 없다

 

2. 그래픽 실제 실행 계획

실제 실행 계획

실제 실행 계획은 SQL이 수행된 정보를 출력하기 때문에 각 연산자의 속성 정보를 통해 예상 비용과 함께 실제 비용을 확인할 수 있다.

 

3. 텍스트 예상 실행 계획

SET SHOWPLAN_ALL ON/OFF

위 명령을 통해 예상 실행 계획을 활성화시킬 수 있다.

 

텍스트 예상 실행 계획은 SQL을 실행하지 않기 때문에 조회 결과는 출력되지 않으며 텍스트를 통해 예상 처리 절차와 함께 예상 비용 정보를 텍스트로 출력한다.

 

4. 텍스트 실제 실행 계획

SET STATISTICS PROFILE ON/OFF

위 명령을 통해 실제 실행 계획을 활성화시킬 수 있다.

 

 

텍스트 실제 실행 계획은 SQL이 실행되어 조회된 결과와 함께 출력되며 텍스트를 통해 실제 처리절차와 함께 실제 비용의 일부를 출력한다.

 

5. 실제 I/O 비용과 수행시간

SET STATISTICS IO ON/OFF

SQL을 처리하면서 사용된 I/O 비용과 수행 시간도 SET STATISTICS 명령을 통해 확인할 수 있다.

 

이는 예상 실행 계획이 활성화된 상태에서는 확인할 수 없으며 SQL이 실제로 처리된 이후에 출력된다.

 

SQL에서 사용된 테이블이 사용한 페이지 수를 논리적/물리적 구분에 따라 확인할 수 있다.

 

SET STATISTICS TIME ON/OFF

SQL이 수행될 때의 구문 분석, 컴파일 시간과 함께 총 수행 시간을 확인할 수 있다.

 

실행 계획을 읽는 방법

실행 계획을 확인하는 방법엔 크게 두가지가 있다.

 

그래픽 실행 계획과 텍스트 실행 계획이다.

 

그래픽 실행 계획의 실행 순서

위 그래픽 실행 계획은 가장 우측 상단에 있는 Index Seek부터 수행하고 RID Lookup을 중첩 루프로 조인하여 최종 출력하는 순서를 나타낸다.

 

텍스트 실행 계획의 실행 순서

텍스트 실행 계획도 마찬가지로 상단에서 하단을 기준으로 우측(안쪽)부터 좌측(바깥쪽) 순으로 실행된다.

가장 안쪽에 있는 Index Seek를 읽는 것부터 시작하여 그래픽 실행 계획과 동일한 처리 과정을 나타낸다.

실행 계획에 따른 SQL의 처리과정

아래 쿼리로 테스트를 하기 위한 테이블을 생성하고 예제를 확인해보자.

SELECT number AS seq
      ,low AS value
      ,CONVERT (NCHAR(100), newid()) AS code
      ,CONVERT (DATE, DATEADD(DD, [number] -1, '2020-01-01')) AS date
      INTO TB_First
    FROM master.dbo.spt_values
    WHERE type = 'P' 
        AND [number] BETWEEN 1 AND 2000
	
SELECT *
      ,CONVERT (NCHAR(100), newid()) AS txt 
      INTO TB_Second
    FROM TB_First 
    WHERE seq % 2 = 1

CREATE INDEX NIDX01_First On TB_First(date)
CREATE INDEX NIDX01_Second On TB_Second(seq) INCLUDE (code)

 

1.  단일문

단일문은 다른 테이블과의 조인 없이 하나의 테이블만 읽어내며 결과를 출력하는 구문이다.

SELECT seq
      ,value
      ,date
    FROM TB_First 
    WHERE date BETWEEN '2020-01-01' AND '2020-01-07'

다음은 단일문을 수행했을 때의 처리 절차이다.

1. [date] BETWEEN '2020-01-01' AND '2020-01-07' 조건에 만족하는 데이터를 [NIDX01_First] 인덱스로 탐색한다.
2. [NIDX01_First] 인덱스로부터 탐색한 행의 RID 값으로 [TB_First] 테이블에 RID Lookup을 수행한다.
3. Nested Loop Join 방식으로 (1) 에서 탐색된 행 수만큼 (2)를 반복 수행하면서 결과 집합을 만든다
4. 최종 결과 집합에서 [seq], [value], [date]의 값을 출력한다.

 

2.  조인문

조인문은 두 테이블 이상이 결합하여 하나의 결과 집합을 만드는 구문이다.

SELECT s.code
    FROM TB_First f 
        INNER JOIN TB_Second s
        ON f.seq = s.seq
    WHERE f.[value] = 1

다음은 조인문을 수행했을 떄의 처리 절차이다.

1. [TB_First] 테이블 전체를 스캔하면서 [value] = 1 조건에 만족하는 행을 찾는다.
2. [TB_First] 테이블에서 읽은 [seq] 값을 기준으로 [NIDX01_Second] 인덱스를 탐색한다.
3. 두 테이블의 조인 방식이 Nested Loop Join 임을 나타내며 (1) 에서 찾은 데이터의 행 수만큼 (2) 를 반복하여 조인된 결과 집합을 만든다.
4. 최종 결과 집합에서 [code] 값을 출력한다.

 

3. 집계 함수

집계 함수는 조건을 만족하는 데이터를 기준으로 합계, 평균, 최소, 최대값 등을 출력하는 함수이다.

SELECT SUM(value)
    FROM TB_First
    WHERE seq <= 100

다음은 집계 함수 SUM()을 사용했을 때의 처리 절차이다.

1. [TB_First] 테이블 전체를 스캔하여 [seq] <= 100 조건에 만족하는 행을 찾는다.
2. 조건에 만족하는 행들에 대해 총 행 수를 [Expr1009] 변수에, 합한 값을 [Expr1010] 변수에 저장한다.
3. [Expr1009] 변수를 확인하여 0건일 경우 NULL을, 아닐 경우 합계를 결과 집합에 담는다
4.  최종 결과 집합을 출력한다.

 

4. GROUP BY

GROUP BY는 특정 열을 기준으로 행을 집계하기 위해 사용된다.

집계 함수가 같이 사용되면 GROUP BY 절에 포함된 열을 기준으로 동일한 값마다 집계되며, 단독으로 사용되면 중복 값을 제거하는 DISTINCT와 동일하게 동작한다.

SELECT value
      ,COUNT(*)
    FROM TB_First
    GROUP BY value

다음은 집계 함수 GROUP BY 절과 함께 COUNT 함수를 사용했을 때의 처리 절차이다.

1. [TB_First] 테이블 전체를 스캔한다.
2. [value] 값을 Hash 함수를 적용하여 동일한 값마다 관련 행을 집계하고 [Expr1006] 변수에 저장한다 (단 상황에 따라 Sort가 뜰 수도 있다는걸 명심하자)
3. [Expr1006] 변수값을 최종적으로 출력하기 위해 int형 데이터 타입으로 암시적 형 변환을 수행한다.
4. [value] 값과 함께 해당되는 행의 수를 최종 결과 집합으로 출력한다.

Hash Match 가 아닌, Sort 가 뜨는 경우

위 과정에서 Hash Match가 아닌 Sort가 뜨는 경우가 있다.

만약 테이블 내의 데이터 양이 적어 해시테이블을 만들어 연산하는 비용보다 테이블을 정렬하는 비용이 더 싸다고 판단되는 경우, Sort를 사용하게 된다.

 

연산 비용의 경우, 예상 실행계획을 돌리면 나오는 수치를 보고 서비스 영향도를 고민하게 된다.

 

집계를 하려면 정렬이 필수적인 이유

정렬이 되어있지 않았을 경우, 데이터 페이지를 중복해서 읽어야 하기 때문이다.

 

정렬이 되어있지 않으면 1건을 찾기 위해 모든 데이터 페이지를 스캔해야하고, 그 다음 건수를 찾을때도 같은 방식을 반복해야하기 때문에 정렬이 필수적이라고 볼 수 있다..!

 

5. ORDER BY

ORDER BY 는 특정 열을 기준으로 행을 정렬하기 위해 사용된다.

SELECT code
    FROM  TB_First
    ORDER BY seq ASC

다음은 ORDER BY 절을 사용했을 때의 처리 절차이다.

 

1. [TB_First] 테이블 전체를 스캔한다.
2. [seq] 열을 기준으로 오름차순 정렬한다.
3. 행이 정렬된 상태로 최종 출력한다.