sys.dm_db_missing_index_details (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Devuelve información detallada sobre los índices que faltan.

En Azure SQL Database, las vistas de administración dinámica no pueden mostrar información que afecte a la contención de la base de datos o que exponga datos acerca de otras bases de datos a las que el usuario tenga acceso. Para evitar exponer esta información, se filtran todas las filas que contienen datos que no pertenecen al inquilino conectado.

Nombre de la columna Tipo de datos Descripción
index_handle int Identifica un índice que falta específico. El identificador es único en todo el servidor. index_handle es la clave de esta tabla.
database_id smallint Identifica la base de datos en la que reside la tabla en la que falta un índice.

En Azure SQL Database, los valores son únicos dentro de una base de datos única o un grupo elástico, pero no dentro de un servidor lógico.
object_id int Identifica la tabla en la que falta el índice.
equality_columns nvarchar(4000) Lista de columnas separadas por comas que contribuyen a predicados de igualdad de la forma:

constant_value table.column =
inequality_columns nvarchar(4000) Lista de columnas separadas por comas que contribuyen a predicados de desigualdad; por ejemplo, a predicados de la forma:

constant_value table.column>

Cualquier operador de comparación distinto de "=" expresa desigualdad.
included_columns nvarchar(4000) Lista de columnas de cobertura separadas por comas requeridas por la consulta. Para obtener más información sobre cómo cubrir o incluir columnas, vea Crear índices con columnas incluidas.

En el caso de los índices optimizados para memoria (hash y optimizados para memoria no agrupados), omita included_columns. Todas las columnas de la tabla se incluyen en cada índice optimizado para memoria.
instrucción nvarchar(4000) Nombre de la tabla en la que falta el índice.

Comentarios

La información devuelta por sys.dm_db_missing_index_details se actualiza cuando el optimizador de consultas optimiza una consulta y no se conserva. La información de índice que falta solo se mantiene hasta que se reinicia el motor de base de datos. Los administradores de bases de datos deben realizar copias de seguridad de forma periódica de la información de índices que faltan si desean conservarla después de reciclar el servidor. Use la columna sqlserver_start_time en sys.dm_os_sys_info para encontrar la hora del último inicio del motor de base de datos.

Para determinar de qué grupos de índices que faltan, un índice que falta en particular forma parte, puede consultar la vista de administración dinámica mediante la sys.dm_db_missing_index_groups equiinación con sys.dm_db_missing_index_details en función de la index_handle columna.

Nota:

El conjunto de resultados de esta DMV está limitado a 600 filas. Cada fila contiene un índice que falta. Si tiene más de 600 índices que faltan, debe abordar los índices que faltan para que pueda ver los más recientes.

Uso de información de índice que falta en instrucciones CREATE INDEX

Para convertir la información devuelta por sys.dm_db_missing_index_details en una instrucción CREATE INDEX para índices optimizados para memoria y basados en disco, las columnas de igualdad deben colocarse antes de las columnas de desigualdad y juntas deben crear la clave del índice. Las columnas incluidas deben agregarse a la instrucción CREATE INDEX mediante la cláusula INCLUDE. Para determinar un orden efectivo para las columnas de igualdad, ordénelas en función de su selectividad, mostrando primero las columnas más selectivas (en la parte izquierda de la lista de columnas). Obtenga más información en Optimización de índices no clúster con sugerencias de índice que faltan, incluidas las limitaciones de la característica de índice que falta.

Para obtener más información sobre los índices optimizados para memoria, vea Índices para tablas optimizadas para memoria.

Coherencia de transacciones

Si una transacción crea o quita una tabla, las filas que contienen información de índices que faltan sobre los objetos quitados se eliminan de este objeto de administración dinámica para mantener la coherencia de la transacción. Obtenga más información sobre las limitaciones de la característica de índice que falta.

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.

Ejemplos

En el ejemplo siguiente se devuelven sugerencias de índice que faltan para la base de datos actual. Las sugerencias de índice que faltan deben combinarse siempre que sea posible entre sí y con índices existentes en la base de datos actual. Aprenda a aplicar estas sugerencias en optimización de índices no clúster con sugerencias de índice que faltan.

SELECT
  CONVERT (varchar(30), getdate(), 126) AS runtime,  mig.index_group_handle,  mid.index_handle,
  CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns, '') + CASE
    WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ','
    ELSE ''
  END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Nota:

El script de creación de índices del cuadro de herramientas Tiger de Microsoft examina las DMV de índices que faltan y quita automáticamente los índices sugeridos redundantes, analiza los índices de bajo impacto y genera scripts de creación de índices para su revisión. Como en la consulta anterior, NO ejecuta comandos de creación de índices. El script de creación de índices es adecuado para SQL Server y Azure SQL Managed Instance. Para Azure SQL Database, considere la posibilidad de implementar el ajuste automático de índices.

Pasos siguientes

Obtenga más información sobre la característica de índice que falta en los siguientes artículos: