sys.dm_exec_query_optimizer_info(Transact-SQL)

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

SQL Server 쿼리 최적화 프로그램의 작업에 대한 자세한 통계를 반환합니다. 쿼리 최적화 문제 또는 개선점을 식별하는 작업을 튜닝할 때 이 뷰를 사용할 수 있습니다. 예를 들어 총 최적화 수, 경과된 시간 값 및 최종 비용 값을 사용하여 현재 작업의 쿼리 최적화와 튜닝 프로세스 동안 관찰된 모든 변경 내용을 비교할 수 있습니다. 일부 카운터는 SQL Server 내부 진단 사용과 관련된 데이터를 제공합니다. 이러한 카운터는 "내부 전용"으로 표시됩니다.

참고 항목

Azure Synapse Analytics 또는 PDW(Analytics Platform System)에서 이를 호출하려면 이름을 sys.dm_pdw_nodes_exec_query_optimizer_info사용합니다. 이 구문은 Azure Synapse Analytics의 서버리스 SQL 풀에서 지원되지 않습니다.

속성	데이터 유형	설명
counter	nvarchar(4000)	최적화 프로그램 통계 이벤트의 이름입니다.
occurrence	bigint	이 카운터에 대한 최적화 이벤트의 발생 횟수입니다.
value	float	이벤트 발생당 평균 속성 값입니다.
pdw_node_id	int	이 배포가 있는 노드의 식별자입니다. 적용 대상: Azure Synapse Analytics, Analytics Platform System(PDW)

사용 권한

SQL Server 2019(15.x) 및 이전 버전 및 Azure SQL Managed Instance에는 권한이 필요합니다 VIEW SERVER STATE .

SQL Server 2022(16.x) 이상 버전에는 서버에 대한 권한이 필요합니다 VIEW SERVER PERFORMANCE STATE .

Azure SQL Database Basic, S0 및 S1 서비스 목표 및 탄력적 풀의 데이터베이스에는 서버 관리자 계정, Microsoft Entra 관리자 계정 또는 ##MS_ServerStateReader## 서버 역할의 멤버 자격이 필요합니다. 다른 모든 SQL Database 서비스 목표 VIEW DATABASE STATE 에서 데이터베이스에 대한 사용 권한 또는 ##MS_ServerStateReader## 서버 역할의 멤버 자격이 필요합니다.

설명

sys.dm_exec_query_optimizer_info 에는 다음 속성(카운터)이 포함됩니다. 모든 발생 값은 누적되며 시스템 다시 시작 시로 0 설정됩니다. 값 필드의 모든 값은 시스템을 다시 시작할 때로 NULL 설정됩니다. 평균을 지정하는 모든 값 열 값은 평균 계산에서 분모와 동일한 행의 발생 값을 사용합니다. 모든 쿼리 최적화는 SQL Server가 사용자가 생성한 쿼리와 시스템 생성 쿼리를 포함하여 변경 내용을 dm_exec_query_optimizer_info결정할 때 측정됩니다. 이미 캐시된 계획을 실행해도 값 dm_exec_query_optimizer_info은 변경되지 않으며 최적화만 중요합니다.

카운터	항목	값
optimizations	총 최적화 수입니다.	해당 없음
elapsed time	총 최적화 수입니다.	개별 문(쿼리)의 최적화당 평균 경과 시간(초)입니다.
final cost	총 최적화 수입니다.	내부 비용 단위의 최적화된 계획에 대한 평균 예상 비용입니다.
trivial plan	내부 전용	내부 전용
tasks	내부 전용	내부 전용
no plan	내부 전용	내부 전용
search 0	내부 전용	내부 전용
search 0 time	내부 전용	내부 전용
search 0 tasks	내부 전용	내부 전용
search 1	내부 전용	내부 전용
search 1 time	내부 전용	내부 전용
search 1 tasks	내부 전용	내부 전용
search 2	내부 전용	내부 전용
search 2 time	내부 전용	내부 전용
search 2 tasks	내부 전용	내부 전용
gain stage 0 to stage 1	내부 전용	내부 전용
gain stage 1 to stage 2	내부 전용	내부 전용
timeout	내부 전용	내부 전용
memory limit exceeded	내부 전용	내부 전용
insert stmt	문에 대한 INSERT 최적화 수입니다.	해당 없음
delete stmt	문에 대한 DELETE 최적화 수입니다.	해당 없음
update stmt	문에 대한 UPDATE 최적화 수입니다.	해당 없음
contains subquery	하나 이상의 하위 쿼리를 포함하는 쿼리에 대한 최적화 수입니다.	해당 없음
unnest failed	내부 전용	내부 전용
tables	총 최적화 수입니다.	최적화된 쿼리당 참조되는 평균 테이블 수입니다.
hints	일부 힌트가 지정된 횟수입니다. 개수에 포함된 힌트는 다음과 JOIN같습니다. , GROUPUNION 및 FORCE ORDER 쿼리 힌트, FORCE PLAN 설정 옵션 및 조인 힌트.	해당 없음
order hint	조인 순서가 강제 적용된 횟수입니다. 이 카운터는 힌트로 FORCE ORDER 제한되지 않습니다. 쿼리 내에서 조인 알고리즘(예: 조인 알고리즘)을 INNER HASH JOIN지정하면 조인 순서가 강제로 증가하여 카운터가 증가합니다.	해당 없음
join hint	조인 힌트에 의해 조인 알고리즘이 강제로 적용된 횟수입니다. 쿼리 힌트는 FORCE ORDER 이 카운터를 증가하지 않습니다.	해당 없음
view reference	쿼리에서 뷰를 참조하는 횟수입니다.	해당 없음
remote query	4부로 구성된 이름 또는 OPENROWSET 결과가 있는 테이블과 같이 쿼리가 하나 이상의 원격 데이터 원본을 참조하는 최적화 수입니다.	해당 없음
maximum DOP	총 최적화 수입니다.	최적화된 계획의 평균 유효 MAXDOP 값입니다. 기본적으로 유효 MAXDOP 는 최대 병렬 처리 서버 구성 옵션에 의해 결정되며 쿼리 힌트 값으로 특정 쿼리에 대해 재정의 MAXDOP 될 수 있습니다.
maximum recursion level	쿼리 힌트를 사용하여 MAXRECURSION 지정된 수준보다 0 큰 최적화 수입니다.	쿼리 힌트를 사용하여 최대 재귀 수준을 지정한 최적화의 평균 MAXRECURSION 수준입니다.
indexed views loaded	내부 전용	내부 전용
indexed views matched	하나 이상의 인덱싱된 뷰가 일치하는 최적화 수입니다.	일치된 평균 뷰 수입니다.
indexed views used	일치된 후 하나 이상의 인덱싱된 뷰가 출력 계획에 사용되는 최적화 수입니다.	사용된 평균 뷰 수입니다.
indexed views updated	하나 이상의 인덱싱된 뷰를 유지하는 계획을 생성하는 DML 문의 최적화 수입니다.	유지 관리되는 평균 뷰 수입니다.
dynamic cursor request	동적 커서 요청이 지정된 최적화 횟수입니다.	해당 없음
fast forward cursor request	빠른 전달 커서 요청이 지정된 최적화 횟수입니다.	해당 없음
merge stmt	문에 대한 MERGE 최적화 수입니다.	해당 없음

예제

A. 최적화 프로그램 실행에 대한 통계 보기

이 SQL Server 인스턴스에 대한 현재 최적화 프로그램 실행 통계는 무엇인가요?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. 총 최적화 수 보기

수행되는 최적화는 몇 개입니까?

SELECT occurrence AS Optimizations
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';

C. 최적화당 평균 경과 시간

최적화당 평균 경과 시간은 얼마인가요?

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'elapsed time';

D. 하위 쿼리를 포함하는 최적화의 분수

하위 쿼리를 포함하는 최적화 쿼리 부분을 확인할 수 있습니다.

SELECT (
    SELECT CAST(occurrence AS FLOAT)
    FROM sys.dm_exec_query_optimizer_info
    WHERE counter = 'contains subquery'
) / (
    SELECT CAST(occurrence AS FLOAT)
    FROM sys.dm_exec_query_optimizer_info
    WHERE counter = 'optimizations'
) AS ContainsSubqueryFraction;

E. 최적화 중 총 힌트 수 보기

쿼리 힌트로 포함될 때 FORCE ORDER 계산되는 힌트는 몇 개입니까?

-- Check hint count before query execution
SELECT ISNULL('', 0) AS [Before],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

SELECT poh.PurchaseOrderID,
    poh.OrderDate,
    pod.ProductID,
    pod.DueDate,
    poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
OPTION (
    FORCE ORDER,
    RECOMPILE
);

-- check hint count after query execution
SELECT ISNULL('', 0) AS [After],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

관련 콘텐츠

• 동적 관리 뷰 및 함수(Transact-SQL)
• 실행 관련 동적 관리 뷰 및 함수(Transact-SQL)