主页 / dba / 问题 / 141951
Accepted
James Rhoat
Asked: 2016-06-23 07:26:29 +0800 CST

更频繁地更新统计信息并停止对索引进行碎片整理

  • 772

我有几个供应商数据库，其中大部分 (99%) 表都聚集在 GUID 上。目前我们每个周末都在重建表格。此时大多数表的碎片化程度达到 80% 或更多。

除了这种无休止的循环，我们不重建会更好吗？

更频繁地更新静态信息而不是重建索引会更有益吗？

我已经阅读了 Brent Ozar 的Stop Worrying About SQL Server Fragmentation，但它并没有真正回答我的问题。我希望有一篇更完整的文章，并且可能是最近更新的文章。

我尽量不这样做，因为我知道这是没有必要的。如果有助于更好地了解我的情况，请从 Brent 的博客再次查看本文的开头部分。我希望有一个包含文章和用例的正式计划，以便在进行更改之前向管理层提出建议并阅读适当的文档。

我正在为我们的生产数据库设计一个新的维护计划。我们唯一的维护是每周重建。有几个（30 多个）数据库都在 700 GB-1.2 TB 左右。表的最大尺寸范围为 100-300 GB。

我希望有一个更好的维护计划，最终提供更好的性能。

sql-server fragmentation

3 个回答

  1. Best Answer

    查询优化器使用统计信息来帮助估计操作的行数，以便它可以确定完成该操作的最有效方法。

    由于您在这些表中使用 GUID 作为主键，因此非常怀疑您是否正在执行任何范围操作/多行操作。由于 GUID 没有固有的顺序，您实际上只能进行单行操作。并且由于这些是主键，它们是“唯一的”并且任何值只存在一次，或者根本不存在，因此您的操作要么在对 PK 进行过滤时影响 1 行，要么在不对任何内容进行过滤时影响所有行（我我暂时忽略其他过滤器，因为那些过滤器会使用另一个索引的统计信息，或在列上自动创建的统计信息，这两者都不受此特定问题的影响）。从这个意义上说，仅仅更新统计数据不会有太大帮助（或者至少不应该）。

    同时，由于针对这些 PK 的操作是单例操作（即查找），因此这些操作不会受到碎片的很大影响。从这个意义上说，重建集群 PK 不会对这些操作产生非常明显的影响。然而，重建（至少偶尔）仍然有好处。

    所以我的建议是：

    1. 更简单但整体效果较差的路径：

      由于您的系统目前正遭受严重的页面拆分崩溃，您需要释放那些浪费/未使用的空间，因为许多数据页之间存在大量浪费空间，这些空间不仅填满了缓冲池，而且还使备份/恢复操作需要更长的时间。所以，尝试做几个星期的索引REORGANIZE操作，然后是REBUILD. 我不确定您使用的是哪个版本的 SQL Server，但如果不使用企业版，则REBUILD操作是离线的，但REORGANIZE操作是在线的。

      进行在线操作不仅可以减少维护窗口的需要，而且可以在一周内更频繁地进行操作。您甚至可以根据碎片级别错开重建哪些索引，并可能选择前 N 个索引，REORGANIZE每晚选择一定数量的索引。

    2. 更难但更有效的途径：

      即使REORGANIZE偶尔的REBUILD计划有所帮助，您实际上仍然只是在用桶从有洞的船上舀水。但是您的系统正在增长，因此“漏洞”越来越大。所有这些 REBUILD / REORG 的东西都只是为了在糟糕的情况下生活。它可能会工作一段时间，甚至可能永远工作，但很容易就会出现它不起作用的时刻。绝对更好的方法是真正解决糟糕的情况，而不仅仅是掩盖它。

      绝对更好的方法是改造表以使用INT/BIGINT列作为集群 PK。如果应用程序代码使用 GUID，或者如果 GUID 为外部系统所知并因此被外部系统引用，那么您仍然可以进行此重大修改：只需将 GUID 值保留在一个表中，在该列上创建一个非聚集索引，然后在操作开始时查找这些值，将它们转换为内部INT/BIGINT值。这将缩小除少数表之外的所有表的大小（特别是考虑到非聚集索引在其中复制聚集索引键时，因此大多数非聚集索引比索引键的总和大 16 个字节，而它们可能只是如果 Clustered Key 是大 4 或 8 个字节INT或BIGINT, 分别）。较小的表在缓冲池中占用的空间较少，因此页面缓存时间更长，查询效率更高。备份和恢复操作也更快。索引 REORG 和 REBUILD 操作也更快。

    我之前在这个答案中向您建议了方法 #2： Best fill factor for GUID clustering key。因此，虽然方法 #1 可能有所帮助，但我认为在采用方法 #2 之前，您的情况不会有很大改善。而且我完全理解说起来容易做起来难，因为我不必处理时间/资源限制和其他复杂因素。但是，我要说的是，如果管理层不热衷于追求如此大的项目，他们需要考虑拥有更快系统的价值主张，在磁盘空间（数据文件、日志文件和备份），减少停机时间/维护窗口，减少重复支持时间，因为您需要每年至少“修复”一次这种不断恶化的情况，等等，等等。从长远来看，这种方法实际上是更便宜。这是提供最佳性能的最佳“维护计划”:-)。而且它可以分阶段完成，因此不必在一次大规模发布中完成。

    • 1

  2. 当一个表有一个 GUID 列作为 PK 时，通常意味着 SELECT 不是基于这个 GUID 列，这个 PK 主要是为了 FK 目的，以便与其他表连接。我推荐以下内容：

    1. 设置适当的监控（XEvents 或 trace）以检查页面拆分，如果页面拆分很多，请调整填充因子/pad_index 参数。否则，不需要更改。
    2. 如果可能，将 GUID 列从 PK 更改为唯一索引，并将另一个整数或类似列提升为 PK，这至少对应用程序是透明的，因此不会影响功能。
    3. 如果可能，至少将您的 GUID 列更改为默认的 newsequentialid()（如果当前不是）。
    4. 与 #2 相同，如果可能，将 GUID 保留为 PK，但不要在其上创建聚簇索引，而是在其他可能的列上选择聚簇索引。此更改对应用程序也是透明的，因此不应导致任何功能差异。
    • 0
    • 由于这是供应商数据库，您无法选择实施更改以使用NEWSEQUENTIALID或 INT/BIGINT
    • 不重新索引的副作用是数据库大小比其他方式更大
    • GUID 列上的键查找性能不太可能受到碎片的不利影响
    • 索引重建给您带来了什么问题？你为什么要停止做那个维护。是发送到可用性组副本的日志流量。索引重组将有助于这种情况
    • 如果您确实想停止重建并简单地更新统计信息。使用 Ola Hallengren 维护解决方案，尤其是示例D. 更新所有用户数据库的修改统计信息：

    EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
    • 0

相关问题