根据碎片化结果自动重建索引？

Question

是否有可能添加一个维护作业来检查索引碎片化情况。如果大于50％，则自动重建这些索引？

索引大小可能从100MB到10GB不等。 SQL 2005。

谢谢。

- user1372635

---

你必须重建高于30%的碎片化索引。使用SQL DMV非常容易实现。我现在不在电脑旁，稍后会发布一个脚本。 - Namphibian

Answer 1

我使用这个脚本。请注意，我建议您了解一下我在这里使用的dmv，它们是SQL2005+中的一个隐藏宝石。

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

请注意，此脚本可能需要一段时间并阻止访问您的表格。除非您拥有企业版SQL，否则在重建索引时SQL可以锁定表格。这将阻塞使用该索引的该表格的所有查询，直到完成索引整理为止。因此，建议仅在维护窗口期间运行索引重建，而不是在操作时间内运行。如果您正在运行企业版，则可以使用ONLINE=ON选项在线进行碎片整理。这会使用更多的空间，但在整理操作期间不会阻止/锁定您的表格。

如果需要更多信息，请告知。

更新：

如果您在较小的数据库上运行此查询，您可能可以在调用sys.dm_db_index_physical_stats时使用“DETAILED”参数。这可能是对索引的更详细的检查。评论中的讨论也会指出，在更大的表格上，进行SAMPLED扫描可能值得一试，因为这将有助于减少执行索引扫描所需的时间。

- Namphibian

---

如果您使用SAMPLED模式，则不会处理索引的非叶级别。从BOL表单开始：使用SAMPLED模式获取compressed_page_count的估计值，并使用DETAILED模式获取compressed_page_count的实际值。SAMPLED模式基于索引或堆中所有页面的1％样本返回统计信息。在SAMPLED模式下得到的结果应视为近似值。 - Diego

如果您使用“采样”功能，则必须意识到可能无法获得准确的结果。 - Diego

确实，但是 500,000,000 行表的 1% 是 5,000,000。在某些情况下这已经足够好了。这实际上取决于索引的大小和您进行碎片整理的时间窗口。 - Namphibian

我正在使用与Azure SQL配合使用，该数据库支持每个版本的 ALTER INDEX REBUILD WITH (ONLINE=ON)。我还移除了使用 REORGANIZE 的 CASE 语句的第二部分。ALTER INDEX 的 REORGANIZE 修改器目前不受 Azure SQL 支持。您可能还想添加 try/catch 块，以便不支持在线索引的表不会导致整个操作失败。 - NovaJoe

Answer 2

如果您想避免创建任何临时表并解析字符串以创建SQL字符串列表，这里有一种有效的方法来完成它：

USE databasename

GO

DECLARE @Queryresult NVARCHAR(4000)

SET @Queryresult=''

SELECT

  @Queryresult=@Queryresult + 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON '
  + QUOTENAME('dbo') + '.'
  + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) + ' REBUILD;'

 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ss

  INNER JOIN sys.indexes i ON i.OBJECT_ID = ss.OBJECT_ID AND i.index_id = ss.index_id

  INNER JOIN sys.objects o ON ss.object_id = o.object_id

WHERE ss.avg_fragmentation_in_percent > 50

AND ss.record_count > 0 

AND o.is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation

AND ss.index_id > 0     --Excludes heap indexes

EXEC sp_executesql @Queryresult

- FutbolFan

Answer 3

您可以使用sys.dm_db_index_physical_stats获取有关索引碎片化的信息（请参阅avg_fragmentation_in_percent列）。然后，每当达到阈值时，您可以使用重建子句进行索引更改。

- David Brabant

Answer 4

可以的。

您可以使用以下查询获取分段索引：

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

根据结果构建一个命令来重新创建它们。

我会将所有内容封装在存储过程中，并从SQL Server作业中调用它。

顺便说一下，50％的碎片化非常严重。我会选择更少的碎片化。

- Diego

---

sys.dm_db_index_physical_stats函数调用'DETAILED'参数时，一旦数据行数超过500,000,000，它可能会有点难以运行。当处理更大的表时，SAMPLED参数可以帮助减少分析索引所花费的时间，并且您实际上可以按时完成索引重建。只是一个注意事项，我喜欢你实际上知道DMV，这是一种黑带功夫艺术！ - Namphibian

如果您使用SAMPLED模式，可能无法获得准确的结果，因为您将仅扫描索引的叶级别：请使用SAMPLED模式获取compressed_page_count的估计值，并使用DETAILED模式获取compressed_page_count的实际值。 SAMPLED模式基于索引或堆中所有页面的1％样本返回统计信息。在SAMPLED模式下的结果应视为近似值。 - Diego

确实，但如果您需要在2小时内对一个拥有1亿行的表进行碎片整理，有时您需要做出权衡。请注意，如果您正在对需要保持在线和可用的大型表进行碎片整理，则可能会使用此解决方案。请注意，使用SAMPLED还具有以下效果：如果索引或堆栈少于10,000页，则使用DETAILED参数。我发现大多数情况下，这实际上允许您在维护窗口期间完成工作。 - Namphibian