쿼리 힌트란?
SQL HINT 란 실행하려 하는 SQL 문에 사전에 정보를 주어서 SQL문 실행에 빠른 결과를 가져오라는 것이다.
SQL문 HINT를 사용하면 OPTIMAZER는 우선 SQL문의 HINT를 이용하고 그 다음에 OPTIMAZER_MODE에 설정된 값을 이용한다.
쿼리힌트는 쿼리 제일 마지막의 OPTION 절로 조인, 집계,UNION 및 기타 방법을 쿼리 전체에 대해 적용한다.
하나 이상의 쿼리 힌트로 인해 쿼리 최적화 프로그램에서 유효한 계획을 생성할 수 없는 경우 8622 오류가 발생한다.
조인형식 : {LOOP | MERGE | HASH } JOIN
전체 쿼리에서 모든 조인 연산이 LOOP JOIN, MERGE JOIN 또는 HASH JOIN에 의해 수행되도록 지정합니다.
쿼리 힌트에서의 조인 형식은 두 가지 이상을 지정할 수도 있다. 'OPTION (LOOP JOIN , MERGE JOIN)' 이라고 지정할 수는 있다.
이 경우 실제로 적용되는 조인 형식은 최적화 프로그램에서는 허용되는 힌트 중 가장 비용이 적은 것으로, 테이블의 크기나 인덱스, 통계등에 따라 좌우된다.
만약 조인 힌트와 쿼리 힌트에서 둘 다 조인 형식을 지정한다면 조인 힌트의 것이 우선권을 가진다.
USE AdventureWorks;
GO
SELECT *
FROM
INNER JOIN
ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO
집계형식 : {HASH | ORDER } GROUP
쿼리의 GROUP BY, DISTINCT 또는 COMPUTE 절에 지정된 집계에서 해시나 정렬을 사용하도록 지정합니다.
USE AdventureWorks ;
GO
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) ;
GO
UNION : {CONACT | HASH | MERGE | UNION
UNION 집합을 병합, 해시 또는 연결하여 모든 UNION 연산을 수행하도록 지정합니다.
둘 이상의 UNION 힌트를 지정한 경우 쿼리 최적화 프로그램에서는 지정한 힌트 중 가장 부담이 적은 전략을 선택합니다.
참고:
FROM 절에서 조인된 특정 테이블 쌍에 대해 <joint_hint>를 지정한 경우 OPTION 절에 지정한 <join_hint>보다 우선적으로 적용됩니다.
USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE
GO
조인순서유지 : FORCE ORDER
쿼리 구문에 지정된 조인 순서가 쿼리 최적화 시 유지되도록 지정합니다.
지정한 처음 행들을 빨리 검색 : FAST number_rows
첫 번째 음수가 아닌 정수 number_rows를 빨리 검색하기 위해 쿼리를 최적화하도록 지정합니다.
첫 번째 number_rows를 반환한 후에 쿼리는 계속 실행하여 전체 결과 집합을 만듭니다.
USE AdventureWorks ;
GO
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
병렬 처리 제한 : MAXDOP number
병렬처리 제한의 경우, 여러개의 ,CPU로 병렬 처리를 할 경우 number 값을 기본값인 0으로 설정하면 가지고 있는 모든 CPU를 이용하여 병렬처리 할 수 있다는 것이며, 2라고 설정하면 CPU 2개만을 이용하여 병렬처리를 하도록 한다. 8이상으로는 지정하지 말 것을 권장하며 1이면 병렬처리 하지 않는 대신 시리얼 처리를 한다. 병렬 처리 제한은 엔터프라이즈 관리자의 서버 속성| 프로세스 | 병렬처리 | '쿼리 병렬 실행의 경우 최소 쿼리 계획 임계값(예상비용)'으로 설정 할 수도 있다.
이 옵션을 지정한 쿼리에 대해서만 sp_configure의 max degree of parallelism 구성 옵션을 무시합니다.
sp_configure로 구성한 값을 초과하면 MAXDOP 쿼리 힌트는 효과가 없습니다.
MAXDOP 쿼리 힌트를 사용할 때 max degree of parallelism 구성 옵션에 사용된 모든 의미 체계 규칙은 적용할 수 있습니다.
병렬처리 옵션은 자주 버그가 발생한다.
평소 5초면 수행되던 쿼리가 30초 이상이 걸린다면 해당하는 쿼리 플랜을 살펴보고 병렬 처리 플랜이 나온다면 OPTION (MAXDOP 1)을 추가하여 병렬처리를 금지하고 수행해 본다.
‘max degree of parallelism’ 구성 옵션을 기본값으로 그대로 두지 말고, 1과 같이 작은 값으로 설정하는 것이 성능 향상에 도움이 되는 경우가 종종 있습니다.
max degree of parallelism 옵션 설정:
max degree of parallelism 옵션은 고급 옵션입니다.
show advanced options를 1인 경우에만 sp_configure를 사용하여 max degree of parallelism의 구성값을 변경할 수 있습니다.
이 설정은 MSSQLSERVER 서비스를 다시 시작하지 않아도 즉시 적용됩니다.
sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 8;
RECONFIGURE WITH OVERRIDE;
GO
USE AdventureWorks ;
GO
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
.중요:
SQL Server 2005 의 쿼리 최적화 프로그램은 일반적으로 쿼리에 대해 최상의 실행 계획을 선택하므로 <query_hint>를 비롯한 힌트는 숙련된 개발자나 데이터베이스 관리자가 최후의 수단으로만 사용하는 것이 좋습니다.
힌트는 최후의 수단으로 사용해야 하며, 힌트를 사용하기 전에 다음 항목들을 먼저 점검해보아야 한다. 인덱스 생성, 통계 업데이트, 인덱스 조각모음, SARG 검토, 서비스 팩 혹은 패치
적어도 SQL SERVER 에서는 힌트가 표준 쿼리 기법이 아니다.