监控、维护、规划SQLSERVER数据库索引碎片问题

本文内容：

像大多数使用索引来提高系统性能的数据库系统一样，规划服务器系统也使用索引来获得最佳性能。对于所有聚集或非聚集索引，INSERT/UPDATE 将会导致拆分页面，从而出现碎片。随着时间的推移，索引碎片现象会越来越严重，以致系统性能下降。因此，数据库管理员应务必监控索引碎片状况，并知晓在索引变得非常零碎时如何进行处理。

应监控规划应用数据库中所有表的索引碎片状况，尤其要注意名称前缀为“MG_”的度量值组表。在应用数据库中，会对度量值组表执行大量的插入、更新和删除操作。执行这些数据更改的操作类型包括分配提交和计算规则执行。某些度量值组表的活动相对较多一些。因此，随着时间的推移，“MG_*”表的索引会变得非常零碎，如果不定期重建或组织索引就会显著降低系统性能。

检查度量值组表的索引

创建模型时，规划服务器会对新的“MG_*”表创建一个聚集索引。此聚集索引包含该度量值组表中的所有维度键。此聚集索引中的维度键的顺序由规划服务器任意排列。这种顺序可能无法最好地反映出单个用户的度量值组表使用情况（例如，写回使用情况、数据加载使用情况和基于规则的计算使用情况）。数据库管理员可能需要查看聚集索引的列顺序并重新排列该顺序。

例如，以下聚集索引是按下列预先排好的列顺序针对 MG_Strategic_Plan_MeasureGroup_default_partition 表创建的。您可以重新排列此索引中的列顺序，使其符合该度量值组表的使用情况。您可能还希望将 Entity_MemberID 列移到此索引的第一列，因为 PerformancePoint Excel 加载项用户经常要基于实体执行写回操作。

CREATE CLUSTERED INDEX [ClusteredIndex_default_partition] ON [dbo].[MG_Strategic_Plan_MeasureGroup_default_partition] ( [Scenario_MemberId] ASC, [Time_Month] ASC, [Account_MemberId] ASC, [BusinessProcess_MemberId] ASC, [Entity_MemberId] ASC, [TimeDataView_MemberId] ASC, [Currency_MemberId] ASC, [BusinessDriver_MemberId] ASC, [Product_MemberId] ASC, [Flow_MemberId] ASC, [Intercompany_MemberId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

示例

下面的示例演示如何使用联机选项重建规划应用数据库中某度量值组表的索引。

ALTER INDEX [ClusteredIndex_default_partition] ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition] REBUILD WITH (ONLINE = ON);

数据库管理员应在运行 Microsoft SQL Server 2005 的计算机上创建一个作业，以便对每个度量值组表的索引进行碎片整理。根据碎片情况，安排该作业以不同的频率重新组织并重建索引。

您可以决定每天重新组织一次索引并且每周重建一次索引。一些索引可能需要更频繁地进行重建，如度量值组表的聚集索引。将重建索引作业安排在非高峰时间（如午夜）进行。这两种方法都可以联机完成，但表例外，它们的数据类型为业务线。如果您选择脱机（而不是联机）重建索引，则需要先使应用程序脱机。有关如何使应用程序脱机的信息，请参阅规划管理控制台帮助。

检查索引碎片

随着时间的推移，对数据库所做的修改会导致数据库中的索引信息变得分散或零碎。当索引碎片情况很严重时会降低查询性能，使应用程序的响应速度变慢。

在 SQL Server 2005 中，您可以通过重新组织或重建索引来解决索引碎片问题。您需要先分析索引以确定其零碎程度，然后再决定使用哪种碎片整理方法。

示例

下面的示例演示如何监控索引碎片。

当您运行下面的代码语句时：

SELECT a.index_id, name, avg_fragmentation_in_percent, * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

将返回如下结果集。

index_id	name	avg_fragmentation_in_percent
1	MG_Strategic_Plan_MeasureGroup_default_partition_PK	23.076923076923077

有关碎片的详细信息，请参阅 。在本示例中，建议的解决方法是重新组织 MG_Strategic_Plan_MeasureGroup_default_partition_PK，因为avg_fragmentation_in_percent 小于 30%。

如果碎片已经超过 30%，建议重建索引。

注意：

上面提到的 30% 是  中的建议值。您可以更改此数字以符合贵组织的需求。

重新组织索引

当索引的零碎程度不严重时，最佳做法是重新组织索引。但是，如果索引的零碎情况很严重，则重建索引可以获得更好的效果。有关碎片的指导信息，请参阅Checking Index Fragmentation。

示例

下面的示例演示如何在规划服务器中重新组织某度量值组表的主键索引。若要重新组织一个或多个索引，请将 ALTER INDEX 语句和 REORGANIZE 子句结合使用。

ALTER INDEX [ClusteredIndex_default_partition] ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition] REORGANIZE ;

重新组织索引不象重建索引那么彻底；它只清理叶级别的碎片。它不会移动对象以获得更好的区扫描密度。重新组织索引命令始终联机运行。在重新组织索引过程中，不会保留长时间运行的锁。有关重新组织索引以及重新组织索引和重建索引的优缺点的信息，请参阅 。

重建索引

只需删除索引并创建新索引即可重建索引。创建新索引时，将删除碎片。

下面的示例演示如何在规划服务器中使用联机选项重建某度量值组表的索引。

ALTER INDEX [ClusteredIndex_default_partition] ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition] REBUILD WITH (ONLINE = ON);

安排一个 SQL Server 作业对每个度量值组表的索引进行碎片整理。确定碎片程度之后，安排该作业以不同的频率执行索引重新组织和索引重建操作。您可能决定每天重新组织一次索引，并将时间安排在非高峰时间（如午夜），然后每周重建一次索引。这两种方法都可以联机完成，但表例外，它们的数据类型为业务线。

重建索引的效果通常好于重新组织索引。重建索引操作可以删除叶级别和 B 树中的各级碎片，使树重新获得平衡。此操作可更新统计信息，这相当于使用正确的统计信息执行一次“完全扫描”。