요즘 바쁜 작업이 끝나고 여유로워져서 이전부터 유독 조회가 느려 성능을 개선하고 싶었던 뷰의 실행 계획을 분석해보고 있다. 문제는 DB에 대한 지식이 적고, 해본 적이 없는 분야라 처음부터 배워가면서 분석을 해야 한다는 것인데, 당분간은 구글링과 지피티에 신세를 많이 져야겠다.
쿼리 실행 계획
DB에 쿼리를 전달하면 DB는 전달 받은 쿼리를 어떤 방식으로 효율적으로 접근하고 처리할지에 대해 계획을 짜고 작업을 수행하게 된다. 하지만, DB가 혼자서 자동으로 처리하는 작업이라도 개발자가 적절한 인덱싱과 통계 작업 등을 통해 DB에게 방향을 제시해 줄 수 있다.
SSMS에서 실행 계획 보기
SSMS에서는 크게 예상 실행 계획, 실제 실행 계획, 활성 쿼리 통계 세 가지 방식으로 실행 계획을 볼 수 있다.
예상 실행 계획
SET SHOWPLAN_ALL ON;
GO
쿼리문
GO
SET SHOWPLAN_ALL OFF;
실제로 쿼리를 실행하지 않고 SQL Server가 내부적으로 생성한 실행 계획을 보여준다. 위의 쿼리를
쿼리를 실제로 실행하지 않기 때문에 빠르게 성능 병목 현상과 데이터 접근 방식, 연산 비용 등을 분석할 수 있지만, 실제 실행 계획에 비해 정확성이 떨어진다.
실제 실행 계획
SET STATISTICS PROFILE ON;
GO
쿼리문
GO
SET STATISTICS PROFILE OFF;
실제로 쿼리를 실행한 뒤에 과정을 기록한 실행 계획으로, 실제 데이터와 통계를 사용한 결과를 보여줘 예상 실행 계획에 비해 느리지만 정확한 계획을 분석할 수 있다.
활성 쿼리 통계
실제 실행 계획과 마찬가지로 실제로 쿼리를 실행하는 것은 같지만, 쿼리가 실행 중인 중간에 실시간으로 실행 계획을 분석할 수 있어 병목 구간을 실시간으로 파악하거나, 처리 시간이 긴 쿼리를 디버깅할 때 유용하다.
어떤 부분을 확인해야 할까?
처음 실행 계획을 봤을 때 뭔지도 모르는 엄청 많은 정보들이 쏟아져서 당황했었는데, 아는 것이 많지 않으니 우선은 크게 다섯 가지만 살펴보기로 했다.
1. 테이블 액세스 방식
데이터를 읽을 때 어떤 방식으로 읽는지에 대한 부분인데, 인덱스를 잘 타주고 있는지를 확인하면 된다. 대부분이 인덱스와 조건 문제이기 때문에 확인 후에 제대로 처리해줘야 한다.
| Table Scan | 인덱스를 타지 못해 테이블의 모든 행을 순차적으로 읽는 방식이다. 적절한 인덱스가 없거나, 조건이 인덱스를 사용할 수 없을 때 사용된다. |
| Index Scan | 인덱스를 타지만 조건이 부족하거나, 결과 데이터가 많은 경우 인덱스의 모든 데이터를 순차적으로 읽는 방식 |
| Index Seek | 인덱스가 효율적으로 활용되어 특정 조건에 따라 필요한 인덱스를 바로 찾아가는 방식 |
2. 조인 방식
테이블 간의 조인을 어떤 방식으로 처리하는지에 대한 부분으로 데이터 크기와 구조에 따라 달라진다.
| Nested Loop Join | 테이블의 각 행에 대해 다른 테이블의 데이터를 검색하는 방식이기 때문에 작은 데이터 세트에 적합하고, 큰 데이터 세트의 경우에는 비효율적이다. |
| Hash Match | 해시 테이블을 만들어 데이터를 결합하는 방식으로 큰 데이터 세트에 적합하지만 성능 병목이 발생할 수 있으며, 생성을 위한 추가 메모리가 필요하다. 조인 대상 테이블의 크기가 크거나, 테이블이 인덱스로 정렬되어 있지 않은 경우 사용된다. |
| Merge Join | 정렬된 데이터를 병합하여 결합하는 방식으로, 두 테이블이 정렬되어 있거나, 정렬 비용이 낮을 때 적합하다. |
3. Sort
정렬, 그룹화, 중복 제거 등의 작업을 처리하기 위해 데이터를 정렬하는 과정으로 추가적인 CPU와 메모리를 소모하기 때문에 비용이 많이 발생한다. 가능하면 정렬된 인덱스를 사용하는 것이 좋다.
4. Filter
WHERE, HAVING 절의 조건을 만족하지 않는 데이터를 제거하는 과정으로, 조건이 복잡할 수록 연산 비용이 높으며, 정렬과 마찬가지로 적절한 인덱스를 사용해서 성능을 향상할 수 있다.
5. Cost
각 단계에서 사용하는 CPU, 메모리, I/O 리소스 등을 기반으로 상대적인 비용을 계산한 값으로, 가장 비용이 높은 단계를 찾아 성능을 개선해야 한다.
주의해야 할 부분과 해결책
대표적으로 다음과 같이 연산 비용이 높게 책정되고, 성능에 영향을 끼치는 작업들이 존재한다.
Table Scan
인덱스를 타지 않고 테이블의 모든 행을 검색하기 때문에 당연히 데이터가 많은 수록 성능 저하가 발생할 수 밖에 없다. 따라서 쿼리에 필요한 컬럼에 인덱스를 추가하고, 필요하지 않은 데이터를 제거하거나 조회 범위를 줄이는 것이 좋다.
Index Scan
Table Scan 보다야 좋은 방식이지만 인덱스를 제대로 사용하고 있지 못한 방식이기 때문에, 조회 범위를 줄이거나, 조건에 자주 사용되는 필터링 인덱스, 커버링 인덱스를 생성하는 방식으로 쿼리를 최적화 해줘야 한다.
Hash Join
큰 데이터 세트일수록 해시 테이블 생성에는 많은 메모리가 필요하기 때문에 조인 대상 테이블의 크기를 줄이는 방향으로 쿼리를 수정하고, 조인 컬럼에 적절한 인덱스를 추가해준다.
Sort
정렬 작업은 CPU와 메모리 리소스를 많이 잡아 먹기 때문에 불필요한 정렬을 제거하거나 간소화하는 것이 좋고, 정렬된 인덱스를 사용하는 것이 좋다.
Nested Loop Join
모든 행에 대해 반복적으로 다른 테이블을 검색하는 무식한 방법이기 때문에 큰 데이터 세트일수록 성능 저하가 심하다. 조인 시 적합한 인덱스가 없거나 사용하지 못하고 있는 경우와 큰 테이블 간의 조인시에 사용되는 방식이니 조인 컬럼에 적합한 인덱스를 추가해주고, 큰 데이터 세트의 경우에는 Hash나 Merge 방식을 사용할 수 있도록 유도해주는 것이 좋다는데 이건 어떻게 해야할지 아직 모르겠다...
Key Lookup
인덱스가 쿼리에서 필요한 데이터를 모두 포함하고 있지 않은 경우에 인덱스에서 필요한 데이터를 조회한 후에 부족한 데이터를 추가로 조회하는 작업으로, 커버링 인덱스를 추가하거나, SELECT 절에서 불필요한 컬럼을 제거한다.
Filter
조건절이 인덱스를 효율적으로 활용할 수 있게 조건을 간소화 혹은 구체화하고, 필터링 조건에 적합한 인덱스를 추가한다.
마치며
실행 계획에서 사용되는 방식들에 대해 학습한 후에 문제가 있던 쿼리의 실행 계획을 봤을 때 Hash Match와 Nested Loop, 과도한 정렬 작업 등이 넘쳐나는 것을 확인했는데, 다음 주에 출근해서 이걸 처리해야 하는게 두렵기도 하지만 해결하면 얼마나 빨라질지 설레기도 한다. 할 수 있을까...