sys.dm_exec_query_optimizer_info(Transact-SQL)
적용 대상: 
SQL ServerAzure SQL 데이터베이스Azure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 최적화 수입니다. |
해당 없음 |
merge stmt |
문에 대한 MERGE 최적화 수입니다. |
해당 없음 |
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 |
빠른 전달 커서 요청이 지정된 최적화 횟수입니다. | 해당 없음 |
예제
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'
);