실행 계획을 어떻게 읽고 계신가요? 오른쪽에서 왼쪽으로? 아니면 비용(Cost) 위주로 확인하시나요? 인덱스 스캔(Index Scan), 테이블 스캔(Table Scan), 룩업(Lookup) 같은 객체들은 어떻게 해석해야 할까요? 이 글에서는 Microsoft® SQL Server 실행 계획을 올바르게 읽고 분석하는 방법을 자세히 알아봅니다.
들어가며
SQL Server는 대체로 좋은 실행 계획을 생성하지만, 때로는 자신의 계획을 검증하고 잘못된 계획을 수정할 만큼 똑똑하지 못할 때가 있습니다.
SQL Server에서는 예상 실행 계획(Estimated Execution Plan)과 실제 그래픽 실행 계획(Actual Graphical Execution Plan) 두 가지를 얻을 수 있습니다. SSMS(SQL Server Management Studio) 표준 도구 모음에서 실행 아이콘 오른쪽에 위치한 아이콘을 사용하거나, 단축키 Ctrl + M(실제 계획 포함) 또는 Ctrl + L(예상 계획)을 눌러 생성할 수 있습니다. SQL Server에는 이 외에도 여러 유형의 계획이 있지만, 이 글에서는 다루지 않습니다.
예상 실행 계획과 실제 실행 계획
실행 계획에는 두 가지 유형이 있습니다.
- 예상 실행 계획: 쿼리를 실제로 실행하기 전에, 데이터를 가져오기 위해 SQL Server가 수행할 것으로 예상되는 작업량을 보여줍니다.
- 실제 실행 계획: Transact-SQL 쿼리나 배치가 실행된 후 생성됩니다. 따라서 실제 리소스 사용 지표와 런타임 경고 등 실제 실행 정보를 함께 담고 있습니다.
데이터 처리량 분석
동일한 쿼리에 대해 예상 계획과 실제 계획의 차이를 발견해본 적이 있으신가요? 대부분의 경우 두 계획은 동일하지만, 통계(statistics) 변경, 스키마 관련 변경, 또는 데이터 변화로 인해 달라질 수 있습니다. 문제 해결 시에는 반드시 실제 실행 계획을 확인해야 합니다.
정확한 원인을 파악하려면 실행 계획을 올바르게 읽어야 합니다. 비용보다 먼저 데이터 흐름(Data Flow)에 주목하세요. 논리적/물리적 읽기 수치부터 고민하기보다, 입출력(I/O) 작업 수를 줄이는 것이 핵심입니다. DBA라면 저장소 접근이 하드웨어 자원 중 가장 느리다는 사실을 알고 있을 것입니다. 따라서 I/O 활동을 최소화하는 것이 최우선 과제입니다.
그렇다면 통계를 어떻게 탐색할 수 있을까요? 실행 계획에서도 확인 가능할까요? 네, 가능합니다! 화살표 선에 마우스를 올려보세요. 레코드 수와 데이터 크기를 표시해주며, 각 연산자가 반환하는 데이터 양에 따라 선의 굵기가 두꺼워지거나 얇아집니다. 아래 그림과 같습니다:
객체가 많다면 각 연산자가 처리하는 데이터 양을 한눈에 파악할 더 좋은 방법이 필요합니다. SentryOne Plan Explorer를 다운로드하여 이 도구로 실행 계획을 열어보면 손쉽게 전체적인 개요를 파악할 수 있습니다.
SentryOne을 설치하고 구성한 후 실행 계획을 열어보세요. SentryOne은 다양한 뷰와 설명 기능을 제공합니다. 데이터 처리량 뷰를 확인하려면 아래 이미지처럼 Data size in MB 옵션으로 뷰를 변경하면 됩니다. 목표는 전체 데이터 처리량을 줄일 수 있는 기회를 찾는 것입니다.
I/O 부하를 줄여야 한다면 SET STATISTICS IO ON T-SQL 값을 활용하여 쿼리의 전체적인 I/O 사용량을 파악할 수 있습니다. SSMS에서 쿼리 실행 전에 이 설정을 적용하고, 결과 패널의 메시지(Messages) 탭으로 전환하여 확인하면 됩니다. 결과는 대략 다음과 같이 나타납니다:
(356 row(s) affected)
Table 'Table3'. Scan count 1, logical reads 5, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
logical reads 5라는 결과는 SQL Server가 메모리에서 데이터를 가져오기 위해 40KB(5 × 8KB) 페이지를 읽었다는 의미입니다. 쿼리를 최적화할 때 논리적 읽기를 무시해서는 안 됩니다. 실제 운영 서버에서는 해당 페이지가 메모리에 있는지 디스크에 있는지 알 수 없으며, 논리적 읽기가 곧 물리적 읽기로 이어질 수 있기 때문입니다. 결국 목표는 누적 읽기 작업 수를 줄이는 것입니다.
예상 계획 vs 실제 계획 비교
실제 계획이 생성되면 실행 계획의 임의의 연산자나 논리 단위 위에 마우스를 올려보세요. 아래 이미지와 같이 Clustered Index Seek 또는 Scan 같은 물리 연산 유형을 확인할 수 있습니다. 특히 실행 횟수(Number of Executions)와 실제 행 수 및 예상 행 수를 주목하세요. 개별 연산자의 비용은 무시하는 것이 좋습니다. 이는 오래된 하드웨어 기준의 초 단위 추정치일 뿐 정확한 정보를 제공하지 않기 때문입니다. 아래 예시에서 Clustered Index Seek 연산자는 한 번 실행되어 100개의 레코드를 가져왔지만, SQL Server는 356개의 레코드로 예측했습니다. 이런 차이는 오래된 통계 또는 쿼리 성능 문제 때문일 수 있습니다.
실행 반복 횟수의 함정
아래 이미지는 클러스터드 인덱스에서 단 하나의 레코드를 가져오기 위해 예상 1,154,121번의 실행이 발생한 경우를 보여줍니다. 상당히 심각한 상황입니다. 이 연산자의 비용이 낮아 단순히 무시하고 비용이 높은 다른 연산자에만 집중했다면, 값비싼 실수를 저질렀을 수 있습니다. 클러스터드 인덱스가 있음에도 불구하고 이런 연산자는 쿼리 성능에 큰 영향을 미칩니다. RID(Row ID) 룩업도 힙(Heap) 테이블에서 유사하게 발생하는 연산입니다.
임시 데이터베이스(TempDB) 사용 문제
예상 레코드 수와 실제 레코드 수의 차이도 반드시 고려해야 합니다. 잘못된 예측은 쿼리에 필요한 메모리 할당이 부족해지는 결과를 낳고, 이 경우 SQL Server는 작업을 완료하기 위해 임시 데이터베이스(tempdb)를 사용합니다. 아래 이미지와 같이 SQL Server가 잘못된 연산자나 계획을 선택하면 쿼리 처리 및 데이터 추출이 느려질 수 있습니다. 실제 레코드 수는 동일한데 예상치만 다른 경우로, 이는 오래되었거나 누락된 통계 때문일 수 있습니다. 참고로 테이블 변수(Table Variable)에는 통계가 없으므로, 재컴파일 옵션을 사용하기 전까지는 새 버전에서도 계획이 항상 1행 또는 1K행으로 반환됩니다. 따라서 대량의 레코드를 처리할 때는 테이블 변수가 적합하지 않습니다.
정렬(Sort) 연산자
정렬의 영향도 고려해야 합니다. Sort 연산자는 주로 집계(Aggregate), 병합 조인(Merge Join), ORDER BY 절에 사용됩니다. 소량의 레코드에서는 큰 영향이 없지만, 레코드가 추가될수록 처리 속도가 느려집니다. 가능하면 정렬을 피하고 ORDER BY 절 사용을 최소화하세요. 불가피하게 정렬이 필요하다면 정렬된 데이터를 애플리케이션으로 보내기보다 애플리케이션의 그리드(Grid)에서 정렬하는 것이 좋습니다.
아래 이미지는 정렬 비용을 보여주는 예시입니다:
Spool 연산자
주목해야 할 또 다른 핵심 연산자는 Spool입니다. Spool은 숨겨진 임시 객체나 작업 테이블을 tempdb에 저장하기 때문에 느린 연산자입니다. 또한 Rebind나 Rewind를 유발하는 특정 연산자와 함께 사용될 때 성능이 더욱 저하됩니다. SQL Server에는 Eager Spool, Lazy Spool, Table/Index Spool 등 다양한 유형의 Spool이 있습니다. SQL Server는 중간 결과 집합을 위해 원본 테이블로 되돌아가는 것보다 임시 작업 테이블을 참조하는 것이 유리하다고 판단될 때 Spool을 사용합니다. 아래 이미지가 그 예시입니다:
Spool을 분석할 때는 Rebind와 Rewind 횟수를 반드시 확인해야 합니다. Rewind가 Rebind보다 비용이 많이 듭니다. 예를 들어 아래 이미지에서 연산자가 2,674회의 Rewind를 기록했다면, 쿼리가 데이터를 가져오기 위해 2,674번 재실행되었음을 의미합니다. 즉, Rewind가 발생할 때마다 Table Spool에서 Table Scan까지 각 연산자를 거쳐 레코드를 하나씩 반환합니다. 반면 Rebind는 Spool에서 데이터를 가져왔고 Table Scan으로 돌아가지 않았다는 의미입니다.
Hash 및 Nested Loop 연산자
작은 레코드 집합에서 우수한 성능을 보이는 Hash와 Nested Loop도 검토 대상입니다. 하지만 대량의 레코드 집합을 처리하거나 예상 계획과 실제 계획 간 차이가 클 때는 tempdb를 메모리 대신 사용할 수 있어 성능에 막대한 영향을 줄 수 있습니다. 이런 경우 SQL Server는 연산자 세부 정보에 "연산자가 실행 중 tempdb로 데이터를 스풀(spill)했습니다"라는 경고를 표시합니다. 이런 경고가 보이면 통계에 주목하세요. 잘못된 예측으로 인해 루프가 메모리 할당에 실패하거나 계속 반복될 수 있습니다. 실행 계획의 시작 지점(왼쪽에서 오른쪽 방향)을 선택하여 속성 상자를 열면 할당된 메모리를 확인할 수 있습니다. 특별한 이상이 없다면 쿼리 튜닝이 가장 이상적인 선택입니다.
결론
쿼리 최적화의 첫 번째 목표는 전반적인 읽기·쓰기 작업, 즉 디스크 I/O를 줄이는 것입니다. 메모리에서의 읽기·쓰기인 논리적 읽기(Logical Reads)도 잊지 마세요. I/O를 줄이면 대부분의 문제가 해결되며 쿼리가 훨씬 빠르게 실행됩니다.
다음으로 tempdb 활동으로 인해 비용이 많이 드는 연산들을 살펴보세요. tempdb는 다양한 작업에 사용되며 항상 비용이 큽니다. Rewind 횟수, 각 연산의 실행 횟수, Spool, 정렬, 루프를 꼼꼼히 확인하세요. 이들은 tempdb와 함께 사용될 때 비용이 매우 높습니다. 또한 각 연산자에 표시된 경고를 검토하는 것도 잊지 마세요. 경고는 문제 해결의 좋은 단서를 제공합니다. 이 글에서 누락된 인덱스(Missing Index) 연산자를 다루지 않았다고 해서 이를 무시해도 된다는 의미는 아닙니다.
누락된 인덱스 제안을 검토하되, 무작정 인덱스를 생성하지는 마세요. 동일한 컬럼에 이미 존재하는 다른 인덱스를 확인하고, 데이터베이스에서 실행 중인 다른 쿼리에 미칠 영향까지 고려해야 합니다.
궁금한 점이나 의견이 있다면 피드백 탭을 통해 남겨주세요. 언제든지 대화를 나눌 수 있습니다.