Identificare indici mancanti o non utilizzati

[FrancyDotNet]

Indici mancanti

Codice: Seleziona tutto

SELECT
    OBJECT_NAME(s.[object_id]) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    dm_ius.user_seeks,
    dm_ius.user_scans,
    dm_ius.user_lookups,
    dm_ius.user_updates
FROM
    sys.indexes AS i
INNER JOIN
    sys.objects AS s ON i.[object_id] = s.[object_id]
LEFT JOIN
    sys.dm_db_index_usage_stats AS dm_ius ON i.[object_id] = dm_ius.[object_id]
                                          AND i.index_id = dm_ius.index_id
                                          AND dm_ius.database_id = DB_ID()
WHERE
    s.[type] = 'U'
    AND s.is_ms_shipped = 0
    AND i.type_desc = 'NONCLUSTERED'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) = 0
ORDER BY
    TableName, IndexName;

Indici non utilizzati

Codice: Seleziona tutto

SELECT
    OBJECT_NAME(s.[object_id]) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    dm_ius.user_seeks,
    dm_ius.user_scans,
    dm_ius.user_lookups,
    dm_ius.user_updates
FROM
    sys.indexes AS i
INNER JOIN
    sys.objects AS s ON i.[object_id] = s.[object_id]
LEFT JOIN
    sys.dm_db_index_usage_stats AS dm_ius ON i.[object_id] = dm_ius.[object_id]
                                          AND i.index_id = dm_ius.index_id
                                          AND dm_ius.database_id = DB_ID()
WHERE
    s.[type] = 'U'
    AND s.is_ms_shipped = 0
    AND i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
    AND (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) = 0
ORDER BY
    TableName, IndexName;