根据碎片结果自动重建 INDEX? [英] Automate INDEX rebuild based on fragmentation results?

问题描述

是否可以添加维护作业来检查索引碎片.如果大于 50% 则自动重建这些索引?

Is it possible to add a maintenance job to check indexes fragmentation. If greater than 50% then rebuild those indexes automatically ?

索引大小可以从 100MB 到 10GB 不等.SQL 2005.

Index size can vary from 100MB to 10GB. SQL 2005.

谢谢.

推荐答案

我使用这个脚本.请注意，我建议您阅读有关我在这里使用的 dmv 它们是 SQL2005+ 中隐藏的宝石.

I use this script . Please note I would advise you reading up about the dmv I am using here they are a hidden gem in 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 可以在重建索引时锁定表.这将使用索引阻止对该表的所有查询，直到索引碎片整理完成.因此，不建议仅在维护时段的操作时间内运行索引重建.如果您运行的是企业版，您可以使用 ONLINE=ON 选项在线整理索引.这将使用更多空间，但在碎片整理操作期间您的表不会被阻塞/锁定.

Also keep in mind that this script can run a while and block access to your tables. Unless you have Enterprise editions SQL can LOCK the table when rebuilding the index. This will block all queries to that table using the index till the index defrag is finished. Thus it is not advised to run index rebuild during operational hours only during maintenance windows. If you are running enterprise edition you can use the ONLINE=ON option to defrag indexes online. This will use more space but your tables wont be blocked/locked during the defrag operation.

如果您需要更多信息，请大喊.

Shout if you need more information.

更新:

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

If you are running this query on a smaller database you can probably use the 'DETAILED' parameter in the call to sys.dm_db_index_physical_stats. This is probably a more detailed examination of the indexes. The discussion in the comments will also point out that on much larger tables it is probably worth doing a SAMPLED scan as this will help reduce the time needed to do the index scan.

这篇关于根据碎片结果自动重建 INDEX?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！