Optimize for hint 쿼리 최적화

 

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

 

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

 

 

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/

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp





profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.