소개
Query Optimizer가 선택한 실행 계획이나 동작을 다르게 처리하고 싶은데 쿼리 자체를 수정할 수 없는 여건을 위해 기존의 Plan Guide와 같은 방법들이 지원되었으나 실제로 실무에서 사용하기는 용이하지 않았습니다. SQL Server 2022에 새로운 도입된 Query Store Hint는 이러한 요구사항을 지원하는데 도움이 될 수 있습니다.
쿼리 실행 계획 강제 or 변경하기
쿼리 성능을 튜닝할 때 Query Optimizer가 선택한 실행 계획이나 동작 방식을 변경해서 다르게 처리되도록 강제하고 싶을 때가 있죠. 대표적으로 테이블(잠금) 힌트, 조인 힌트, 쿼리 힌트와 같은 방법들을 이용합니다. 문제는 해당 방법들이 기존 쿼리 텍스트의 수정을 필요로 한다는 것이죠. SQL Server에서는 일반적으로 쿼리를 저장 프로시저 형태로 개발하므로 쿼리 텍스트 수정이 필요할 때 DB 영역에서 큰 어려움없이 작업이 가능하지만 Application에서 쿼리가 하드코딩(임의 쿼리 or 매개변수 쿼리 형태)되어 있거나 혹은 .NET Entity Framework(or EF Core)와 같은 ORM(Object Relational Mapper)에 의해서 자동 생성 후 실행되는 쿼리의 경우엔 쿼리 텍스트의 힌트 적용이 어렵습니다.
그래서 도입된 기능이 Plan Guide(계획지침)였습니다. 여러가지 형태의 기능이 지원됩니다만 동일 쿼리로 매칭할 템플릿을 만들고 적용할 힌트나 혹은 XML Plan을 지정해서 원하는 실행 계획이나 동작을 유도하는 것입니다. 자세한 방법은 링크를 참조하세요. 처음 이 기능이 나왔을 때 오~ 하면서 감탄을 하기도 했고 실제 실무에서 해당 상황이 발생했을 때 그 기능을 활용하기 위해 여러가지 시도도 했습니다만 쉽지가 않았습니다. 필요한 정확한 상황이나 방식을 만족해야만 가능하기 때문이었습니다. 그래서 개인적으로는 잊혀져가는 기능이기도 했습니다.
새로운 방법, Query Store Hint(쿼리 저장소 힌트)
이러한 불편함을 해소하는데 도움이 되는 또 한가지 선택지가 SQL Server 2022에 추가되었습니다. 바로 Query Store(쿼리 저장소)를 이용한 Hint 설정 기능입니다. 물론 여기에는 Query Store 기능을 해당 DB에서 사용 중이어야 한다는 조건이 붙습니다. Azure SQL Database와 SQL Server 2022에서는 Query Store가 디폴트로 ON 상태이므로 이 기능 사용 조건을 충족할 수 있습니다.
Query Store를 사용하면 쿼리 텍스트와 실행 계획이 영구적으로 저장될 수 있고, 이를 이용해서 원하는 특정 쿼리에 원하는 힌트를 쉽게 적용할 수 있습니다. 간단한 사용 절차는 아래와 같습니다.
참고. 자세한 내용은 MS 문서 “쿼리 저장소 힌트“를 보세요.
— 원하는 쿼리 텍스트와 query_id 확인
SELECT qt.query_sql_text, q.query_id
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE '%dbo.Orders%';
— query hint 확인
SELECT * FROM sys.query_store_query_hints;
— 해당 쿼리에 특정 힌트 설정
EXEC sp_query_store_set_hints 1, N'OPTION(MAXDOP 10)';
— 힌트 제거
EXEC sp_query_store_clear_hints 1;
쿼리에 이미 힌트가 적용된 경우라면?
개인적으로 이 기능을 테스트하면서 이 부분이 궁금했습니다, 기존 쿼리에 이미 힌트가 적용되어 있다면 Query Store Hint는 어떻게 될까? 테이블 힌트, 조인 힌트, 쿼리 힌트를 모두 가지고 있는 경우를 가정하고 테스트한 결과 기존 쿼리 힌트만 대체가 되고(기존은 무시) 대신 테이블 힌트(예, NOLOCK)나 조인 힌트 등은 그대로 동작하는 것을 확인했습니다.
정리
Azure SQL 환경을 제외하고 기존 온프레미스 SQL Server에서는 Query Store 기능을 사용하는 기업이 매우 드물고 업그레이드를 하는 경우에도 Query Store를 ON해서 사용하는 경우도 드뭅니다. 따라서 이 기능 또한 당분간은 실무에서 사용하는 것을 보기는 어려울 것이라 예상됩니다만, 유용한 기능이고 편리하게 활용할 수 있다는 장점을 고려했을 때 앞으로 Query Store 기능 사용이 보다 많아진다면 더불어 많이 사용하게 될 것으로 추측됩니다.
이것으로 SQL Server 2022의 Intelligent Query Processing 관련 기능 소개를 마칩니다, 즐거운 SQL 되세요~
모두 건강하세요.
김정선 드림
