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. Эти счетчики помечены атрибутом «Только для внутреннего использования.».

Разрешения

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, только оптимизации являются значительными.

Примеры

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

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

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

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