Compartir a través de


sys.dm_exec_query_optimizer_info (Transact-SQL)

Devuelve información estadística detallada acerca de la operación 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 sólo afectan al uso del diagnóstico interno de SQL Server. Estos contadores se marcan como "Sólo para uso interno".

Nombre

Tipo de datos

Descripción

counter

nvarchar(8000)

Nombre del evento de estadísticas del optimizador.

repetición

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.

Permisos

Requiere el permiso VIEW SERVER STATE en el servidor.

Notas

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 consultas se miden cuando SQL Server determina los cambios de dm_exec_query_optimizer_info, incluidas las consultas generadas por el sistema y las generadas por el usuario. La ejecución de un plan almacenado en caché no cambia valores en dm_exec_query_optimizer_info, sólo las optimizaciones son relevantes.

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

Sólo para uso interno

Sólo para uso interno

tasks

Sólo para uso interno

Sólo para uso interno

no plan

Sólo para uso interno

Sólo para uso interno

search 0

Sólo para uso interno

Sólo para uso interno

search 0 time

Sólo para uso interno

Sólo para uso interno

search 0 tasks

Sólo para uso interno

Sólo para uso interno

search 1

Sólo para uso interno

Sólo para uso interno

search 1 time

Sólo para uso interno

Sólo para uso interno

search 1 tasks

Sólo para uso interno

Sólo para uso interno

search 2

Sólo para uso interno

Sólo para uso interno

search 2 time

Sólo para uso interno

Sólo para uso interno

search 2 tasks

Sólo para uso interno

Sólo para uso interno

gain stage 0 to stage 1

Sólo para uso interno

Sólo para uso interno

gain stage 1 to stage 2

Sólo para uso interno

Sólo para uso interno

tiempo de espera

Sólo para uso interno

Sólo para uso interno

memory limit exceeded

Sólo para uso interno

Sólo 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

Sólo para uso interno

Sólo 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 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 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, el valor MAXDOP efectivo se determina mediante la opción de configuración del servidor Grado de paralelismo máximo y se puede reemplazar en 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

Sólo para uso interno

Sólo 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 de ejecución del optimizador actual 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 promedio 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;