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

但是我使用的是"详细"而不是"采样"，但仍然没有重建一些索引。我发现一些索引的碎片值相同，超过 30%，但尚未重建或重组。该脚本在过去 4 天每晚运行。我的问题是我不能使用维护计划来完成这项任务。

有什么想法吗？

Answer 1

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

您需要考虑索引的页数，以了解是否进行重建

我建议将您的插入到选择更改为此

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