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

 

  • Version : SQL Server 2012, 2014, 2016

 

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

 

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

 

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를 사용하였다.

 

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

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/

 




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

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 15625
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 9498
2054 확장이벤트 사용시 주의사항 jevida(강성욱) 2017.01.11 1684
2053 Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기 jevida(강성욱) 2017.01.11 3602
» Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화 jevida(강성욱) 2017.01.11 1649
2051 시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기 jevida(강성욱) 2017.01.11 2564
2050 In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고 jevida(강성욱) 2017.01.11 1322
2049 Spool 연산자와 추적 플래그 8690 jevida(강성욱) 2017.01.11 1998
2048 .NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작 jevida(강성욱) 2017.01.11 1651
2047 함수 통계 정보 확인 (sys.dm_exec_function_stats) jevida(강성욱) 2017.01.11 1619
2046 NULL 데이터가 포함된 데이터 사용 시 주의점 jevida(강성욱) 2017.01.11 2933
2045 통계정보와 실제 데이터 분포 확인하기 jevida(강성욱) 2017.01.11 1721
2044 SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류 jevida(강성욱) 2017.01.11 1539
2043 SSIS 실행 로그 남기기 jevida(강성욱) 2017.01.11 2786
2042 확장이벤트를 사용한 실행 계획 캡처 jevida(강성욱) 2017.01.11 1279
2041 테이블 외래키 트리 확인 및 데이터 삭제하기 jevida(강성욱) 2017.01.11 2034
2040 기본 추적을 사용한 SQL Server 스키마 변경사항 캡처 jevida(강성욱) 2017.01.11 1655
2039 SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기 jevida(강성욱) 2017.01.11 1758
2038 SQL Server 임시 테이블 특성 jevida(강성욱) 2017.01.11 7263
2037 트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) jevida(강성욱) 2017.01.11 2262
2036 컬럼스토어 인덱스 성능 (Columnsotre Index Performance) jevida(강성욱) 2017.01.11 3259
2035 In-Memory 최적화 파일 경로 변경 jevida(강성욱) 2017.01.11 1544





XE Login