sys.dm_exec_query_optimizer_info (Transact-SQL)
Se aplica a: SQL Server
Azure SQL Database
Azure SQL Instancia administrada
Azure Synapse Analytics Analytics
Platform System (PDW)
Devuelve estadísticas detalladas sobre el funcionamiento del optimizador de consultas de SQL Server. Puede usar esta vista al optimizar una carga de trabajo para identificar los problemas o las mejoras de la optimización de consultas. Por ejemplo, puede usar el número total de optimizaciones, el valor de tiempo transcurrido y el valor del costo final para comparar las optimizaciones de las consultas de la carga de trabajo actual y los cambios observados durante el proceso de optimización. Algunos contadores proporcionan datos que solo son relevantes para el uso de diagnóstico interno de SQL Server. Estos contadores se marcan como "Solo para uso interno".
Nota:
Para llamar a esto desde Azure Synapse Analytics o Analytics Platform System (PDW), use el nombre sys.dm_pdw_nodes_exec_query_optimizer_info
. El grupo de SQL sin servidor no admite esta sintaxis en Azure Synapse Analytics.
Nombre | Tipo de datos | Descripción |
---|---|---|
counter |
nvarchar(4000) | Nombre del evento de estadísticas del optimizador. |
occurrence |
bigint | Número de repeticiones del evento de optimización para este contador. |
value |
float | Valor promedio de la propiedad por repetición del evento. |
pdw_node_id |
int | Identificador del nodo en el que se encuentra esta distribución. Se aplica a: Azure Synapse Analytics, Sistema de la plataforma de análisis (PDW) |
Permisos
SQL Server 2019 (15.x) y versiones anteriores, y Azure SQL Instancia administrada, requieren VIEW SERVER STATE
permiso.
SQL Server 2022 (16.x) y versiones posteriores requiere VIEW SERVER PERFORMANCE STATE
permiso en el servidor.
En los objetivos de servicio básico de Azure SQL Database, S0 y S1, y para las bases de datos de grupos elásticos, la cuenta de administrador del servidor, la cuenta de administrador de Microsoft Entra o la pertenencia al rol de servidor ##MS_ServerStateReader## es necesaria. En todos los demás objetivos de servicio de SQL Database, se requiere el VIEW DATABASE STATE
permiso en la base de datos o la pertenencia al rol de servidor ##MS_ServerStateReader#### .
Comentarios
sys.dm_exec_query_optimizer_info
contiene las siguientes propiedades (contadores). Todos los valores de repetición son acumulativos y se establecen 0
en al reiniciar el sistema. Todos los valores de los campos de valor se establecen NULL
en al reiniciar el sistema. Todos los valores de columnas de valor que especifican un promedio utilizan el valor de repetición de la misma fila que el denominador en el cálculo del promedio. Todas las optimizaciones de consulta se miden cuando SQL Server determina los cambios en dm_exec_query_optimizer_info
, incluidas las consultas generadas por el usuario y generadas por el sistema. La ejecución de un plan ya almacenado en caché no cambia los valores en dm_exec_query_optimizer_info
, solo las optimizaciones son significativas.
Contador | Repetición | Valor |
---|---|---|
optimizations |
Número total de optimizaciones. | No aplicable |
elapsed time |
Número total de optimizaciones. | Tiempo promedio transcurrido por optimización de instrucción individual (consulta), en segundos. |
final cost |
Número total de optimizaciones. | Costo promedio estimado para un plan optimizado en unidades de costo internas. |
trivial plan |
Solo para uso interno | Solo para uso interno |
tasks |
Solo para uso interno | Solo para uso interno |
no plan |
Solo para uso interno | Solo para uso interno |
search 0 |
Solo para uso interno | Solo para uso interno |
search 0 time |
Solo para uso interno | Solo para uso interno |
search 0 tasks |
Solo para uso interno | Solo para uso interno |
search 1 |
Solo para uso interno | Solo para uso interno |
search 1 time |
Solo para uso interno | Solo para uso interno |
search 1 tasks |
Solo para uso interno | Solo para uso interno |
search 2 |
Solo para uso interno | Solo para uso interno |
search 2 time |
Solo para uso interno | Solo para uso interno |
search 2 tasks |
Solo para uso interno | Solo para uso interno |
gain stage 0 to stage 1 |
Solo para uso interno | Solo para uso interno |
gain stage 1 to stage 2 |
Solo para uso interno | Solo para uso interno |
timeout |
Solo para uso interno | Solo para uso interno |
memory limit exceeded |
Solo para uso interno | Solo para uso interno |
insert stmt |
Número de optimizaciones que son para instrucciones INSERT . |
No aplicable |
delete stmt |
Número de optimizaciones que son para instrucciones DELETE . |
No aplicable |
update stmt |
Número de optimizaciones que son para instrucciones UPDATE . |
No aplicable |
contains subquery |
Número de optimizaciones de una consulta que contiene al menos una subconsulta. | No aplicable |
unnest failed |
Solo para uso interno | Solo para uso interno |
tables |
Número total de optimizaciones. | Número promedio de tablas referenciadas por consulta optimizada. |
hints |
Número de veces que se ha especificado alguna sugerencia. Las sugerencias con recuento incluyen: JOIN sugerencias de consulta , GROUP y UNION FORCE ORDER sugerencias de consulta, FORCE PLAN opción set y sugerencias de combinación. |
No aplicable |
order hint |
Número de veces en las que se obligó el orden de unión. Este contador no está restringido a la FORCE ORDER sugerencia. Al especificar un algoritmo de combinación dentro de una consulta, como , INNER HASH JOIN también se fuerza el orden de combinación, que incrementa el contador. |
No aplicable |
join hint |
Número de veces que una sugerencia de combinación ha forzado el algoritmo de combinación. La FORCE ORDER sugerencia de consulta no incrementa este contador. |
No aplicable |
view reference |
Número de veces que se hace referencia a una vista en una consulta. | No aplicable |
remote query |
Número de optimizaciones en las que la consulta hace referencia al menos a un origen de datos remoto, como una tabla con un nombre de cuatro partes o un OPENROWSET resultado. |
No aplicable |
maximum DOP |
Número total de optimizaciones. | Valor medio efectivo MAXDOP para un plan optimizado. De forma predeterminada, la eficacia MAXDOP viene determinada por la opción de configuración del servidor grado máximo de paralelismo y puede reemplazarse para una consulta específica por el valor de la MAXDOP sugerencia de consulta. |
maximum recursion level |
Número de optimizaciones en las que se especificó un MAXRECURSION nivel mayor que 0 con la sugerencia de consulta. |
Nivel medio MAXRECURSION en optimizaciones donde se especificó un nivel máximo de recursividad con la sugerencia de consulta. |
indexed views loaded |
Solo para uso interno | Solo para uso interno |
indexed views matched |
Número de optimizaciones en las que se coinciden una o varias vistas indizadas. | Número promedio de vistas coincidentes. |
indexed views used |
Número de optimizaciones donde se han utilizado una o más vistas indizadas en el plan de salida después de la coincidencia. | Número promedio de vistas utilizadas. |
indexed views updated |
Número de optimizaciones de una instrucción DML que producen un plan que mantiene una o más vistas indizadas. | Número promedio de vistas mantenidas. |
dynamic cursor request |
Número de optimizaciones en las que se especificó una solicitud de cursor dinámico. | No aplicable |
fast forward cursor request |
Número de optimizaciones en las que se especificó una solicitud de cursor de avance rápido. | No aplicable |
merge stmt |
Número de optimizaciones que son para instrucciones MERGE . |
No aplicable |
Ejemplos
A Visualización de estadísticas sobre la ejecución del optimizador
¿Cuáles son las estadísticas actuales de ejecución del optimizador para esta instancia de SQL Server?
SELECT * FROM sys.dm_exec_query_optimizer_info;
B. Visualización del número total de optimizaciones
¿Cuántas optimizaciones se realizan?
SELECT occurrence AS Optimizations
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';
C. Tiempo medio transcurrido por optimización
¿Cuál es el tiempo promedio transcurrido por optimización?
SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'elapsed time';
D. Parte de las optimizaciones que afectan a subconsultas
¿Qué fracción de las consultas optimizadas contenían una subconsulta?
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. Visualización del número total de sugerencias durante la optimización
¿Cuántas sugerencias se cuentan cuando FORCE ORDER
se incluye como sugerencia de consulta?
-- 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'
);