Sql Server Veritanında (Database) Kayıp Index leri tespit etme

SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [Index Advantage] ,

    migs.last_user_seek AS [Last User Seek],

    mid.[statement] AS [Database.Schema.Table] ,

    mid.equality_columns AS [Equality Columns] ,

    mid.inequality_columns AS [Inequality Columns] ,

    mid.included_columns AS [Included Columns] ,

    migs.unique_compiles AS [Unique Compiles],

    migs.user_seeks AS [User Seeks] ,

    migs.avg_total_user_cost AS [AVG Total User Cost] ,

    migs.avg_user_impact AS [AVG User Impact],

N'CREATE NONCLUSTERED INDEX [SQLTRINX_' + SUBSTRING(mid.statement,

                           CHARINDEX('.',

                               mid.statement,

                               CHARINDEX('.',

                               mid.statement)

                               + 1) + 2,

                           LEN(mid.statement) - 3

                           - CHARINDEX('.',

                               mid.statement,

                               CHARINDEX('.',

                               mid.statement)

                               + 1) + 1) + '_'

    + REPLACE(REPLACE(REPLACE(CASE WHEN mid.equality_columns IS NOT NULL

                      AND mid.inequality_columns IS NOT NULL

                      AND mid.included_columns IS NOT NULL

                    THEN mid.equality_columns + '_'

                      + mid.inequality_columns

                      + '_Includes'

                    WHEN mid.equality_columns IS NOT NULL

                      AND mid.inequality_columns IS NOT NULL

                      AND mid.included_columns IS NULL

                    THEN mid.equality_columns + '_'

                      + mid.inequality_columns

                    WHEN mid.equality_columns IS NOT NULL

                      AND mid.inequality_columns IS NULL

                      AND mid.included_columns IS NOT NULL

                    THEN mid.equality_columns + '_Includes'

                    WHEN mid.equality_columns IS NOT NULL

                      AND mid.inequality_columns IS NULL

                      AND mid.included_columns IS NULL

                    THEN mid.equality_columns

                    WHEN mid.equality_columns IS NULL

                      AND mid.inequality_columns IS NOT NULL

                      AND mid.included_columns IS NOT NULL

                    THEN mid.inequality_columns

                      + '_Includes'

                    WHEN mid.equality_columns IS NULL

                      AND mid.inequality_columns IS NOT NULL

                      AND mid.included_columns IS NULL

                    THEN mid.inequality_columns

                 END, ', ', '_'), ']', ''), '[', '') + '] '

    + N'ON ' + mid.[statement] + N' (' + ISNULL(mid.equality_columns, N'')

    + CASE WHEN mid.equality_columns IS NULL

        THEN ISNULL(mid.inequality_columns, N'')

        ELSE ISNULL(', ' + mid.inequality_columns, N'')

     END + N') ' + ISNULL(N'INCLUDE (' + mid.included_columns + N');',

                ';') AS CreateStatement

FROM  sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )

    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle

WHERE  mid.database_id = DB_ID()

ORDER BY [Index Advantage] DESC