A volte è possibile che sql server perda fisicamente degli indici durante le operazioni di Rebuild, cioè ne esiste la definizione sulla tabella ma manca effettivamente l'indice su sql.
é possibile identificare le tabelle che hanno perso l'indice:
USE [DB]
select * from sys.dm_db_missing_index_details
altre opzioni esistenti:
| sys.dm_db_missing_index_group_stats |
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes. |
| sys.dm_db_missing_index_groups |
Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group. |
| sys.dm_db_missing_index_details |
Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index. |
| sys.dm_db_missing_index_columns |
Returns information about the database table columns that are missing an index. |