在SSW关于更好的SQL Server数据库规则中，有一个完整的数据库维护计划示例：SSW。在示例中，他们先运行Reorganize Index，然后再运行Rebuild Index，最后运行更新统计信息。这样做有什么意义吗？我认为Reorganize Index是Rebuild Index的快速但不太有效的版本？并且索引重建还会自动更新统计信息（至少在聚集索引上）。
9个回答
35
重组和重建是两个不同的概念。
重组：它是索引碎片整理。将现有的索引进行碎片整理，但如果页面不是连续的，则保持原样。只有页面的内容会改变。
重建：实际上是删除索引并从头开始重新构建。这意味着您将获得一个全新的索引，其中包括了碎片整理和连续页面。
此外，使用重建操作，您可以更改分区或文件组，而使用重组操作，则既可以对整个索引进行碎片整理，也可以对索引中的一个分区进行碎片整理。
聚集索引的统计信息更新是自动进行的，但非聚集索引则不是。
- Biri
3
1没错，但在同一个维护子计划中同时拥有重新组织和重建的用处吗？ - codeulike
3根据Books Online上的资料，实际上，Reorg确实会重新组织页面，使它们物理上相邻。以下是确切的引用：“重新组织索引通过对表和视图上的聚集和非聚集索引的叶级别进行物理重排，以匹配叶节点的逻辑顺序（从左到右）来对其进行碎片整理。按顺序排列的页面可提高索引扫描性能。索引将在已分配给它的现有页面内重新组织；不会分配新页面。” - Michael K. Campbell
2@MichaelK.Campbell：你的引用有点断章取义。虽然ReOrg会重新排序页面，但它仅在中间级别节点指向它们的最低级别上重新排序它们。重组后，整个索引中的所有页面都不能保证是连续的。这里有一个更好的解释：http://dba.stackexchange.com/a/36817/6816 - MikeTeeVee
28
在考虑索引维护之前，重要的是要回答两个主要问题：
- 分片程度是什么？
- 适当的操作是什么？重新组织或重新构建？
如此文章所述http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/，并帮助您确定是否应执行索引重建或索引重新组织，请理解以下内容：
索引重新组织是一个过程，其中SQL Server通过现有索引进行清理。索引重建是一个重型过程，其中索引被删除，然后从头开始重新创建，具有全新的结构，摆脱了所有堆积的碎片和空白页。
虽然索引重新组织是一种纯清理操作，它会保留系统状态而不锁定受影响的表和视图，但重建过程将在整个重建期间锁定受影响的表，这可能导致长时间的停机时间，在某些环境中可能无法接受。 有了这个想法，很明显索引重建是一个“更强”的解决方案，但它的代价是可能会锁定受影响的索引表。
另一方面，索引重新组织是一个“轻量级”过程，以较不有效的方式解决分片问题-因为清理后的索引将始终位于全新的从头开始创建的索引之后。但是，从效率的角度来看，重新组织索引要好得多，因为在操作过程中不会锁定受影响的索引表。
上述文章还解释了如何使用SSMS、T-SQL（重新组织/重建表中的索引）和名为ApexSQL Backup的第三方工具重新组织和重建索引。
- Alex Kirilov
1
当重建索引时，是否需要更新表统计信息？特别是在存在非聚集索引的情况下？ - Moudiz
28
在同一个索引上执行REORGANIZE 和 REBUILD 是毫无意义的，因为REBUILD会覆盖REORGANIZE所做的任何更改。
更糟糕的是，在 SSW 的维护计划图表中，它首先执行SHRINK操作，这会导致索引根据释放空间的方式而受到碎片化的影响。然后，REBUILD在REBUILD操作期间再次将更多的空间分配给数据库文件作为工作空间。
REORGANIZE是一种在线操作，逐页对聚集或非聚集索引中的叶页进行紧凑处理，使用很少额外的工作空间。REBUILD是企业版中的一种在线操作，在其他版本中则是离线的，并且会使用与索引大小相同的额外工作空间。它创建索引的新副本，然后删除旧副本，从而消除碎片。默认情况下，统计信息将在此操作的一部分重新计算，但可以禁用此选项。
请参阅重新组织和重建索引获取更多信息。
不要使用SHRINK，除非使用TRUNCATEONLY选项，即使是这样，如果文件将再次增长，您也应该仔细考虑是否有必要：
- Llew
2
2真是太神奇了，有多少在线的“权威人士”完全是错误和误导性的，例如建议你在数据库上进行收缩操作！！ - Nick.McDermaid
1不是真的。如果你先“重新组织”，你可能会压缩数据页。在所有版本的SQL Server中，这是在线操作，可以逐渐完成。完全重建需要更多的资源。通过首先重新组织并逐渐压缩数据页，您将减少稍后重建所需的I/O，因为它将读取较少的数据页，从而执行较少的i/o并使用较少的内存。这是可以量化的。在我看来，说重新组织对随后的重建没有影响是荒谬的。 - Triynko
10
当对一个索引进行重组时，如果该索引跨越两个或多个物理文件，则仅在数据文件中对数据进行碎片整理。页面不会从一个数据文件移动到另一个数据文件。
当索引位于单个文件中时，重组和重新索引将有相同的结果。
有时，根据索引的碎片程度，重组速度可能更快，重新索引速度也可能更快。索引的碎片程度越少，重组会更快；碎片程度越多，重组会越慢，但重新索引会更快。
- mrdenny
8
正如Biri所说的那样。以下是我重新索引整个数据库的方法：
EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"
- GateKiller
1
现在的首选方法是不使用Alter Index - https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15 - Rohit Vipin Mathews
6
我使用这个SP。
CREATE PROCEDURE dbo.[IndexRebuild]
AS
DECLARE @TableName NVARCHAR(500);
DECLARE @SQLIndex NVARCHAR(MAX);
DECLARE @RowCount INT;
DECLARE @Counter INT;
DECLARE @IndexAnalysis TABLE
(
AnalysisID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
TableName NVARCHAR(500) ,
SQLText NVARCHAR(MAX) ,
IndexDepth INT ,
AvgFragmentationInPercent FLOAT ,
FragmentCount BIGINT ,
AvgFragmentSizeInPages FLOAT ,
PageCount BIGINT
)
BEGIN
INSERT INTO @IndexAnalysis
SELECT [objects].name ,
'ALTER INDEX [' + [indexes].name + '] ON ['
+ [schemas].name + '].[' + [objects].name + '] '
+ ( CASE WHEN ( [dm_db_index_physical_stats].avg_fragmentation_in_percent >= 20
AND [dm_db_index_physical_stats].avg_fragmentation_in_percent < 40
) THEN 'REORGANIZE'
WHEN [dm_db_index_physical_stats].avg_fragmentation_in_percent > = 40
THEN 'REBUILD'
END ) AS zSQL ,
[dm_db_index_physical_stats].index_depth ,
[dm_db_index_physical_stats].avg_fragmentation_in_percent ,
[dm_db_index_physical_stats].fragment_count ,
[dm_db_index_physical_stats].avg_fragment_size_in_pages ,
[dm_db_index_physical_stats].page_count
FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL,
NULL, 'LIMITED') AS [dm_db_index_physical_stats]
INNER JOIN [sys].[objects] AS [objects] ON ( [dm_db_index_physical_stats].[object_id] = [objects].[object_id] )
INNER JOIN [sys].[schemas] AS [schemas] ON ( [objects].[schema_id] = [schemas].[schema_id] )
INNER JOIN [sys].[indexes] AS [indexes] ON ( [dm_db_index_physical_stats].[object_id] = [indexes].[object_id]
AND [dm_db_index_physical_stats].index_id = [indexes].index_id
)
WHERE index_type_desc <> 'HEAP'
AND [dm_db_index_physical_stats].avg_fragmentation_in_percent > 20
END
SELECT @RowCount = COUNT(AnalysisID)
FROM @IndexAnalysis
SET @Counter = 1
WHILE @Counter <= @RowCount
BEGIN
SELECT @SQLIndex = SQLText
FROM @IndexAnalysis
WHERE AnalysisID = @Counter
EXECUTE sp_executesql @SQLIndex
SET @Counter = @Counter + 1
END
GO
并创建一个每周执行此存储过程的任务。
- Ardalan Shahgholi
1
不回答问题。 - Alan Macdonald
4
更好的是：
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REINDEX'
或者
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'
- Chris KL
1
我在网上进行了研究并找到了一些好的文章。最后，我编写了以下函数和脚本，以重新组织、重建或重建数据库中的所有索引。
首先，您可能需要阅读此文章以了解为什么我们不仅仅是重新创建所有索引。
其次，我们需要一个函数来构建索引的创建脚本。因此，此文章可能会有所帮助。此外，我将分享下面的工作函数。
最后一步是制作一个while循环，以查找和组织数据库中的所有索引。此视频是一个很好的示例。
函数：
create function GetIndexCreateScript(
@index_name nvarchar(100)
)
returns nvarchar(max)
as
begin
declare @Return varchar(max)
SELECT @Return = ' CREATE ' +
CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END +
I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
I.name + ' ON ' +
Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
KeyColumns + ' ) ' +
ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' +
CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' +
'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' +
-- default value
'SORT_IN_TEMPDB = OFF ' + ',' +
CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' +
CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' +
-- default value
' DROP_EXISTING = ON ' + ',' +
-- default value
' ONLINE = OFF ' + ',' +
CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' +
CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' +
DS.name + ' ] '
FROM sys.indexes I
JOIN sys.tables T ON T.Object_id = I.Object_id
JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
JOIN (SELECT * FROM (
SELECT IC2.object_id , IC2.index_id ,
STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
FROM sys.index_columns IC1
JOIN Sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column = 0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY IC1.object_id,C.name,index_id
ORDER BY MAX(IC1.key_ordinal)
FOR XML PATH('')), 1, 2, '') KeyColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
LEFT JOIN (SELECT * FROM (
SELECT IC2.object_id , IC2.index_id ,
STUFF((SELECT ' , ' + C.name
FROM sys.index_columns IC1
JOIN Sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column = 1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY IC1.object_id,C.name,index_id
FOR XML PATH('')), 1, 2, '') IncludedColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY IC2.object_id ,IC2.index_id) tmp1
WHERE IncludedColumns IS NOT NULL ) tmp2
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0
AND I.[name] = @index_name
return @Return
end
Sql for while:
declare @RebuildIndex Table(
IndexId int identity(1,1),
IndexName varchar(100),
TableSchema varchar(50),
TableName varchar(100),
Fragmentation decimal(18,2)
)
insert into @RebuildIndex (IndexName,TableSchema,TableName,Fragmentation)
SELECT
B.[name] as 'IndexName',
Schema_Name(O.[schema_id]) as 'TableSchema',
OBJECT_NAME(A.[object_id]) as 'TableName',
A.[avg_fragmentation_in_percent] Fragmentation
FROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A
INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
INNER JOIN sys.objects O ON O.[object_id] = B.[object_id]
where B.[name] is not null and B.is_primary_key = 0 AND B.is_unique_constraint = 0 and A.[avg_fragmentation_in_percent] >= 5
--select * from @RebuildIndex
declare @begin int = 1
declare @max int
select @max = Max(IndexId) from @RebuildIndex
declare @IndexName varchar(100), @TableSchema varchar(50), @TableName varchar(100) , @Fragmentation decimal(18,2)
while @begin <= @max
begin
Select @IndexName = IndexName from @RebuildIndex where IndexId = @begin
select @TableSchema = TableSchema from @RebuildIndex where IndexId = @begin
select @TableName = TableName from @RebuildIndex where IndexId = @begin
select @Fragmentation = Fragmentation from @RebuildIndex where IndexId = @begin
declare @sql nvarchar(max)
if @Fragmentation < 31
begin
set @sql = 'ALTER INDEX ['+@IndexName+'] ON ['+@TableSchema+'].['+@TableName+'] REORGANIZE WITH ( LOB_COMPACTION = ON )'
print 'Reorganized Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation)
end
else
begin
set @sql = (select dbo.GetIndexCreateScript(@IndexName))
if(@sql is not null)
begin
print 'Recreated Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation)
end
else
begin
set @sql = 'ALTER INDEX ['+@IndexName+'] ON ['+@TableSchema+'].['+@TableName+'] REBUILD PARTITION = ALL WITH (ONLINE = ON)'
print 'Rebuilded Index ' + @IndexName + ' for ' + @TableName + ' Fragmentation was ' + convert(nvarchar(18),@Fragmentation)
end
end
execute(@sql)
set @begin = @begin+1
end
- vast
0
我的看法是... 这种方法遵循了TechNet上概述的规范: http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx
USE [MyDbName]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [maintenance].[IndexFragmentationCleanup]
AS
DECLARE @reIndexRequest VARCHAR(1000)
DECLARE reIndexList CURSOR
FOR
SELECT INDEX_PROCESS
FROM (
SELECT CASE
WHEN avg_fragmentation_in_percent BETWEEN 5
AND 30
THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REORGANIZE;'
WHEN avg_fragmentation_in_percent > 30
THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REBUILD with(ONLINE=ON);'
END AS INDEX_PROCESS
,avg_fragmentation_in_percent
,t.NAME
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS i ON a.object_id = i.object_id
AND a.index_id = i.index_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.NAME IS NOT NULL
) PROCESS
WHERE PROCESS.INDEX_PROCESS IS NOT NULL
ORDER BY avg_fragmentation_in_percent DESC
OPEN reIndexList
FETCH NEXT
FROM reIndexList
INTO @reIndexRequest
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
PRINT @reIndexRequest;
EXEC (@reIndexRequest);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = 'UNABLE TO CLEAN UP INDEX WITH: ' + @reIndexRequest + ': MESSAGE GIVEN: ' + ERROR_MESSAGE()
,@ErrorSeverity = 9
,@ErrorState = ERROR_STATE();
END CATCH;
FETCH NEXT
FROM reIndexList
INTO @reIndexRequest
END
CLOSE reIndexList;
DEALLOCATE reIndexList;
RETURN 0
GO
- mcfea
1
这很棒，但会导致重复，意味着可能需要多次重新组织或重建，应该避免这种情况发生。 - smoore4
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接