问在Server上重建分段索引

EN

我还能做些什么来减少碎片的频率呢？

也许您可以为您的表找到合适的FillFactor。

我建议您阅读这篇文章：SQL &A数据库一致性、临时表以及Paul S. Randal的更多内容

问:我们正在实施一项夜间数据库维护计划，其中包括提高索引性能。我听说，为索引设置“填充因子”选项可以完全消除维护索引的需要。这是真的吗？似乎我们的数据库中的一些索引会受到碎片的影响，而有些则不会。我们应该为数据库设置一个适用于所有索引的默认填充因子吗?如果是，我们应该使用什么值？答:填充因子设置确实可以部分缓解索引维护的需要，但很少能完全消除这种需求。简而言之，填充因子设置指示存储引擎在创建或重建聚集索引和非聚集索引页时保留一定比例的空闲空间。( 注意，在常规的insert/update/delete操作中没有维护填充因子设置。)例如，填充因子为90，留下10%的空闲空间。0或100的填充因子都没有留下任何空闲空间(这是造成许多混乱的根源)。这样做的想法是在页面中留出空间，这样页面上的记录就可以展开，或者新的记录可以插入到页面上，而不会导致称为页面拆分的昂贵的、导致碎片的操作。您可以指定一个百分比的空闲空间，这样页面就可以更加稳定地填满，直到下一个索引维护操作发生，这将再次重置填充因子。诀窍是选择一个百分比，以尽量减少索引维护操作之间的分页。对于联机事务处理( OLTP )数据库，除了根据试验和错误为每个索引选择填充因子之外，没有简单的答案。对于索引不变的数据仓库，填充因子应该是100% (意味着页面上没有空闲空间)。数据库的默认填充因子从默认的100%更改为非常少见，因为各种索引的最佳填充因子通常不同。Server 2008联机丛书主题“填充因子”包含更多有关此内容的信息。另一种选择是更改索引，以免出现分页现象。这可能涉及更改索引键，使插入不是随机的(例如，不使用随机GUID主键)或不允许更改可变长度列大小的操作。

索引重建(联机)是一种资源密集型操作。在重建过程中，存在锁，在很短的时间内，这些锁可以使表不可用(非常短的时间).Below链接为联机索引操作提供了指导方针：

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations

上述链接中的一段：

因为源结构和目标结构都是在联机索引操作期间维护的，因此插入、更新和删除事务的资源使用量会增加一倍。在索引操作期间，这可能会导致性能下降和更大的资源使用，特别是CPU时间。联机索引操作将被完全记录。

建议在最小操作窗口期间安排脚本。根据您的环境便利性，确定频繁的高碎片表，并将它们分别处理。

而且，如果可能的话，总是为数据库定义一个维护窗口。

您可能需要考虑将统计数据更新到具有高度分段性的索引中。

是的，索引是支离破碎的，但是它们仍然是索引，并且不管碎片的级别如何，它们仍然可以工作。

但是，在你.

• 更新统计数据
• 重建索引

为什么？有一个内置算法来决定何时更新统计信息。根据下列文件更新统计数字：

查询优化器通过计数自上次统计更新以来的数据修改数并将修改次数与阈值进行比较来确定统计信息何时过时。阈值基于表或索引视图中的行数。

参考文献：

- 统计数据 (Microsoft )

- Server统计:解释 (微软开发者博客)

然后，本文档继续解释Server 2014及更高版本的算法：

直到Server 2014，Server使用基于行更改百分比的阈值。这与表中的行数无关。阈值是: --如果评估统计数据时表基数为500或更少，则每500 modifications.更新一次--如果评估统计数据时表基数高于500，则每500 + 20 %的修改更新一次。

如果要对200万行表进行调整，那么统计数据将在Server 2014或更高版本的20% changes/rows * 2'000'000 rows = 200'000 changes之后大致更新。

SQL 2016和更新版本略有不同：

从Server 2016开始，在数据库兼容性级别130下，Server使用一个递减的动态统计更新阈值，根据表中的行数进行调整。这是以1,000的平方根乘以当前的表基数来计算的。随着这一变化，大型表上的统计数据将更经常地更新。但是，如果数据库的兼容级别低于130，则适用Server 2014阈值。

参考文献：

- 统计数据 (Microsoft )

- 在Microsoft平台上运行SAP应用程序

如果要将其调整为Server环境中的200万行表，则统计信息将在大约2%的数据更改后更新。2% changes/rows * 2'000'000 rows = 20'000 changes。

所以你可能会遇到这样一种情况：

情景1

1. 没有发生足够的变化
2. 统计过时
3. 索引零碎
4. 糟糕的查询计划

..。性能不佳，索引碎片

情景2

1. 很多变化都在发生
2. 定期更新统计数据
3. 索引零碎
4. 好的查询计划

..。周期性的性能不佳(统计数据更新)，索引碎片导致的性能比去碎片时稍差。

形势3

1. 不相关的变化
2. 统计数据更新与否
3. 索引零碎
4. 坏的或好的查询计划

..。性能参差不齐，索引碎片导致的性能比去碎片时稍差。

可能的解决方案

1. 手动更新大型表上的索引的统计信息，以确保您有最新的统计信息和查询计划，这些统计信息和查询计划将根据数据的基数选择正确的索引扫描/查找选项。
2. 根据插入数据的频率，创建FILLFACTOR为90、80甚至更低的索引，以减少插入/修改数据造成的索引碎片。
3. 打开选项AUTO_UPDATE_STATS_ASYNC = ON，以降低由于统计数据在一天中的高峰时间被更新而造成的性能影响的可能性。

Considerations

正如其他人所指出的，索引碎片“本身”不应该是一个问题。然而，视各种因素而定，它可以作为其他问题的一个标志。

重新生成索引时，将刷新统计信息，性能将恢复正常。

参考资料

• 统计数据 (Microsoft )
• 服务器统计:解释 (微软开发者博客)
• 在Microsoft平台上运行平台上运行应用程序
• 服务器2016的默认自动统计更新阈值更改 (CSS工程师)
• 对Server 2016中自动更新统计值阈值的更改 (Brenzozar.com)
• 更新统计信息(Transact-SQL) (Microsoft )
• (Azure SQL数据库) (Microsoft )
• 统计数据_日期(Transact-SQL) (Microsoft )