SQL Server USE HINT( 'hint_name' )

파라미터 스니핑 이슈를 해결하기 위한 방법중 하나로 평균 통계를 바라보게 하는방법이 있다

USE HINT

 

A 컬럼의 통계를 히스토그램을 보는것이 아닌 평균 통계를 바라보게 하여 플랜 수립

SELECT * FROM TEST WHERE A = @A
OPTION(USE HINT('DISABLE_PARAMETER_SNIFFING'))

 

OPTIMIZE FOR 은 @SEQ 값을 10000의 값으로 실행계획은 수립한다.

DECLARE @seq int = 10

select  
    count(*) from
    TEST1M
    where seq < @seq
    option(optimize for(@seq = 10000))

 

실행 계획

<ParameterList>
	<ColumnReference Column="@seq" ParameterDataType="int" ParameterCompiledValue="(10000)" ParameterRuntimeValue="(10)" />
</ParameterList>

 

그외

통계조작,  통계 갱신등 몇가지 방법이 더 있다.

 

아래는 USE HINT로 사용 가능한 힌트 리스트

 

적용 대상: SQL Server (SQL Server 2016(13.x) SP1부터) 및 Azure SQL Database.

쿼리 프로세서에 하나 이상의 추가 힌트를 제공합니다. 추가 힌트는 작은따옴표 안의 힌트 이름으로 지정됩니다.

지원되는 힌트는 다음과 같습니다.

• 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
• SQL Server가 SQL Server 2014(12.x) 이상의 쿼리 최적화 프로그램 카디널리티 추정 모델에서 조인에 기본 베이스 제약 가정 대신 단순 제약을 사용하여 쿼리 계획을 생성하게 합니다. 이 힌트 이름은 추적 플래그 9476과 동일합니다.

 

• 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
• 전체 상관 관계를 설명하기 위해 필터에 대한 AND 조건자를 추정할 때 SQL Server에서 최소 선택도를 사용하여 플랜을 생성하도록 합니다. 이 힌트 이름은 SQL Server 2012(11.x) 및 그 이전 버전의 카디널리티 추정 모델에 사용하던 추적 플래그 4137에 해당하며 추적 플래그 9471을 카디널리티 추정 모델 SQL Server 2014(12.x) 이상에서 사용할 때 결과가 비슷합니다.
• ‘ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES’
• 전체 독립성을 설명하기 위해 필터에 대한 AND 조건자를 추정할 때 SQL Server에서 최대 선택도를 사용하여 플랜을 생성하도록 합니다. 이 힌트 이름은 SQL Server 2012(11.x) 및 이전 버전에서 카디널리티 추정 모델의 기본 동작이며, SQL Server 2014(12.x) 이상의 카디널리티 추정 모델에서 사용되는 경우 추적 플래그 9472와 동일합니다.

 

• ‘ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES’
• 부분 상관 관계를 설명하기 위해 필터에 대한 AND 조건자를 추정할 때 SQL Server에서 최대부터 최소까지 선택도를 사용하여 플랜을 생성하도록 합니다. 이 힌트 이름은 SQL Server 2014(12.x) 이상에서 카디널리티 추정 모델의 기본 동작입니다.
• 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'적용 대상: SQL Server(SQL Server 2017(14.x)부터) 및 Azure SQL Database
• 일괄 처리 모드 적응 조인을 사용 하지 않습니다. 자세한 내용은 일괄 처리 모드 적응 조인을 참조하세요.
• 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
  • 적용 대상: SQL Server(SQL Server 2017(14.x)부터) 및 Azure SQL Database
• 일괄 처리 모드 메모리 부여 피드백을 사용하지 않습니다. 자세한 내용은 일괄 처리 모드 메모리 부여 피드백을 참조합니다.
• 'DISABLE_DEFERRED_COMPILATION_TV'
  • 적용 대상: SQL Server(SQL Server 2019 (15.x)부터) 및 Azure SQL Database
• 테이블 변수 지연 컴파일을 사용하지 않도록 설정합니다. 자세한 내용은 테이블 변수 지연 컴파일을 참조하세요.
• 'DISABLE_INTERLEAVED_EXECUTION_TVF'
  • 적용 대상: SQL Server(SQL Server 2017(14.x)부터) 및 Azure SQL Database
• 다중 문 테이블 반환 함수에 대한 인터리브 실행을 사용하지 않도록 설정합니다. 자세한 내용은 다중 명령문 테이블 반환 함수에 대한 인터리브 실행을 참조하세요.
• 'DISABLE_OPTIMIZED_NESTED_LOOP'
• 쿼리 프로세서가 쿼리 계획을 생성할 때 최적화된 중첩 루프 조인을 위해 정렬 연산(일괄 처리 정렬)을 사용하지 않도록 지시합니다 이 힌트 이름은 추적 플래그 2340과 동일합니다.
• 'DISABLE_OPTIMIZER_ROWGOAL'
  • 맨 위로 이동
  • OPTION (FAST N)
  • IN
  • EXISTS
  이 힌트 이름은 추적 플래그 4138과 동일합니다.
• SQL Server가 다음 키워드를 포함하는 쿼리에 행 목표 수정을 사용하지 않는 계획을 생성하게 합니다.
• 'DISABLE_PARAMETER_SNIFFING'
• 쿼리 최적화 프로그램이 하나 이상의 매개 변수가 있는 쿼리를 컴파일할 때 평균 데이터 분산을 사용하도록 지시합니다. 이 지시를 통해 쿼리 계획에서는 쿼리를 컴파일할 때 처음 사용된 매개 변수 값이 사용되지 않습니다. 추적 플래그 4136 또는 데이터베이스 범위 구성 설정 PARAMETER_SNIFFING = OFF에 해당합니다.
• 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'적용 대상: SQL Server(SQL Server 2019 (15.x)부터) 및 Azure SQL Database
• 행 모드 메모리 부여 피드백을 비활성화합니다. 자세한 내용은 행 모드 메모리 부여 피드백을 참조하세요.
• 'DISABLE_TSQL_SCALAR_UDF_INLINING'
  • 적용 대상: SQL Server(SQL Server 2019 (15.x)부터) 및 Azure SQL Database
• 스칼라 UDF 인라인을 비활성화합니다. 자세한 내용은 스칼라 UDF 인라인 처리를 참조하세요.
• 'DISALLOW_BATCH_MODE'
  • 적용 대상: SQL Server(SQL Server 2019 (15.x)부터) 및 Azure SQL Database
• 일괄 처리 모드 실행을 사용하지 않도록 설정합니다. 자세한 내용은 실행 모드를 참조하세요.
• 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
• 카디널리티 추정이 필요한 모든 선행 인덱스 열에 대해 자동으로 생성된 빠른 통계(히스토그램 수정)를 사용합니다. 카디널리티 추정에 사용되는 히스토그램은 이 열의 실제 최댓값 또는 최솟값을 반영하기 위해 쿼리 컴파일 시점에 조정됩니다. 이 힌트 이름은 추적 플래그 4139와 동일합니다.
• 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
• 쿼리 최적화 프로그램 핫픽스(SQL Server 누적 업데이트 및 Service Pack에서 릴리스된 변경 내용)를 사용하도록 설정합니다. 추적 플래그 4199 또는 데이터베이스 범위 구성 설정 QUERY_OPTIMIZER_HOTFIXES = ON에 해당합니다.
• 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
• 쿼리 최적화 프로그램이 현재 데이터베이스 호환성 수준에 해당하는 카디널리티 추정 모델을 사용하도록 강제 적용합니다. 이 힌트를 사용하여 데이터베이스 범위 구성 설정 LEGACY_CARDINALITY_ESTIMATION = ON 또는 추적 플래그 9481을 재정의합니다.
• 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
• 쿼리 최적화 프로그램이 SQL Server 2012(11.x) 및 이전 버전의 카디널리티 추정 모델을 사용하도록 강제 적용합니다. 추적 플래그 9481 또는 데이터베이스 범위 구성 설정 LEGACY_CARDINALITY_ESTIMATION = ON에 해당합니다.
• 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'적용 대상: SQL Server(SQL Server 2017(14.x) CU10 이상) 및 Azure SQL Database
•  참고
• 데이터베이스 범위 구성, 추적 플래그 또는 다른 쿼리 힌트(예: QUERYTRACEON)를 통해 적용되는 경우 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 힌트는 기본 또는 레거시 카디널리티 예상 설정을 재정의하지 않습니다.
  이 힌트는 쿼리 최적화 프로그램의 동작에만 영향을 줍니다. 특정 데이터베이스 기능의 가용성과 같이, 데이터베이스 호환성 수준에 따라 달라질 수 있는 SQL Server의 다른 기능에는 영향을 주지 않습니다.
  이 힌트에 대한 자세한 내용은 개발자 선택 사항: 힌트 쿼리 실행 모델을 참조하세요.
• 쿼리 수준에서 쿼리 최적화 프로그램 동작을 적용합니다. 이 동작은 쿼리가 데이터베이스 호환성 수준 n으로 컴파일된 것처럼 나타납니다. 여기서 n은 지원되는 데이터베이스 호환성 수준입니다(예: 100, 130 등). n에 대해 현재 지원되는 값 목록은 sys.dm_exec_valid_use_hints를 참조하세요.
• 'QUERY_PLAN_PROFILE'
  • 적용 대상: SQL Server (SQL Server 2016(13.x) SP2 CU3 및 SQL Server 2017(14.x) CU11부터 시작).
•  참고
• query_post_execution_showplan 확장 이벤트 수집을 사용하도록 설정하는 경우 서버에서 실행되는 모든 쿼리에 표준 프로파일링 인프라가 추가되므로 전체 서버 성능에 영향을 미칠 수 있습니다.
  대신 query_thread_profile 확장 이벤트의 컬렉션을 사용하도록 설정하여 간단한 프로파일링 인프라를 사용하는 경우 성능 오버헤드가 훨씬 감소하지만 전체 서버 성능에는 여전히 영향을 미칩니다.
  query_plan_profile 확장 이벤트를 사용하도록 설정하는 경우 QUERY_PLAN_PROFILE로 실행된 쿼리의 간단한 프로파일링 인프라만 사용하므로 서버의 다른 워크로드에는 영향을 미치지 않습니다. 이 힌트를 사용하여 서버 워크로드의 다른 부분에 영향을 미치지 않고 특정 쿼리를 프로파일링하세요. 간단한 프로파일링에 대한 자세한 내용은 쿼리 프로파일링 인프라를 참조하세요.
• 쿼리에 대해 간단한 프로파일링을 사용합니다. 이 새 힌트가 포함된 쿼리가 완료되면 새 확장 이벤트 query_plan_profile이 발생합니다. 이 확장 이벤트는 query_post_execution_showplan 확장 이벤트와 유사한 실행 통계 및 실제 실행 계획 XML을 표시하지만 새 힌트가 포함된 쿼리에 대해서만 표시합니다.

지원되는 모든 USE HINT 이름 목록은 동적 관리 뷰 sys.dm_exec_valid_use_hints를 사용하여 쿼리할 수 있습니다.

 팁

힌트 이름은 대/소문자를 구분하지 않습니다.

 중요

일부 USE HINT 힌트는 전역 또는 세션 수준에서 사용하는 추적 플래그나 데이터베이스 범위 구성 설정과 충돌할 수 있습니다. 이 경우 쿼리 수준 힌트(USE HINT)가 항상 우선합니다. USE HINT가 쿼리 수준에서 사용하는 다른 쿼리 힌트나 추적 플래그와 충돌하는 경우(예: QUERYTRACEON) SQL Server가 쿼리를 실행할 때 오류를 생성합니다.