sql | SQL‎ > ‎sql | Downloads‎ > ‎sql | Notes‎ > ‎

note.sqlsrv | index.missing_index

 
An easier way to determine which indexes may be needed:
 
•sys.dm_db_missing_index_details -
    Returns detailed information about a missing index
•sys.dm_db_missing_index_group_stats  -
    Returns summary information about missing index groups
•sys.dm_db_missing_index_groups -
    Returns information about a specific group of missing indexes
•sys.dm_db_missing_index_columns(index_handle) -
    Returns information about the database table columns that are missing for an index.
    This is a function and requires the index_handle to be passed.
 
==>Once the instance of SQL Server is restarted the data gets cleared out.
 
How often this query is being called
SELECT * FROM sys.dm_db_missing_index_group_stats
 
Information about the index_group_handle and the index_handle
SELECT * FROM sys.dm_db_missing_index_groups
 
SELECT
    mig.*,
    statement AS table_name,
    column_id,
    column_name,
    column_usage
FROM
    sys.dm_db_missing_index_details AS mid
CROSS APPLY
    sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN
    sys.dm_db_missing_index_groups AS mig
        ON
        mig.index_handle = mid.index_handle
ORDER BY
    mig.index_group_handle,
    mig.index_handle, column_id;
 
When you add or drop indexes on a table all stats for missing indexes get cleared for this table.
 
May not be an optimal approach for managing your indexes.
 
 
Deeper insight into unused indexes for SQL Server
 
Determine which indexes are being used and how they are being used:
 
sys.dm_db_index_usage_stats
Keeps track of each index that has been used and how it has been used.
 
EXAMPLE.
 
SELECT  
    A.NAME,     
    B.NAME, 
    C.KEY_ORDINAL,    
    D.NAME
FROM    
    SYS.OBJECTS A 
        INNER JOIN SYS.INDEXES B     
            ON A.OBJECT_ID = B.OBJECT_ID
        INNER JOIN SYS.INDEX_COLUMNS C
            ON B.OBJECT_ID = C.OBJECT_ID
            AND B.INDEX_ID = C.INDEX_ID     
        INNER JOIN SYS.COLUMNS D 
            ON C.OBJECT_ID = D.OBJECT_ID 
            AND C.COLUMN_ID = D.COLUMN_ID
WHERE   
    A.TYPE <> 'S'
ORDER BY 1, 2, 3
 
EXAMPLE. 
 
SELECT
    PVT.TABLENAME,
    PVT.INDEXNAME,
    PVT.INDEX_ID,
    [1] AS COL1,
    [2] AS COL2,
    [3] AS COL3, 
    [4] AS COL4, 
    [5] AS COL5,
    [6] AS COL6,
    [7] AS COL7,
    B.USER_SEEKS, 
    B.USER_SCANS,
    B.USER_LOOKUPS
FROM
    (SELECT
        A.NAME AS TABLENAME,
        A.OBJECT_ID, 
        B.NAME AS INDEXNAME, 
        B.INDEX_ID, 
        D.NAME AS COLUMNNAME, 
        C.KEY_ORDINAL 
    FROM  
        SYS.OBJECTS A 
            INNER JOIN SYS.INDEXES B 
                ON A.OBJECT_ID = B.OBJECT_ID 
            INNER JOIN SYS.INDEX_COLUMNS C 
                AND B.INDEX_ID = C.INDEX_ID 
            INNER JOIN SYS.COLUMNS D 
                ON C.OBJECT_ID = D.OBJECT_ID 
                AND C.COLUMN_ID = D.COLUMN_ID 
    WHERE  A.TYPE <> 'S'
    ) P 
    PIVOT (
        MIN(COLUMNNAME) 
        FOR KEY_ORDINAL
        IN ( [1],[2],[3],[4],[5],[6],[7] )
    ) AS PVT 
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B 
    ON PVT.OBJECT_ID = B.OBJECT_ID 
    AND PVT.INDEX_ID = B.INDEX_ID 
    AND B.DATABASE_ID = DB_ID()
UNION 
SELECT
    TABLENAME,
    INDEXNAME,
    INDEX_ID,
    [1] AS COL1,
    [2] AS COL2,
    [3] AS COL3, 
    [4] AS COL4,
    [5] AS COL5,
    [6] AS COL6,
    [7] AS COL7,
    0, 0, 0
FROM   (
    SELECT
        A.NAME AS TABLENAME, 
        A.OBJECT_ID, 
        B.NAME AS INDEXNAME, 
        B.INDEX_ID, 
        D.NAME AS COLUMNNAME, 
        C.KEY_ORDINAL 
    FROM  SYS.OBJECTS A 
        INNER JOIN SYS.INDEXES B 
            ON A.OBJECT_ID = B.OBJECT_ID 
        INNER JOIN SYS.INDEX_COLUMNS C 
            ON B.OBJECT_ID = C.OBJECT_ID 
            AND B.INDEX_ID = C.INDEX_ID 
        INNER JOIN SYS.COLUMNS D 
            ON C.OBJECT_ID = D.OBJECT_ID 
            AND C.COLUMN_ID = D.COLUMN_ID 
    WHERE  A.TYPE <> 'S') P 
    PIVOT (
        MIN(COLUMNNAME)
        FOR KEY_ORDINAL
        IN ( [1],[2],[3],[4],[5],[6],[7] )
    ) AS PVT
WHERE  NOT EXISTS (
    SELECT
        OBJECT_ID, 
        INDEX_ID 
    FROM   SYS.DM_DB_INDEX_USAGE_STATS B 
    WHERE 
        DATABASE_ID = DB_ID(DB_NAME()) 
        AND PVT.OBJECT_ID = B.OBJECT_ID 
        AND PVT.INDEX_ID = B.INDEX_ID)
ORDER BY TABLENAME, INDEX_ID;  
 
 
 
 
Identify Missing Indexes
 
select   d.* 
        ,s.avg_total_user_cost 
        ,s.avg_user_impact 
        ,s.last_user_seek 
        ,s.unique_compiles 
from     sys.dm_db_missing_index_group_stats s 
        ,sys.dm_db_missing_index_groups g 
        ,sys.dm_db_missing_index_details d 
where    s.group_handle = g.index_group_handle 
         and d.index_handle = g.index_handle 
order by s.avg_user_impact desc 
go 
--- suggested index columns & usage 
declare @handle int 
 
select  @handle = d.index_handle 
from     sys.dm_db_missing_index_group_stats s 
        ,sys.dm_db_missing_index_groups g 
        ,sys.dm_db_missing_index_details d 
where    s.group_handle = g.index_group_handle 
	 and d.index_handle = g.index_handle 
 
select  *  
from     sys.dm_db_missing_index_columns(@handle) 
order by column_id 
Comments