ABORT_QUERY_EXECUTION 쿼리 힌트
DB 서버의 성능이나 안전성에 큰 영향을 미치는 쿼리가 실행되는 경우 관계자들의 고통스러운 시간이 시작됩니다. 일차적으로 상황을 인식하는 단계를 시작으로 진단, 분석, 조치해서 다시 안정화될 때까지 때론 단순하지만 많은 경우 매우 복잡하고 긴 시간을 거치기도 합니다. 개인적으로도 간헐적으로 고객사에서 특정 고부하 쿼리로 인해 긴급한 연락을 받은 경우가 있고 그 때마다 담당자와 함께 어렵고 힘든 시간을 보냅니다.
이를 위한 사전 예방도 기본이지만 빠른 사후 조치도 반드시 필요한 방어 도구입니다 이를 위해 여러가지 방법들을 고려할 수 있는데 지금까지는 충분치 못했다고 봐야겠죠. SQL Server 2025에는 쿼리 저장소(Query Store)와 ABORT_QUERY_EXECUTION 쿼리 힌트를 연동해서 특정 쿼리가 후속 실행되는 경우 이를 중지하도록 강제할 수 있는 기능이 도입되었습니다. DB 관리자(운영자) 입장에서 활용할 수 있는 또 하나의 도구가 생긴 셈이네요.
어떻게 동작하는지 어떻게 처리되는지 테스트 결과와 함께 살펴보겠습니다.
원본) 아래 예제는 Microsoft의 발표 자료를 참고했습니다. 테스트를 직접한다면 개인 장비나 테스트 장비 이용하셔야 합니다.
1. 쿼리 저장소와 ABORT_QUERY_EXECTUION 쿼리 힌트
이 기능은 DB 단위 쿼리 저장소 기능을 사용하고, 쿼리 저장소는 이제 기본적으로 ON 상태이므로 별도로 OFF하지 않은 이상 적용할 수 있습니다.
USE master;
GO
— 필요 시 ON
ALTER DATABASE [AdventureWorks]
SET QUERY_STORE = ON;
— 이후 목록 확인 편리를 위해 기존 데이터 삭제해
ALTER DATABASE [AdventureWorks]
SET QUERY_STORE CLEAR;
따라서 쿼리 저장소에서 먼저 후속 중지하고 싶은 쿼리의 query_id를 확인하고 이를 이용해서 쿼리 힌트 추가하되 이 때 힌트를 ABORT_QUERY_EXECTION으로 사용하는 겁니다. 아래와 같은 절차로 간단히 재현할 수 있습니다.
절차-1. 고부하 쿼리 샘플입니다. 아래 또는 유사한 고부하 쿼리를 실행합니다. (약 113만건 출력)
USE [AdventureWorks];
GO
WITH LargeDataSet AS (
SELECT
p.ProductID, p.Name, p.ProductNumber, p.Color,
s.SalesOrderID, s.OrderQty, s.UnitPrice, s.LineTotal,
c.CustomerID, c.AccountNumber,
(SELECT AVG(UnitPrice) FROM Sales.SalesOrderDetail WHERE ProductID = p.ProductID) AS AvgUnitPrice,
(SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE ProductID = p.ProductID) AS OrderCount,
(SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail WHERE ProductID = p.ProductID) AS TotalSales,
(SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = c.CustomerID) AS LastOrderDate,
r.ReviewCount
FROM
Production.Product p
JOIN
Sales.SalesOrderDetail s ON p.ProductID = s.ProductID
JOIN
Sales.SalesOrderHeader h ON s.SalesOrderID = h.SalesOrderID
JOIN
Sales.Customer c ON h.CustomerID = c.CustomerID
JOIN
(SELECT
ProductID, COUNT(*) AS ReviewCount
FROM
Production.ProductReview
GROUP BY
ProductID) r ON p.ProductID = r.ProductID
CROSS JOIN
(SELECT TOP 1000 * FROM Sales.SalesOrderDetail) s2
)
SELECT
ld.ProductID, ld.Name, ld.ProductNumber, ld.Color,
ld.SalesOrderID, ld.OrderQty, ld.UnitPrice, ld.LineTotal,
ld.CustomerID, ld.AccountNumber, ld.AvgUnitPrice, ld.OrderCount, ld.TotalSales, ld.LastOrderDate, ld.ReviewCount
FROM
LargeDataSet ld
ORDER BY
ld.OrderQty DESC, ld.ReviewCount ASC;
절차-2. SSMS 개체 탐색기의 “쿼리 저장소” GUI 인터페이스를 이용하거나 아래와 같은 Query Store용 분석 스크립를 이용해서 원하는 쿼리의 query_id 값을 추출합니다.
USE AdventureWorks;
GO
SELECT
qsqt.query_sql_text,
qsp.plan_id,
qsp.query_id,
rs.avg_duration,
rs.count_executions
FROM
sys.query_store_query_text AS qsqt
JOIN
sys.query_store_query AS qsq
ON qsqt.query_text_id = qsq.query_text_id
JOIN
sys.query_store_plan AS qsp
ON qsq.query_id = qsp.query_id
JOIN
sys.query_store_runtime_stats AS rs
ON qsp.plan_id = rs.plan_id
GROUP BY qsqt.query_sql_text, qsp.plan_id, qsp.query_id, rs.avg_duration, rs.count_executions
ORDER BY
rs.avg_duration DESC;
절차-3. 이제 query_id로 식별되는 특정 쿼리에 원하는 쿼리 힌트를 부착시킵니다. SQL Server 2025의 새로 도입된 ABORT_QUERY_EXECUTION 힌트입니다.
USE AdventureWorks;
GO
EXEC sys.sp_query_store_set_hints
@query_id = 1, — id 확인 후 변경
@query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';
GO
— 힌트 적용 결과 확인
SELECT * FROM sys.query_store_query_hints;
결과. 정상적으로 동작할 경우 해당 쿼리를 재 실행하면 아래와 같이 8778 오류가 발생하며 쿼리 실행이 중지됩니다.
메시지 8778, 수준 16, 상태 1, 줄 61
ABORT_QUERY_EXECUTION 힌트가 지정되어 쿼리 실행이 중단되었습니다.
Hint 제거. 해당 쿼리가 더 이상 문제를 일으키지 않는다면 힌트를 제거할 수 있습니다.
USE AdventureWorks;
GO
EXEC sys.sp_query_store_clear_hints @query_id = 1; — id 확인 후 변경
마치면서
실제 상황에서는 수동으로, 업무적으로, 혹은 DB 앞단에서 대상 쿼리 호출을 일시적으로 차단할 수 도 있겠지만 긴급한 상황이나 다른 선택지가 불가한 경우에 DB 관리자의 실용적이고 효과적인 도구가 될 수 있을 것으로 예상됩니다. SQL Server의 새로운 도전과 진보는 계속됩니다.
감사합니다.
