Wednesday, April 13, 2016

Missing indexes -- revised script

Thanks to Darren Myher (https://DarrenMyher.com) for a few intelligent tweaks as well as some useful formatting for this revised version.

--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.
--           see later posting on this blog for detail
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

No comments:

Post a Comment