sys.dm_db_missing_index_details (Transact-SQL)

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

Restituisce informazioni dettagliate sugli indici mancanti.

Nel database SQL di Azure, le viste a gestione dinamica non possono esporre le informazioni che influenzerebbero l'indipendenza del database o le informazioni sugli altri database a cui l'utente dispone di accesso. Per evitare di esporre queste informazioni, ogni riga contenente dati che non appartengono al tenant connesso viene filtrata.

Nome colonna Tipo di dati Descrizione
index_handle int Identifica un determinato indice mancante. L'identificatore è univoco nel server. index_handle è la chiave di questa tabella.
database_id smallint Identifica il database in cui è archiviata la tabella con l'indice mancante.

In database SQL di Azure i valori sono univoci all'interno di un database singolo o di un pool elastico, ma non all'interno di un server logico.
object_id int Identifica la tabella in cui l'indice risulta mancante.
equality_columns nvarchar(4000) Elenco delimitato da virgole delle colonne che contribuiscono ai predicati di uguaglianza nel formato seguente:

constant_value table.column =
inequality_columns nvarchar(4000) Elenco delimitato da virgole delle colonne che contribuiscono ai predicati di disuguaglianza, ad esempio predicati nel formato seguente:

constant_value table.column>

Qualsiasi operatore di confronto diverso da "=" esprime disuguaglianza.
included_columns nvarchar(4000) Elenco delimitato da virgole delle colonne necessarie come colonne di copertura per la query. Per altre informazioni sulla copertura o sulle colonne incluse, vedere Creare indici con colonne incluse.

Per gli indici ottimizzati per la memoria (sia hash che non cluster ottimizzati per la memoria), ignorare included_columns. Tutte le colonne della tabella vengono incluse in ogni indice ottimizzato per la memoria.
statement nvarchar(4000) Nome della tabella in cui l'indice risulta mancante.

Osservazioni:

Le informazioni restituite da sys.dm_db_missing_index_details vengono aggiornate quando una query è ottimizzata da Query Optimizer e non è persistente. Le informazioni sull'indice mancanti vengono mantenute solo fino al riavvio del motore di database. Per mantenere tali informazioni anche dopo il riciclo del server, gli amministratori di database devono eseguirne periodicamente copie di backup. Usare la colonna sqlserver_start_time in sys.dm_os_sys_info per trovare l'ora di avvio dell'ultimo motore di database.

Per determinare quali gruppi di indici mancanti fanno parte di un particolare indice mancante, è possibile eseguire una query sulla sys.dm_db_missing_index_groups vista a gestione dinamica equijoining con sys.dm_db_missing_index_details in base alla index_handle colonna.

Nota

Il set di risultati per questa DMV è limitato a 600 righe. Ogni riga contiene un indice mancante. Se sono presenti più di 600 indici mancanti, è necessario risolvere gli indici mancanti esistenti in modo da poter visualizzare quelli più recenti.

Uso delle informazioni sull'indice mancanti nelle istruzioni CREATE INDEX

Per convertire le informazioni restituite da sys.dm_db_missing_index_details in un'istruzione CREATE INDEX per indici ottimizzati per la memoria e basati su disco, le colonne di uguaglianza devono essere inserite prima delle colonne di disuguaglianza e insieme devono rendere la chiave dell'indice. Aggiungere le colonne incluse all'istruzione CREATE INDEX mediante la clausola INCLUDE. Per determinare un ordine efficiente per le colonne di uguaglianza, ordinarle in base alla selettività a partire dalle colonne più selettive, all'estrema sinistra nell'elenco di colonne. Per altre informazioni, vedere Ottimizzare gli indici non cluster con suggerimenti per l'indice mancanti, incluse le limitazioni della funzionalità di indice mancante.

Per altre informazioni sugli indici ottimizzati per la memoria, vedere Indici per tabelle ottimizzate per la memoria.

Coerenza delle transazioni

Se in una transazione viene creata o eliminata una tabella, le righe contenenti le informazioni sugli indici mancanti per gli oggetti eliminati vengono rimosse da questo oggetto a gestione dinamica, mantenendo la consistenza delle transazioni. Altre informazioni sulle limitazioni della funzionalità di indice mancante.

Autorizzazioni

In SQL Server e Istanza gestita di SQL è richiesta l'autorizzazione VIEW SERVER STATE.

In database SQL obiettivi di servizio Basic, S0 e S1 e per i database nei pool elastici, è necessario l'account amministratore del server, l'account amministratore di Microsoft Entra o l'appartenenza al ruolo del ##MS_ServerStateReader## server. Per tutti gli altri obiettivi di servizio database SQL, è necessaria l'autorizzazione VIEW DATABASE STATE per il database o l'adesione ruolo del server ##MS_ServerStateReader##.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Esempi

Nell'esempio seguente vengono restituiti suggerimenti di indice mancanti per il database corrente. I suggerimenti per l'indice mancanti devono essere combinati tra loro e con gli indici esistenti nel database corrente. Informazioni su come applicare questi suggerimenti per ottimizzare gli indici non cluster con suggerimenti per l'indice mancanti.

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

Lo script di creazione di indici nella casella degli strumenti Tiger di Microsoft esamina le DMV mancanti sugli indici e rimuove automaticamente gli indici suggeriti ridondanti, analizza gli indici a basso impatto e genera script di creazione degli indici per la revisione. Come nella query precedente, NON esegue i comandi di creazione dell'indice. Lo script di creazione dell'indice è adatto per SQL Server e l'istanza gestita di SQL di Azure. Per database SQL di Azure, è consigliabile implementare l'ottimizzazione automatica degli indici.

Passaggi successivi

Altre informazioni sulla funzionalità di indice mancante sono disponibili negli articoli seguenti: