Technical Article

SQL Server Missing Index

,

We will use this script to find missing indexes in our database.

Yusuf KAHVECI

yusufkahveci@sqlturkiye.com

www.sqlturkiye.com

Thanks.

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;

Read 479 times
(5 in last 30 days)

Rate

3.2 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.2 (5)

You rated this post out of 5. Change rating