数据库缺失索引查询

SQL：

SELECT TOP 100
        statement AS 表 ,
        equality_columns AS 相等列 ,
        inequality_columns AS 不相等列 ,
        included_columns AS 包含列 ,
        user_scans + user_seeks AS 总查询次数 ,
        avg_user_impact AS 平均百分比收益 ,
        avg_total_user_cost AS 平均成本 ,
        avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS 可能改进 ,
        --obj.name AS TableName,
        'CREATE INDEX [IX_' + obj.name + '_'
        + CONVERT(VARCHAR(32), GS.group_handle) + '_'
        + CONVERT(VARCHAR(32), D.index_handle) + ']' + ' ON ' + [statement]
        + ' (' + ISNULL(equality_columns, '')
        + CASE WHEN equality_columns IS NOT NULL
                    AND inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
                                                              + included_columns
                                                              + ')', '') AS Create_Index_Syntax
FROM    sys.dm_db_missing_index_details AS D
        INNER JOIN sys.dm_db_missing_index_groups G ON G.index_handle = D.index_handle
        INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
        INNER JOIN sys.objects AS obj ON obj.object_id = OBJECT_ID([statement])
                                         AND obj.type = 'U'
WHERE   1 = 1
        AND D.database_id = DB_ID()
ORDER BY 可能改进 DESC;

转载于:https://www.cnblogs.com/efenghuo/articles/6945426.html