Darren Myher's Blog

Because different customers use software in different ways, not every customer will benefit from exactly the same database-level indexes.

As a best practice, when users complain about database performance in a specific area of the application, or when performance tuning in general, I like to let the customer run with the application for a few days, and then use a query such as the one included below (adapted from Jeff Garbus at Soaring Eagle: https://mssqlperformance.blogspot.com):

–Source: Jeff Garbus from Soaring Eagle – Blog: https://mssqlperformance.blogspot.com/
–DRM – 2020-05-04 – Adapted by Darren to deal with mixed SQL Environments, include table name in index name, deal with table names containing spaces, etc.
–Use this SQL Script to identify indexes that if added could help improve performance.
–IMPORTANT: Do not BLINDLY implement the script recommendations.  For example, if the SAME TABLE is mentioned multiple times, then
–           it may be better to COMBINE the recommendations of multiple statements into a single one.
–           If you need help, Soaring Eagle. 
DECLARE @Edition varchar(50)
SET @Edition = CONVERT(varchar(50), SERVERPROPERTY(‘Edition’))
DECLARE @bEnterpriseEdition bit
IF (LEFT(@Edition, 9) = ‘Enterpris’ OR LEFT(@Edition, 9) = ‘Developer’)
SET @bEnterpriseEdition = 1

SELECT
      ‘CREATE INDEX IX_’
            + REPLACE(obj.Name, ‘ ‘, ”) +’_’
            + replace(replace(replace (equality_columns, ‘[‘, ”),’]’,”),’, ‘,’_’)
            + ‘ ON ‘
            + sch.name COLLATE SQL_Latin1_General_CP1_CI_AS + ‘.’ + QuoteName(obj.name)
            + ‘ (‘
            + equality_columns
             + CASE WHEN inequality_columns IS NULL
                   THEN ”
                   ELSE ‘,’ +  inequality_columns
               END
            + ‘)’
            + CASE WHEN included_columns IS NOT NULL
                   THEN ‘ INCLUDE (‘ + ISNULL(included_columns,”) + ‘) ‘
                   ELSE ”
               END
            + CASE WHEN @bEnterpriseEdition = 1
                   THEN ‘ WITH (online = ON)’
                    ELSE ”
              END
            + ‘ — ‘ + CONVERT (varchar, avg_user_impact) + ‘% anticipated impact’
      FROM sys.dm_db_missing_index_details mid
        JOIN sys.dm_db_missing_index_groups mig
            ON mid.index_handle = mig.index_handle
        JOIN sys.dm_db_missing_index_group_stats migs
             ON migs.group_handle = mig.index_group_handle
        JOIN sys.objects obj
            ON obj.object_id = mid.object_id
        JOIN sys.schemas sch
            ON obj.schema_id = sch.schema_id
      WHERE
        database_id = db_id()
        AND avg_user_impact > 60
        AND equality_columns IS NOT NULL
      ORDER BY
        obj.name
        , equality_columns
         , inequality_columns
        , included_columns

The output of this script should be reviewed carefully to see if the tables / recommendations make sense for the specific scenario.  Remember that there is a cost for implementing index changes that may outweigh the benefits.  The most common change that I make to the results from the above is to combine multiple recommendations for the same index into a single index with more elements in the INCLUDE() section.

For Blue Link employees reading this Blog, a version of the above is now available as IndexCreationRecommendationPROC().