Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화

Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화

 

• Version : SQL Server 2012, 2014, 2016

 

SQL Server는 쿼리를 실행 할 때 다양한 비용을 계산하여 최적의 계획을 생성하고 사용한다. 여러 사용 가능한 실행 계획 중에 비용이 가장 적게 드는 하나의 실행 계획을 선택하는 프로세스를 최적화라 한다.

 

쿼리 처리에 대한 아키텍처는 아래 링크를 참고 한다.

• SQL Server 쿼리 처리 아키텍처 : http://sqlmvp.kr/140188321707

 

SQL Server에서 쿼리를 실행 할 때 옵티마이저가 최적의 실행 계획을 만들어 사용할 수도 있지만 쿼리를 튜닝하는 과정에서 옵티마이저의 최적화를 사용하지 않도록 비활성화 할 수 있다. 문서화 되지 않은 Query Rule Off 명령을 사용하여 옵티마이저를 비활성화 하는 방법에 대해서 살펴본다.

 

아래 스크립트를 실행하면 현재 SQL Server의 옵티마이저 규칙을 확인할 수 있다. 매우 많은 규칙 목록이 있음을 확인할 수 있다.

USE master GO   DBCC TRACEON(3604) GO   DBCC SHOWONRULES GO   DBCC SHOWOFFRULES GO

 

 

규칙 이름은 대부분 해석하기 쉽게 되어 있다. 몇 가지 규칙에 대한 설명을 살펴보면 다음과 같다.

Rule Name	Description
JNtoNL	Join to Nested Loop
JNtoHS	Join to Hash
JNtoSM	Join to Sort Merge
LOJNtoNL	Left Outer Join to Nested Loop
LSJNtoHS	Left Semi join to Hash
LASJNtoSM	Left Anti Semi Join to Sort Merge

 

Query Rule OFF를 적용하면 어떻게 쿼리 실행계획이 변경되는지 실습을 통해서 알아본다. 이번 실습은 AdventureWorks2012를 사용하였다.

• AdventureWorks2012 : http://msftdbprodsamples.codeplex.com/releases/view/55330

 

아래 스크립트를 실행하여 쿼리 실행 계획을 확인해보자. 옵티마이저는 최적화 규칙으로 해시조인을 사용하여 조인 순서를 결정하였다.

USE AdventureWorks2012 GO   SELECT     c.CustomerID ,     c.PersonID ,     c.StoreID ,     c.TerritoryID ,     c.AccountNumber ,     c.rowguid ,     c.ModifiedDate FROM Sales.SalesOrderHeader OH     INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID WHERE OH.ShipMethodID = 1 GO

 

 

아래 스크립트는 QUERYRULEOFF JoinCommute를 사용하여 옵티마이저 비활성화와 함께 조인의 순서를 결정하지 않도록 하였다. 조인 순서가 변경된 것을 확인할 수 있다.

USE AdventureWorks2012 GO   SELECT c.CustomerID , c.PersonID , c.StoreID , c.TerritoryID , c.AccountNumber , c.rowguid , c.ModifiedDate FROM Sales.SalesOrderHeader OH INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID WHERE OH.ShipMethodID = 1 OPTION( QUERYRULEOFF JoinCommute ) GO

 

 

위의 두 실행계획에서 더 나은 실행 계획을 비교하기 위해 예상 하위 트리 비용을 비교해보았다. 이렇게 하면 JoinCommute를 사용한 쿼리 계획이 더 낮은 비용을 사용하였음을 확인할 수 있다.

 

아래 스크립트는 옵티마이저가 JNtoHS 규칙을 사용하지 않도록 하였다. 옵티마이저는 JNtoHS 규칙을 제외한 나머지 규칙에서 최적화 계획은 Merge를 사용한 것을 확인할 수 있다.

USE AdventureWorks2012 GO   SELECT c.CustomerID , c.PersonID , c.StoreID , c.TerritoryID , c.AccountNumber , c.rowguid , c.ModifiedDate FROM Sales.SalesOrderHeader OH INNER JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID WHERE OH.ShipMethodID = 1 OPTION( QUERYRULEOFF JNtoHS ) GO

 

 

하위 트리 비용을 살펴보면 위의 두 상황보다 나쁜 선택을 한 것을 알 수 있다.

 

 

옵티마이저가 최적의 실행계획을 선택하기 위해서는 다양한 정보 (통계, 인덱스 등등)를 고려하여 판단하기 때문에 우리가 생각하는 이상의 복잡도를 가지고 있다. 옵티마이저를 비활성화 하여 사용하는 경우는 옵타미이저의 특성에 대해서 잘 알고 내가 의도하는 방향으로 실행 계획을 유도할 수 있을때만 사용할 수 있도록 한다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4175/disabling-sql-server-optimizer-rules-with-queryruleoff/

 

2016-03-03 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MS SQL, 옵타미이저, Optimizer, 쿼리 최적화, 쿼리 튜닝, TRACEON, QUERYRULEOFF, Optimizer Rule, DB튜닝, SQL튜닝