SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화

SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화

 

·         Version : SQL Server 2016 later

 

SQL Server에서는 서버의 특정 동작을 임시로 설정 하거나 중지 시킬때 사용하는 추적 플래그(Trace Flag)라는 기능이 있다. 추적 플래그는 주로 성능 문제를 진단하거나 저장 프로시저 또는 복잡한 컴퓨터 시스템을 디버깅하는데 사용된다.

·         DBCC TRACE ON – Trace Flags :  https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

 

추적 플래그를 쿼리에서 사용할때 지금까지는 사용하려는 추적 플래그의 번호를 사용하였다.

SELECT x FROM correlated WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137)

 

SQL Server 2016 SP1에서는 USE HINT 라는  쿼리 힌트를 도입하여 사용자가 추적 플래그 번호를 기억할 필요 없이 추적 플래그 이름을 사용할 수 있게 되었다. 아래 두 쿼리는 동일한 기능의 추적 플래그를 실행 한다.  

SELECT TOP 1000 col1 FROM tbl1 OPTION (QUERYTRACEON 4138);   SELECT TOP 1000 col1 FROM tbl1 OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

 

SQL Server 2017 CU10에서는 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 이라는 몇 가지 새로운 USE 힌트를 도입했다. 여기서 n은 지원되는 데이터베이스 호환성 수준이다. 이렇게 하면 쿼리가 데이터베이스 호환성 수준으로 컴파일된 것처럼 쿼리 수준에서 쿼리 최적화 프로그램 동작이 강제로 수행된다. N에 대해 지원되는 값을 보려면 아래 링크를 참고 한다.

·         sys.dm_exec_valid_use_hints : https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-valid-use-hints-transact-sql?view=sql-server-2017

 

새로운 힌트는 특정 CE 모델만 강제하는 것은 아니며 데이터베이스 호환성 수준에서 기본적으로 활성화되는 쿼리 최적화 프로그램 수정을 비롯하여 특정 베이터베이스 호환성 수준의 쿼리 최적화 프로그램 동작과 동등하다.

아래 예제는 새로운 힌트를 사용하여 데이터베이스 호환성 수준이 SQL Server 2014수준(상단의 실행 계획)으로 유지되는 것처럼 해당 특정 쿼리를 컴파일 할 수 있었으며 SQL Server 2017 계획(하단의 실행 계획)과의 차이점을 확인할 수 있다.

 

등록 정보를 보면 사용된 CE 버전(120 vs 130)과 각 QueryTimeStats간의 차이점을 확인할 수 있다.

 

모든 힌트는 특정 행동을 강요하고 향후 업그레이드를 통해서 해결 될 수도 있다. Microsoft에서는 다른 옵션이 없는 경우에만 힌트를 적용하고 힌트가 있는 코드를 새로 업그레이드 할 때마다 다시 계획 할 것을 권장한다. 힌트를 통한 강제 적용은 최신 버전에서 향샹된 기능의 이점을 워크로드에서 제외할 수도 있다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/developers-choice-hinting-query-execution-model/

 

 

 

2018-10-01 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, 쿼리 힌트, USE 쿼리 힌트, 추적 플래그, Query Hint, Trace Flag, USE Hint