sys.dm_exec_query_optimizer_info (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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
Contador	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	Se aplica a: Azure Synapse Analytics, Sistema de la plataforma de análisis (PDW) Identificador del nodo en el que se encuentra esta distribución.

Permisos

En SQL Server y SQL Managed Instance, requiere el permiso VIEW SERVER STATE.

En los objetivos de servicio de SQL Database Basic, S0 y S1, y para las bases de datos de grupos elásticos, se requiere la cuenta de administrador del servidor, la cuenta de administrador de Microsoft Entra o la pertenencia al rol de ##MS_ServerStateReader##servidor. En el resto de objetivos del servicio de SQL Database, se requiere el permiso VIEW DATABASE STATE en la base de datos o la pertenencia en el rol del servidor ##MS_ServerStateReader##.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VER ESTADO DE RENDIMIENTO DEL SERVIDOR en el servidor.

Comentarios

sys.dm_exec_query_optimizer_info contiene las siguientes propiedades (contadores). Todos los valores de repetición son acumulativos y se establecen en 0 cuando se reinicia el sistema. Todos los valores de los campos de valor se establecen en NULL cuando se reinicia 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 el sistema. La ejecución de un plan ya almacenado en caché no cambia los valores de 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
tareas	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
Tablas	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 contadas incluyen: sugerencias de consulta JOIN, GROUP, UNION y FORCE ORDER, la opción de conjunto FORCE PLAN y las sugerencias de combinación.	No aplicable
order hint	Número de veces que se ha especificado una sugerencia de orden forzada.	No aplicable
join hint	Número de veces que una sugerencia de combinación ha forzado el algoritmo de combinación.	No aplicable
view reference	Número de veces que se ha hecho referencia a una vista en una consulta	No aplicable
remote query	Número de optimizaciones donde la consulta hacía referencia al menos a un origen de datos remoto, como una tabla con un nombre de cuatro partes o una salida de OPENROWSET.	No aplicable
maximum DOP	Número total de optimizaciones.	Valor promedio de MAXDOP efectivo de un plan optimizado. De forma predeterminada, MAXDOP efectivo viene determinado 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 sugerencia de consulta MAXDOP.
maximum recursion level	Número de optimizaciones en las que se ha especificado un nivel de MAXRECURSION mayor que 0 con la sugerencia de consulta.	Nivel promedio de MAXRECURSION en optimizaciones donde se ha especificado 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 donde han coincidido una o más 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 ha especificado una solicitud de cursor dinámico.	No aplicable
fast forward cursor request	Número de optimizaciones en las que se ha especificado 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 Ver estadísticas de 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. Ver el 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;  

Consulte también

Funciones y vistas de administración dinámica (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con ejecuciones (Transact-SQL)