Unused/Missing indexes

---

-- unused indexes
SELECT  OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
        OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
        I.NAME AS IndexName    
FROM    sys.indexes I   
WHERE   -- only get indexes for user created tables
        OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 
        -- find all indexes that exists but are NOT used
        AND NOT EXISTS ( 
                    SELECT  index_id 
                    FROM    sys.dm_db_index_usage_stats
                    WHERE   OBJECT_ID = I.OBJECT_ID 
                            AND I.index_id = index_id 
                            -- limit our query only for the current db
                            AND database_id = DB_ID()) 
  AND I.index_id != 0
  AND I.is_primary_key = 0
  AND I.is_unique_constraint = 0  
  -- AND I.is_unique = 0                            
ORDER BY SchemaName, ObjectName, IndexName 

---

-- rarely used indexes
SELECT
    O.name AS object_name, 
    I.name AS index_name, 
    I.index_id AS index_id, 
    DM_IUS.user_seeks AS user_seeks, 
    DM_IUS.user_scans AS user_scans, 
    DM_IUS.user_lookups AS user_lookups, 
    DM_IUS.user_updates AS user_updates, 
    P.table_rows as table_rows,
    DM_IUS.last_user_seek,
    DM_IUS.last_user_scan,
    DM_IUS.last_user_lookup,
    DM_IUS.last_user_update 
FROM sys.dm_db_index_usage_stats DM_IUS
INNER JOIN sys.indexes I
    ON I.index_id = DM_IUS.index_id AND 
       DM_IUS.OBJECT_ID = I.OBJECT_ID
INNER JOIN sys.objects O 
    ON DM_IUS.OBJECT_ID = O.OBJECT_ID
INNER JOIN sys.schemas S
    ON O.schema_id = S.schema_id
INNER JOIN (SELECT SUM(P.rows) table_rows, P.index_id, P.OBJECT_ID FROM sys.partitions P 
            GROUP BY P.index_id, P.OBJECT_ID) P
    ON P.index_id = DM_IUS.index_id 
       AND DM_IUS.OBJECT_ID = P.OBJECT_ID
WHERE OBJECTPROPERTY(DM_IUS.OBJECT_ID,'IsUserTable') = 1
    AND DM_IUS.database_id = DB_ID()
    AND I.type_desc = 'nonclustered'
    AND I.is_primary_key = 0
    AND I.is_unique_constraint = 0
ORDER BY (DM_IUS.user_seeks + DM_IUS.user_scans + DM_IUS.user_lookups) ASC

---

-- used indexes stats
SELECT    
    DB_NAME(C.database_id) AS database_name,
    OBJECT_NAME(C.object_id, C.database_id) AS table_name,
    A.avg_user_impact * (A.user_seeks + A.user_scans) avg_estimated_impact,
    A.avg_total_user_cost * (A.user_seeks + A.user_scans) avg_estimated_total_user_cost,
    A.unique_compiles, 
    A.user_seeks,
    A.last_user_seek,
    DATEPART(d, GETDATE() - A.last_user_seek) as last_user_seek_days_ago,
    A.user_scans,
    A.last_user_scan,
    DATEPART(d, GETDATE() - A.last_user_scan) as last_user_scan_days_ago,
    A.avg_total_user_cost,
    A.avg_user_impact,
    C.equality_columns,
    C.inequality_columns,
    C.included_columns  
FROM sys.dm_db_missing_index_group_stats AS A
INNER JOIN sys.dm_db_missing_index_groups AS B
    ON A.group_handle = B.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS C
    ON B.index_handle = C.index_handle
--WHERE  
    --DATEPART(hh, last_user_seek) >= 6 AND DATEPART(hh, last_user_seek) <= 20 AND    -- run in work hours
    --DATEPART(d, GETDATE() - A.last_user_seek) = 1          -- run today
ORDER BY avg_estimated_total_user_cost DESC

---