SQL Server 重建索引 - 脚本

Question

我正在使用来自@Namphibian 的脚本，但我在那里遇到了一些问题。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

CREATE TABLE #FragmentedIndexes
(
    DatabaseName SYSNAME,
    SchemaName SYSNAME,
    TableName SYSNAME,
    IndexName SYSNAME,
    [Fragmentation%] FLOAT
)

INSERT INTO #FragmentedIndexes
    SELECT
        DB_NAME(DB_ID()) AS DatabaseName,
        ss.name AS SchemaName,
        OBJECT_NAME (s.object_id) AS TableName,
        i.name AS IndexName,
        s.avg_fragmentation_in_percent AS [Fragmentation%]
    FROM 
        sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
    INNER JOIN 
        sys.indexes i ON s.[object_id] = i.[object_id]
                      AND s.index_id = i.index_id
    INNER JOIN 
        sys.objects o ON s.object_id = o.object_id
    INNER JOIN 
        sys.schemas ss ON ss.[schema_id] = o.[schema_id]
    WHERE 
        s.database_id = DB_ID()
        AND i.index_id != 0
        AND s.record_count > 0
        AND o.is_ms_shipped = 0

DECLARE @RebuildIndexesSQL NVARCHAR(MAX)

SET @RebuildIndexesSQL = ''
SELECT
 @RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
 WHEN [Fragmentation%] > 30
   THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
      + QUOTENAME(SchemaName) + '.'
      + QUOTENAME(TableName) + ' REBUILD;'
 WHEN [Fragmentation%] > 10
    THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
    + QUOTENAME(SchemaName) + '.'
    + QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
 PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @Length
END

PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL

DROP TABLE #FragmentedIndexes

但是我使用的不是“SAMPLED”而是“DETAILED”，但仍然有一些索引没有重建。我发现有几个索引具有相同的碎片值超过 30%，但仍未重建或重组。该脚本过去 4 天每晚都在运行。我的问题是我无法为此任务使用维护计划。

有什么想法吗？

Answer 1

根据这个答案: https://dba.stackexchange.com/questions/18372/why-index-rebuild-does-not-reduce-index-fragmentatation

您需要考虑索引的页数才能知道是否进行重建

我会建议将您的 INSERT INTO SELECT 更改为此

    SELECT
    DB_NAME(DB_ID()) AS DatabaseName,
    ss.name AS SchemaName,
    OBJECT_NAME (s.object_id) AS TableName,
    i.name AS IndexName,
    s.avg_fragmentation_in_percent AS [Fragmentation%],
    page_count
FROM 
    sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'DETAILED') s
INNER JOIN 
    sys.indexes i ON s.[object_id] = i.[object_id]
                  AND s.index_id = i.index_id
INNER JOIN 
    sys.objects o ON s.object_id = o.object_id
INNER JOIN 
    sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE 
    s.database_id = DB_ID()
    AND i.index_id != 0
    AND s.record_count > 0
    AND o.is_ms_shipped = 0
    AND s.avg_fragmentation_in_percent > 0
    AND page_count > 1000