在SQLSERVER中查找缺失和无用的索引SQL

--缺失的索引
SELECT  avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement , 
        last_user_seek , 
        last_user_scan , 
        [statement] AS [Object] , 
        'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_' 
        + CONVERT(VARCHAR(32), D.index_handle) + '_' 
        + REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '') 
        + ']' + ' 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_groups AS G 
        INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle 
        INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle 
ORDER BY statement,PossibleImprovement desc


--无用的索引
SELECT  ind.index_id , 
        obj.name AS TableName , 
        ind.name AS IndexName , 
        ind.type_desc , 
        indUsage.user_seeks , 
        indUsage.user_scans , 
        indUsage.user_lookups , 
        indUsage.user_updates , 
        indUsage.last_system_seek , 
        indUsage.last_user_scan , 
        'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand 
FROM    sys.indexes AS ind 
        INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id 
        LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id 
                                                          AND ind.index_id = indUsage.index_id 
WHERE   ind.type_desc <> 'HEAP' 
        AND obj.type <> 'S' 
        AND OBJECTPROPERTY(obj.object_id, 'isusertable') = 1 
        AND ( ISNULL(indUsage.user_seeks, 0) = 0 
              AND ISNULL(indUsage.user_scans, 0) = 0 
              AND ISNULL(indUsage.user_lookups, 0) = 0 
            ) 
ORDER BY obj.name , 
        ind.name