索引缺失和索引碎片

 

 

SELECT
       [database_name]=db.[name]
, [table_name] = REVERSE(SUBSTRING(REVERSE(statement), 2, (CHARINDEX('[', REVERSE(statement), 2)) - 2))
, [index_creation_statement]='CREATE NONCLUSTERED INDEX [MI_'+ CONVERT (VARCHAR, g.Index_Group_Handle) + '_'+ CONVERT (VARCHAR, g.Index_Handle) + '_' + LEFT(PARSENAME(REVERSE(SUBSTRING(REVERSE(statement), 2, (CHARINDEX('[', REVERSE(statement), 2)) - 2)), 1), 32) + ']' + ' ON ['+db.[name]+'].[dbo].['+REVERSE(SUBSTRING(REVERSE(statement), 2, (CHARINDEX('[', REVERSE(statement), 2)) - 2)) + '] (' + 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 + ')', '')
, [improvement_measure]=s.avg_total_user_cost * (avg_user_impact / 100.0) * s.user_seeks
, [total_cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, [avg_total_user_cost] =avg_total_user_cost  -- 表示缺失索引被建立后，平均会降低的成本数，这个数据越大，表示索引创建之后，效果越好
, [avg_user_impact]=avg_user_impact       --  表示缺失索引创建以后，平均会降低成本的百分比
, [user_seeks]=s.user_seeks        --  表示从服务器开启到现在，缺失索引可以被用户的语句用于 Seek 操作的次数
, [equality_columns] = equality_columns   --  表示等于操作的字段
, [inequality_columns] = inequality_columns      --  表示非等于操作的字段
, [included_columns] = included_columns   --  表示建立使用 include 关键字添加在索引 叶子节点的字段
, [index_group_handle]=g.[index_group_handle]
, [index_handle]=g.[index_handle]
FROM sys.dm_db_missing_index_groups g
       INNER JOIN sys.dm_db_missing_index_group_stats s
       ON s.group_handle = g.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details d
       ON d.index_handle = g.index_handle
       INNER join sys.databases db
       ON db.database_id=d.database_id
ORDER BY [avg_total_user_cost] DESC;

 

-- 获取数据库索引碎片率大于10% 的索引信息
DECLARE @DB_NAME NVARCHAR(100)=(Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid))
 
SELECT i.[name] AS IndexName,
       o.name AS TableName,
       sch.name AS SchemaName,
       s.avg_fragmentation_in_percent,
       'ALTER INDEX [' + i.[name] + '] ON [' + @DB_NAME + '].[' + sch.name + '].[' + o.name
       + '] REBUILD WITH(FILLFACTOR=80, SORT_IN_TEMPDB=ON ,STATISTICS_NORECOMPUTE = ON,ONLINE=OFF)' AS Scripts
FROM sys.dm_db_index_physical_stats(DB_ID(@DB_NAME), NULL, NULL, NULL, NULL) AS s
    INNER JOIN sys.indexes AS i
        ON s.object_id = i.object_id
           AND s.index_id = i.index_id
    INNER JOIN sys.objects AS o
        ON i.object_id = o.object_id
    INNER JOIN sys.schemas AS sch
        ON o.schema_id = sch.schema_id
WHERE s.avg_fragmentation_in_percent > 10
      AND i.name IS NOT NULL
ORDER BY s.avg_fragmentation_in_percent DESC