SQL Server 索引管理

1. 获取索引脚本

USE $DBName$;

SELECT   t.sch
       , t.tbl
       , t.idx
       , CASE t.idxType
              WHEN 0 THEN N'堆'
              WHEN 1 THEN N'聚集'
              WHEN 2 THEN N'非聚集'
              WHEN 3 THEN N'XML'
              WHEN 4 THEN N'空间'
              WHEN 5 THEN N'聚集列存储索引'
              WHEN 6 THEN N'非群集列存储索引'
              WHEN 7 THEN N'非群集哈希索引'
         END idxDesc
       , CASE WHEN t.is_primary_key = 1 THEN
                'ALTER TABLE [' + t.sch + '].[' + t.tbl + '] ADD CONSTRAINT [' + t.idx + '] PRIMARY KEY '
                + CASE WHEN t.idxType = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' (' + t.idxCols + ')'
              WHEN t.is_unique = 1 AND t.is_unique_constraint = 1 THEN
                'ALTER TABLE [' + t.sch + '].[' + t.tbl + '] ADD CONSTRAINT [' + t.idx + '] UNIQUE '
                + CASE WHEN t.idxType = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' (' + t.idxCols + ')'
              WHEN t.is_unique = 1 AND (t.is_primary_key = 0 OR t.is_unique_constraint = 0) THEN
                'CREATE UNIQUE ' + CASE WHEN t.idxType = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' INDEX [' + t.idx + '] ON [' + t.sch + '].[' + t.tbl
                + '] (' + t.idxCols + ')'
              ELSE
                'CREATE ' + CASE WHEN t.idxType = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' INDEX [' + t.idx + '] ON [' + t.sch + '].[' + t.tbl + '] ('
                + t.idxCols + ')'
         END + ISNULL(' INCLUDE (' + t.includeCols + ')', '') + CASE WHEN t.has_filter = 1 THEN ' WHERE ' + t.filter_definition ELSE '' END
FROM     (
  SELECT     sch.name sch
           , tbl.name tbl
           , idx.name idx
           , idx.type idxType
           , idx.is_primary_key
           , idx.is_unique
           , idx.is_unique_constraint
           , idx.has_filter
           , idx.filter_definition
           , STUFF((
                     SELECT     ', [' + col.name + ']'
                     FROM       sys.sysindexkeys idxKey
                     INNER JOIN sys.index_columns idxCol ON idxCol.object_id = idxKey.id AND idxCol.index_id = idxKey.indid AND idxCol.column_id = idxKey.colid
                     INNER JOIN sys.syscolumns col ON col.id = idxKey.id AND col.colid = idxKey.colid
                     WHERE      idxKey.id = idx.object_id AND idxKey.indid = idx.index_id AND idxKey.keyno <> 0
                     ORDER BY   idxCol.key_ordinal
                     FOR XML PATH('')
                   )
                 , 1
                 , 2
                 , ''
             ) idxCols
           , STUFF((
                     SELECT     ', [' + col.name + ']'
                     FROM       sys.sysindexkeys idxKey
                     INNER JOIN sys.index_columns idxCol ON idxCol.object_id = idxKey.id AND idxCol.index_id = idxKey.indid AND idxCol.column_id = idxKey.colid
                     INNER JOIN sys.syscolumns col ON col.id = idxKey.id AND col.colid = idxKey.colid
                     WHERE      idxKey.id = idx.object_id AND idxKey.indid = idx.index_id AND idxKey.keyno = 0
                     ORDER BY   idxCol.key_ordinal
                     FOR XML PATH('')
                   )
                 , 1
                 , 2
                 , ''
             ) includeCols
  FROM       sys.schemas sch
  INNER JOIN sys.tables tbl ON tbl.schema_id = sch.schema_id
  INNER JOIN sys.indexes idx ON idx.object_id = tbl.object_id
  WHERE      tbl.object_id IN (OBJECT_ID('$DBName$.$dbo$.$TblName$')) AND idx.type IN (1, 2) AND idx.index_id > 0 AND idx.is_hypothetical = 0
) t
ORDER BY t.tbl, t.idx;

2. 查看索引碎片

USE $DBName$;

IF OBJECT_ID('tempdb.dbo.#idxStats') IS NOT NULL
  DROP TABLE #idxStats;

SELECT     DB_NAME(idxStats.database_id) dbName
         , OBJECT_SCHEMA_NAME(idxStats.object_id, idxStats.database_id) schName
         , OBJECT_NAME(idxStats.object_id) tblName
         , idx.name idxName
         , idxStats.index_type_desc idxTypeDesc
         , idxStats.avg_fragmentation_in_percent fragmentationPercent
         , idxStats.page_count pageCount
INTO       #idxStats
FROM       sys.dm_db_index_physical_stats(DB_ID('$DBName$'), NULL, NULL, NULL, NULL) AS idxStats
INNER JOIN sys.indexes idx ON idx.object_id = idxStats.object_id AND idx.index_id = idxStats.index_id;

SELECT t.dbName, t.schName, t.tblName, t.idxName, t.idxTypeDesc, t.fragmentationPercent, t.pageCount FROM #idxStats t;

SELECT COUNT(1) total
     , COUNT(CASE WHEN t.pageCount > 1000 AND t.fragmentationPercent > 5 AND t.fragmentationPercent <= 15 THEN 1 END) r_5_15
     , COUNT(CASE WHEN t.pageCount > 1000 AND t.fragmentationPercent > 15 AND t.fragmentationPercent <= 30 THEN 1 END) r_15_30
     , COUNT(CASE WHEN t.pageCount > 1000 AND t.fragmentationPercent > 30 THEN 1 END) g30
FROM   #idxStats t;

3. 清理索引碎片

USE $DBName$;
GO

DECLARE @clrIdxFragSql VARCHAR(MAX);

SET @clrIdxFragSql = (
  SELECT     STRING_AGG(
               CAST('ALTER INDEX [' + idx.name + '] ON [' + OBJECT_SCHEMA_NAME(idxStats.object_id, idxStats.database_id) + '].[' + OBJECT_NAME(idxStats.object_id)
                    + '] ' + CASE WHEN idxStats.avg_fragmentation_in_percent >= 30 THEN 'REBUILD PARTITION = ALL' ELSE 'REORGANIZE' END AS VARCHAR(MAX))
             , '
'
             )
  FROM       sys.dm_db_index_physical_stats(DB_ID('$DBName$'), NULL, NULL, NULL, NULL) AS idxStats
  INNER JOIN sys.indexes idx ON idx.object_id = idxStats.object_id AND idx.index_id = idxStats.index_id
  WHERE      idxStats.avg_fragmentation_in_percent >= $15$ AND idxStats.page_count > $1000$
);

EXEC (@clrIdxFragSql);
GO