쿼리 힌트 이해

  • 4분

쿼리 힌트는 쿼리 프로세서가 SELECT, INSERT, UPDATE 및 DELETE 문에 대한 실행 계획에서 특정 연산자를 사용하도록 강제하는 옵션 또는 전략입니다. 쿼리 힌트는 쿼리 프로세서가 특정 쿼리에 대해 OPTION 절을 사용하여 선택할 수 있는 실행 계획을 무시하고 재정의합니다.

대부분의 경우 쿼리 최적화 프로그램은 인덱스, 통계 및 데이터 분포에 따라 효율적인 실행 계획을 선택합니다. 데이터베이스 관리자는 수동으로 개입할 필요가 거의 없습니다.

쿼리의 끝에 쿼리 힌트를 추가하여 쿼리의 실행 계획을 변경할 수 있습니다. 예를 들어 단일 CPU를 사용하는 쿼리의 끝에 추가하는 OPTION (MAXDOP <integer_value>) 경우 쿼리는 선택한 값에 따라 여러 CPU(병렬 처리)를 사용할 수 있습니다. 또는 쿼리가 실행될 때마다 새로운 임시 계획을 생성하는 데 사용할 OPTION (RECOMPILE) 수 있습니다.

--With maxdop hint
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2)
GO

--With recompile hint
SELECT City
FROM Person.Address
WHERE StateProvinceID=15 OPTION (RECOMPILE)
GO

쿼리 힌트는 다양한 성능 관련 문제에 대한 지역화된 솔루션을 제공할 수 있지만 다음과 같은 이유로 프로덕션 환경에서 사용하지 않아야 합니다.

  • 쿼리에 대한 영구 쿼리 힌트가 있으면 구조적 데이터베이스 변경이 발생할 수 있으며, 이는 해당 쿼리를 적용할 수 없는 데 도움이 될 수 있습니다.
  • 특정 실행 계획에 쿼리를 바인딩하는 경우 후속 버전의 SQL Server에서 새롭고 향상된 기능을 활용할 수 없습니다.

그러나 SQL Server에는 다양한 용도로 사용되는 몇 가지 쿼리 힌트가 있습니다. 다음 중 몇 가지를 살펴보겠습니다.

  • FAST <integer_value>- 쿼리 실행을 계속하는 동안 첫 번째 <integer_value> 행 수를 검색합니다. 빠른 쿼리 힌트를 위해 작은 데이터 집합과 낮은 값으로 더 잘 작동합니다. 행 수가 늘어나면 쿼리 비용이 높아집니다.

  • OPTIMIZE FOR- 쿼리 최적화 프로그램에서 쿼리를 컴파일하고 최적화할 때 지역 변수의 특정 값을 사용해야 한다는 지침을 제공합니다.

  • USE PLAN- 쿼리 최적화 프로그램은 xml_plan 특성에 지정된 쿼리 계획을 사용합니다.

  • RECOMPILE- 쿼리에 대한 새 임시 계획을 만들고 쿼리가 실행된 직후 삭제합니다.

  • { LOOP | MERGE | HASH } JOIN— 전체 쿼리에서 모든 조인 작업이 LOOP JOIN, MERGE JOIN, 또는 HASH JOIN에 의해 수행됨을 지정합니다. 둘 이상의 조인 힌트를 지정하는 경우 최적화 프로그램은 옵션 중에서 가장 저렴한 조인 전략을 선택합니다.

  • MAXDOP <integer_value>의 최대 병렬 처리 수준 값을 sp_configure로 재정의합니다. 또한 이 옵션을 지정하는 쿼리는 Resource Governor를 재정의합니다.

동일한 쿼리에서 여러 쿼리 힌트를 적용할 수도 있습니다. 다음 예제에서는 동일한 쿼리에서 HASH GROUP 쿼리 힌트 및 FAST <integer_value> 쿼리를 사용합니다.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO

쿼리 힌트에 대한 자세한 내용은 힌트(Transact-SQL)를 참조하세요.

쿼리 저장소 힌트

쿼리 저장소 힌트는 애플리케이션 코드를 수정하지 않고 쿼리 계획을 셰이핑하는 간단한 방법을 제공합니다.

쿼리 저장소 힌트는 쿼리 최적화 프로그램이 효율적인 실행 계획을 생성하지 않고 개발자 또는 DBA가 원래 쿼리 텍스트를 수정할 수 없는 경우에 유용합니다. 일부 애플리케이션에서는 쿼리 텍스트가 하드 코딩되거나 자동으로 생성될 수 있습니다.

쿼리 저장소 힌트의 작동 방식 스크린샷

쿼리 저장소 힌트를 사용하려면 쿼리 저장소 카탈로그 뷰, 기본 제공 쿼리 저장소 보고서 또는 Azure SQL Database용 Query Performance Insight를 통해 수정하려는 쿼리 문의 query_id 쿼리 저장소를 식별해야 합니다. 그런 다음, 쿼리에 적용하려는 sp_query_store_set_hints 및 쿼리 힌트 문자열을 사용하여 실행 합니다.

다음 예제에서는 특정 쿼리를 query_id 가져온 다음 이를 사용하여 쿼리에 RECOMPILE 힌트와 MAXDOP 힌트를 적용하는 방법을 보여 줍니다.

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
    INNER JOIN sys.query_store_query q 
        ON qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY CustomerName DESC%'  
  AND query_sql_text not like N'%query_store%'
GO

--Assuming the query_id returned by the previous query is 42
EXEC sys.sp_query_store_set_hints @query_id= 42, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1)'
GO

쿼리 저장소 힌트가 쿼리 수준 성능 문제에 도움이 될 수 있는 몇 가지 시나리오가 있습니다.

  • 실행할 때마다 쿼리를 다시 컴파일합니다.
  • 통계 업데이트 작업의 최대 병렬 처리 수준을 제한합니다.
  • 중첩 루프 조인 대신 해시 조인을 사용합니다.
  • 데이터베이스를 현재 호환성으로 유지하면서 특정 쿼리에 호환성 수준 110을 사용합니다.

쿼리 저장소 힌트에 대한 자세한 내용은 쿼리 저장소 힌트를 참조하세요.