Поделиться через

sys.dm_exec_query_optimizer_info (Transact-SQL)

  • Статья

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

Возвращает подробную статистику о работе оптимизатора запросов SQL Server. Это представление можно использовать для настройки рабочей нагрузки при обнаружении проблем, связанных с оптимизацией запросов, или для улучшения производительности обработки запросов. Например, можно использовать общее число оптимизаций, значение затрачиваемого времени и значение конечной стоимости для сравнения с оптимизацией запросов текущей рабочей нагрузки и любыми изменениями во время процесса настройки. Некоторые счетчики предоставляют данные, относящиеся только к внутреннему использованию SQL Server. Эти счетчики помечены атрибутом «Только для внутреннего использования.».

Примечание.

Чтобы вызвать это из Azure Synapse Analytics или Analytics Platform System (PDW), используйте имя sys.dm_pdw_nodes_exec_query_optimizer_info. Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Имя. Тип данных Description
counter nvarchar(4000) Имя события статистики оптимизатора.
occurrence bigint Количество вхождений события оптимизации для этого счетчика.
value float Среднее значение свойства для вхождения события.
pdw_node_id int Идентификатор узла, на который находится данное распределение.

Область применения: Azure Synapse Analytics, Analytics Platform System (PDW)

Разрешения

SQL Server 2019 (15.x) и более ранних версий и Управляемый экземпляр SQL Azure требуют VIEW SERVER STATE разрешения.

ДЛЯ SQL Server 2022 (16.x) и более поздних версий требуется VIEW SERVER PERFORMANCE STATE разрешение на сервере.

Для целей службы База данных SQL Azure "Базовый", "S0" и "S1" и для баз данных в эластичных пулах требуется учетная запись администратора сервера, учетная запись администратора Microsoft Entra или членство в роли сервера #MS_ServerStateReader#. Для всех остальных целей VIEW DATABASE STATE службы База данных SQL требуется разрешение на базу данных или членство в роли сервера ##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 Количество оптимизаций, на которые ссылается запрос по крайней мере на один удаленный источник данных, например таблицу с четырехкомпонентным именем или результатом 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 инструкций. Нет данных

Примеры

А. Просмотр статистики по выполнению оптимизатора

Что такое текущая статистика выполнения оптимизатора для этого экземпляра 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';

В. Среднее время, затраченное на операцию оптимизации

Каково среднее время, затраченное на операцию оптимизации?

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;

Е. Просмотр общего количества подсказок во время оптимизации

Сколько подсказок учитывается при 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'
);

Связанный контент