查询索引缺失和无效索引

查询索引缺失：

SELECT
    avg_total_user_cost,
    avg_user_impact,
    migs.group_handle,
    mid.*
FROM
    sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON (
    migs.group_handle = mig.index_group_handle
)
INNER JOIN sys.dm_db_missing_index_details AS mid ON (
    mig.index_handle = mid.index_handle
)

查询索引缺失并创建索引

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

查询失效索引

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