소개
사견으로 시작합니다, SQL Server 버전이 올라가면서 Query Optimizing에도 많은 변화가 있습니다 달리 말해서 Query Optimizer 루틴이 더 길어지고 절차가 더 복잡해진다는 것입니다 이는 쿼리 최적화 기능의 향상에 도움이 되겠지만 반대로 최적화의 복잡도를 증가시키면서 쿼리 컴파일 부하나 시간이 커지는 부작용도 동반될 것입니다, 오랜 경험을 통한 사견이므로 가볍게 받아주세요^^
SQL Server로 서비스되는 DB 서버가 대형화되고(CPU 수백 개, Memory 수 TB 이상, Full Flash Storage) 동일 쿼리가 대량으로 호출되는 서비스 환경에서는 특정 쿼리의 컴파일/최적화 시간이 길어지거나 부하가 커지는 경우엔 매우 심각한 문제 상황을 유발할 수도 있습니다, 실제론 그런 사례도 년 중에 몇 번이상 발생하고 있습니다.
SQL Server 2022에 “Query Compilation Replay” 혹은 “Optimized Plan Forcing” 이라는 기능이 도입된다고 소개가 나왔을 때 개인적으로 매우 반가웠습니다, 실무에서 일어나는 해당 이슈를 제거하거나 완하시키는데 도움이 될 수 있을까 하는 기대감이었습니다.
참고. Optimized Plan Forcing 동작이나 기준 등 사용법에 대해서는 MS 문서 “Optimized Plan Forcing with Query Store“를 참조하세요.
Compile 부하 및 차단 문제
저와 같은 SQL Server 컨설턴트나, 실무 DBA 등의 전문가들은 오랜 전부터 이러한 컴파일/최적화 시간이나 부하를 줄일 수 있는 방법에 관심을 가지고 여러가지를 살펴보고 있었고 SQL Server 개발팀과도 틈틈이 의견 교환이 있어왔습니다 그러나 아직까지 명확한 방법을 찾지는 못했습니다, 대신에 실제 해당 상황이 발생했을 때 단발적으로 문제를 회피하기 위한 방법이나 혹은 컴파일을 유발하는 동기를 제거하는 방법 등을 동원했었습니다.
아래 그림은 실제 컨설팅 사례로서, 특정 쿼리(프로시저)의 동시 대량의 컴파일 대기가 발생하고 있는 상황을 모니터링한 내용입니다. COMPILE 대기가 적게는 수초에서 많게는 수 십초 이상 소요되면서 쿼리 실행이 되지 않아서 큰 문제가 발생했습니다 더 큰 문제는 이와 같은 상황에서 시나리오에 따라서 단 시간에 문제를 해결하기가 어려울 수 있다는 것입니다.
[그림. 사례-쿼리 컴파일 대기로 인한 대량 차단 유발]
Optimized Plan Forcing (일명 Query Compilation Replay)
Optimized Plan Forcing 기능의 핵심은 “Optimization Replay Script”에 있어 보입니다, 위 MS 문서에 설명에 따르면 쿼리 컴파일/최적화 단계에서 최적화 소요되는 예상 시간이 특정 임계값을 초과하면 최적화 재생 스크립트를 생성 및 쿼리 저장소(Query Store)에 보관하고 이후 실제 최적화 시간과 재생 스크립트를 이용하는 시간과의 비교를 통해 재생 스크립트 사용이 훨씬 더 이득이라면 계속 사용하겠다는 전략입니다.
따라서 앞서 살펴봤던 Feedback 기능들처럼 이 또한 쿼리 저장소가 읽기/쓰기 가능한 상태여야 합니다. 더불어 필요 시 DB 단위로 이 기능을 활성화/비활성화하거나 쿼리 단위로 처리를 할 수 있는 힌트들이 제공되니 자세한 건 위 MS 문서를 참조하세요.
정리
실제로 실무에서 위 기능이 얼마나 혹은 의도한대로 의미있는 도움이 될지는 지켜봐야겠습니다 또한 쿼리 저장소에 재생 스크립트를 저장하는 추가 부하도 고려해야겠죠. 우선은 SQL Server 2022를 사용하는 시스템에서 메타 데이터 검색을 통해서 이 기능이 얼마나 적용되고 있는지를 확인해 보는 것도 의미 있는 시작점이 될 수 있겠습니다. 아무튼 기대가 됩니다^^
모두 건강하세요.
김정선 드림
