[SQL Server] 고비용 쿼리 튜닝

 

 

 

기존 서비스에는 APM으로 Scouter를 붙여서 사용하고 있다. 

XLog 차트와 Summary를 사용해 쿼리 수행 시간이 높은 순서대로 정렬하고 쿼리 튜닝 대상 쿼리를 선정했다.

 

Scouter의 트랜잭션 프로파일링을 사용하면 지연이 발생한 시점에 실행된 쿼리와 파라미터를 찾을 수 있음.

 

이제 SSMS의 Query Store에서 해당 쿼리의 실행 이력을 조회하고, 해당 쿼리가 CPU 자원을 얼마나 쓰는지 확인함.

(https://learn.microsoft.com/ko-kr/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16 참고...)

 

MySQL 쓸 때는 EXPLAIN ANALYZE로 실행계획 보고 실행 중 어떤 단계에서 시간을 오래 잡아먹는지 쉽게 볼 수 있는데..

SQL Server에는 그런 기능이 없음. 대신 다른 도구가 있다. 

 

 

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

 

 

 

STATISTICS IO는 쿼리가 실행되는 동안 발생한 Logical Read와 Physical Read 횟수를 테이블마다 상세하게 보여줌.

개인정보가 암호화되어 저장되는 환경이라.. 저걸 켜는게 좀 많이 중요함. (D'Amo 사용)

 

실행 계획에는 인덱스를 탔다고 뜨는데, 암호화된 컬럼의 복호화를 위해 불필요한 페이지를 메모리로 가져오는 작업이 Logical Read 카운트를 늘리는 경우가 많았다.

 

STATISTICS TIME 으로 CPU가 처리하는데 걸린 시간과 전체 경과 시간을 확인할 수 있는데, 복호화 함수 호출로 Context Switching이 발생해 경과 시간과 CPU 시간의 차이가 컸다.

 

 

 

고비용 쿼리들을 봤을 때 공통으로 나타난 것.

 

1. 서브쿼리

SELECT / WHERE 에 포함된 서브쿼리가 반복 수행되면서 비용을 뻥튀기시킴 

 

2. 복잡한 NULL 처리

 CASE WHEN 구문과 ISNULL 처리가 인덱스 활용을 방해하고 연산 오버헤드를 높임 

 

3. 비효율적인 실행 계획

통계 정보가 안 맞아서 최적의 인덱스를 타지 않고 풀스캔 때려버리는 일 발생 -_-

 

 

 

서브쿼리는 LEFT OUTER JOIN / OUTER APPLY 구문을 사용하는 형태로 변경함.

 

 

-- 이전
SELECT
    c.CustomerID,
    c.CustomerName,
    (SELECT TOP 1 OrderDate FROM Orders o WHERE o.CustomerID = c.CustomerID ORDER BY OrderDate DESC) as LastOrderDate
FROM Customer c
WHERE c.Region = 'Seoul'

-- 이후
SELECT
    c.CustomerID,
    c.CustomerName,
    o.LastOrderDate
FROM Customer c
OUTER APPLY (
    SELECT TOP 1 OrderDate as LastOrderDate
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o
WHERE c.Region = 'Seoul'

 

 

 

OUTER APPLY는 서브쿼리에서 여러 컬럼을 한 번에 반환해 메인 쿼리에서 사용한다.

또, 데이터베이스 엔진이 JOIN 연산으로 인식하도록 유도함.

 

 

복잡한 NULL 체크 로직은 COALESCE 구문으로 변경.

 

 

 

 

 

COALESCE는 내부적으로 CASE WHEN 표현식으로 변환된다.

그래서 성능 민감도가 높은 영역에서는 ISNULL을 우선 사용해야되는거 아닌가 싶은데.. 

 

ISNULL을 사용할 때와 COALESCE를 사용할 때 서로 다른 인덱스를 타는 경우가 있었음;

실행시간이 막 크게 차이나지는 않긴 했는데.. 

 

여튼... 기존에 다중 CASE + ISNULL 으로 체크하는 구문을 COALESCE로 리팩토링했다.

 

 

임시 쿼리 결과나 복잡한 집합을 CTE로 정의해 가독성을 높이고 성능을 끌어올림.

 

CTE는 잘 써야함.. 쿼리에서 CTE가 두 번 참조되면 CTE 정의 쿼리도 두 번 실행됨.

암호화된 테이블을 조회하는 CTE가 있고, 메인 쿼리에서 UNION 구문으로 CTE를 여러번 참조하면 복호화 연산도 중복된다.

 

반복 참조가 필요한 무거운 CTE 로직을 임시테이블로 대체해서 사용했다.

필요한 데이터만 복호화하고 #TempTable에 저장한다.

 

임시테이블에 인덱스를 잡아주면 통계 정보가 제대로 박히고 옵티마이저가 제대로 일할 수 있다.

 

이 부분이 좀 많이 치명적이였음.. 20초쯤 걸리던게 1.2초로 줄었다.

 

 

공통적으로 나타나는 요소는 저 3개인데.. 암호화된 테이블을 건드리는 쿼리를 조작할 때는 다른 방식으로 접근해야 한다.

 

 

인덱스는 암호화된 값을 기준으로 정렬되어 있음.

암호문의 순서와 평문의 순서는 전혀~ 상관관계가 없으니 인덱스를 통해 암호문이 어디에 있는지는 절대 알 수 없다.

이러면 데이터베이스 엔진이 인덱스 타는걸 포기하고 풀스캔하면서 모든 행을 복호화해서 비교한다.

 

그러니.. Partial Encryption 전략을 함께 사용했음.

식별 가능한 일부 패턴을 평문으로 남기고, 해시 컬럼을 인덱싱 전용으로 사용하는 방식이다.

 

 

 

---

 

 

 

추가로.......

SQL SERVER 에서 Remote Procedure Call으로 다른 DB에 쿼리를 날려서 데이터를 현재 DB로 쿼리하는 구조를 사용 중임.

즉, Linked Server로 SQL SERVER 인스턴스에서 다른 데이터소스로 접속하는 통로를 뚫어놓고 RPC로 쿼리를 보내고 있다.

 

그래서 쿼리를 아무리 깎아도 Remote Procedure Call의 오버헤드 때문에 성능이 안 나올수밖에 없는 -_- 

네트워크 오버헤드와 쿼리 실행 오버헤드가 중첩되니까..

 

[RemoteServer].[RemoteDB].[dbo].[TargetTable] 이렇게 사용하면 데이터를 전부 다 가져온 후 걸러내는 식으로 동작하기도 하던데.. 그래서 이렇게 쓰는건 웬만하면 지양하는게 좋음.

 

EXEC이나 OPENQUERY 구문을 사용해서 쿼리 자체가 원격 서버에서 실행되도록 강제해서 성능이 훨씬 좋다.

 

그런데 첫 번째 방식으로 해야 현재 테이블과 Linked Server로 연결된 데이터베이스의 테이블을 JOIN 걸 수 있어서 저 방식을 어쩔 수 없이 사용하는 경우가 있었음; 

 

진짜 필요할 때만 첫 번째 방식을 사용하고, 웬만하면 OPENQUERY이나 EXEC을 사용하자.