Finding Missing Indexes

The Below script will brings the Missing Indexes in the databases

-- ===================================================
-- Author:  V.U.M.Sastry Sagi
-- Create date: 01/30/2011
-- Description: Fetches the Missing Indexes
-- ===================================================

CREATE PROCEDURE [DBO].[FINDMISSINGINDEXES] AS   
    BEGIN  
    
    SELECT  MID.STATEMENT,  
            MIGS.AVG_TOTAL_USER_COST * ( MIGS.AVG_USER_IMPACT / 100.0 )  
            * ( MIGS.USER_SEEKS + MIGS.USER_SCANS ) AS IMPROVEMENT_MEASURE,  
            OBJECT_NAME(MID.OBJECT_ID),  
            'CREATE INDEX [MISSING_INDEX_'  
            + CONVERT (VARCHAR, MIG.INDEX_GROUP_HANDLE) + '_'  
            + CONVERT (VARCHAR, MID.INDEX_HANDLE) + '_'  
            + LEFT(PARSENAME(MID.STATEMENT, 1), 32) + ']' + ' 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   MIGS.AVG_TOTAL_USER_COST * ( MIGS.AVG_USER_IMPACT / 100.0 )  
            * ( 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   
END
2 Comments