Optimize for hint 쿼리 최적화

Optimize for hint 쿼리 최적화

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server는 쿼리를 실행 할 때 다양한 비용을 계산하여 최적의 계획을 생성하고 사용 한다. 그리고 이렇게 생성된 실행 계획은 재사용 된다.

 

• 매개 변수 및 실행 계획 재사용 - http://sqlmvp.kr/140188831357
• 실행 계획 캐싱 및 다시 사용 - http://sqlmvp.kr/140188765472

 

SQL Server에서는 최적의 실행 계획을 선택하지 않고 하나 이상의 다른 실행계획을 사용할 수 있도록 강제 할 수 있는 힌트도 있다. 예를 들어 Where 절에 특정한 값이 사용됨에 따라 실행 계획이 변경되고 쿼리 실행 시간이 오래 걸리는 경우가 있다. 이런 경우 힌트를 사용하여 실행 계획을 만들 때 매개 변수 값을 지정 할 수 있다.

 

OPTIMIZE FOR 힌트는 쿼리가 컴파일 되고 최적화 될 때 쿼리의 최적화 프로그램이 지역 변수에 특정 값을 사용하도록 지시할 수 있다. 해당 값은 쿼리 최적화 중에만 사용되고 쿼리 실행 중에는 사용되지 않는다.

 

실습을 통해서 OPTIMIZE FOR 동작에 대해서 알아본다. AdventureWorks 데이터베이스를 사용하였다.

OPTIMIZE FOR를 사용하지 않고 매개변수를 사용하였다.

DECLARE @Country VARCHAR ( 20 ) SET @Country = 'US'   SELECT * FROM Sales.SalesOrderHeader h , Sales.Customer c , Sales.SalesTerritory t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country

 

 

 

이 쿼리에 대한 비용은 1.34408 이다.

 

 

이번에는 OPTIMIZE FOR 힌트를 지정하고 매개 변수 값으로 "CA"를 사용하여 실행 계획을 사용하도록 하였다. 실행계획을 살펴보면 OPTIMIZE FOR를 사용하지 않은 쿼리의 실행계획과 비교하였을 때 작업의 비율이 조금 다른 것을 제외하곤 계획이 동일하게 보인다.

DECLARE @Country VARCHAR(20) SET @Country = 'US'   SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country OPTION (OPTIMIZE FOR (@Country = 'CA'))

 

 

이 쿼리에 대한 전체 비용은 더 적은 1.19891 이다.

 

 

이번에는 "CA" 대신 "US"를 매개변수로 사용하였다. 실행 계획을 살펴보면 동일한 실행 계획이 사용된 것을 확인 할 수 있다.

DECLARE @Country VARCHAR(20) SET @Country = 'US'   SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country OPTION (OPTIMIZE FOR (@Country = 'US'))

 

 

쿼리에 대한 전체 비용은 1.69949 이다. 이는 위의 예제1, 예제 2보다 더 큰 비용이 발생 하였다.

 

간단한 테스트를 하여 OPTIMIZE FOR 힌트를 사용하여 쿼리의 계획을 변경 할 수 있는 것을 확인하였다. 하지만 마지막 결과에서 보듯이 부정적인 영향을 미칠 수도 있으니 충분한 이해와 검토 후 사용할 수 있도록 한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/1354/optimize-parameter-driven-queries-with-sql-server-optimize-for-hint/

 

 

2014-07-24 / 강성욱 / http://sqlmvp.kr

 

Msslq, 쿼리 튜닝, 쿼리 힌트, DB 튜닝, OPTIMIZE FOR, 쿼리 최적화, 매개변수, SQL튜닝, 실행계획, 플랜캐시