何时将数据库收缩是合适的？

Question

何时将数据库收缩是合适的？

48

我知道收缩是魔鬼：它颠倒页面顺序，并导致皮肤癌、数据碎片化和全球变暖。问题还在继续... 话虽如此，假设我有一个100 GB的数据库，我删除了50 GB的数据——不是在一个表上，而是在数据库整体层面上对旧数据进行了普遍修剪，涵盖了90%的表——这是否构成了适合收缩数据库的用例？

如果不是，从数据库中删除如此高比例的数据后，应采取哪些适当的步骤来清理空间？我可以想到两个步骤：重建索引和更新统计信息。还有其他什么？

- bumble_bee_tuna

5个回答

16

重新组织和缩小是不被推荐的。

如果你可以将数据库正在服务的应用程序下线，你可以在收缩之前移除所有非聚集索引（这可能意味着删除依赖非聚集索引的外键约束），以加快过程并减少索引碎片化（这将意味着要移动的数据较少，因为只有数据页会被重新排序，而现在不存在的索引页则不会被重新排序，从而加快过程），然后重新创建所有索引和键。

在收缩之后重新创建索引意味着它们不应该出现显著的碎片，而在收缩期间没有索引意味着重建它们不会在文件中留下许多小的"空洞"，这可能会导致以后的碎片化。

如果你可以将应用程序下线，另一个选择是将所有数据迁移到具有相同结构的新数据库。如果你的构建过程可靠，你应该能够快速构建空白的数据库；否则，可以从当前数据库创建一个（还原当前数据库的备份，清空/删除表中的所有内容，并执行完整收缩）。

你可能还想在目标数据库中删除所有索引，并在之后重新创建它们，因为当改变大量索引数据时（在这种情况下是100%的数据），这样做会更高效。为了加快复制过程的速度，将目标数据库的数据文件放在与源数据库不同的物理驱动器上（除非您使用SSD，那么您就不需要关心减少磁头移动），完成后可以将它们移回源位置。另外，如果将目标数据库创建为新数据库（而不是通过清空源数据库的副本），请使用一个初始大小，足以容纳当前所有数据以及几个月的增长量 - 这样做将使数据复制过程再次加快，因为它不会在整个过程中不断分配新空间。这可能比使用收缩操作更好，因为将数据迁移到全新的数据库会复制收缩操作的预期效果，但可能会产生更少的碎片化（这是重新组织和收缩的意外后果）。收缩操作只是从文件末尾附近取出块并将其放置在靠近开头的第一个空间中，没有努力保持相关数据在一起。我怀疑结果在空间方面也会更有效率，因为之后可能会有更少的部分使用页面。缩小尺寸只是将部分使用页面移动，而将数据移动更有可能导致完整页面，特别是如果按表的聚集键/索引（如果表有）的顺序插入到目标位置，并在数据全部迁移后创建其他索引。另一个比迁移到新数据库更现场化的选择是创建一个新的文件组，并将所有数据迁移到其中，如https://stackoverflow.com/a/2438060/114292中所讨论的那样 - 一旦所有数据和非聚集索引都被移动，基本文件组的文件可以缩小到非常小的尺寸，因为它们几乎为空，而新的文件组应该在空间上相当有组织。当然，如果您无法完全离线运行应用程序，仅执行收缩操作是您唯一的选择，所以如果您真的需要回收空间，请选择这种方式。根据您的数据、访问模式、常见工作集大小、服务器的RAM量等因素，最后额外的内部碎片可能并不那么重要。对于复制操作，无论是使用SSIS还是基本的T-SQL都可以（SSIS选项可能效率较低，但后期维护可能更容易）。如果您在最后创建了外键关系和索引，无论哪种情况下，都可以简单地进行“对每个表进行复制”。当然，对于一次性操作，收缩+重组也可能是可以接受的，但我只是喜欢吓唬人们，让他们永远不要考虑定期收缩！（我知道有些人每天都安排这样的操作）。对于将数据复制到新数据库以有效地收缩和重组数据的进一步选择，在最初提出这个问题时，这在除了新兴的Azure SQL之外并不常见，即将整个数据库导出为bacpac文件并进行恢复。恢复操作会创建数据库架构，批量加载数据，然后重新创建非聚集索引。这种方法可能比上述方法花费更长的时间，但需要您进行的手动干预或脚本编写较少，因此您可以更加确信它会正常工作。

更新至2023年：随着现代存储系统主要基于固态设备，并具有大量用于进一步缓存的RAM（存储设备上的缓存，SQL本身中的缓冲池），页面位置方面的碎片化问题已经不再是一个很大的关注点，这可能改变了建议的平衡。关于拥有很多部分使用的页面方面的碎片化仍然需要考虑（您会使用更多的缓冲池来存储相同的数据，并且需要更多的IO来读取或更新相同数量的数据），只能通过索引重建而非重新组织来修复，同样适用于由于删除数据而产生大量空闲空间（这是该问题中的原始问题）。在此背景下，现在更容易推荐进行一次性收缩。

- David Spillett

3

如果你的空间不足，并且数据不应该变得那么大，那么可以进行缩小操作，但在此之后要重建索引，并使用合适的填充因子以适应典型的增长。

如果你的最终目标是减小备份文件的大小，请确保实施全面的备份策略来清除事务日志，并在备份数据库时使用压缩选项。

我不建议自动增长5GB，除非你经常需要增长5GB。否则，你可能会遇到间歇性的性能问题。你应该首先将数据大小设置为你认为在一年内所需的大小，并将自动增长设置为一个你已经测试过不会影响操作性能的大小。参见Mike Walsh的《Don't Touch that Shrink Database Button In SQL Server!》。在收缩之前重建索引会导致索引布局不良。重建后再收缩是不好的做法。收缩会导致索引被破坏以恢复空间，因此事先重建再收缩是没有意义的。参见Thomas LaRock的《When to use Auto Shrink》。

- GilesDMiddleton

如果您在缩小索引之后重新构建索引，数据文件将不得不再次增长以容纳用于重建的数据副本。虽然在这种情况下它不会像原始数据文件那样大，但仍然会有增长，这似乎是逆向效果。在有空闲空间时进行重建将更快（无需自动增长），并且通常仍然比您所建议的更好，因为它会更好地布局新索引副本的页面，并且我怀疑在大多数情况下，这将导致整体上更短的时间和相同或更好的磁盘空间恢复。也许是时候进行一些测试了。 - Aaron Bertrand

当然，这是基于剩下的数据索引实际上需要重建的假设——也许它们已经处于相当良好的状态。 - Aaron Bertrand

2

回到这个问题上来已经晚了。不过，我们一直在考虑和测试在我们的测试环境中使用收缩的方法已经很长时间了。根据这个话题，有时候收缩是一个可行的选择。但是知道何时以及如何应用它对于长期和短期的正确执行都是至关重要的。在我们的情况下，我们最近对我们的大型数据库进行了许多更改，包括压缩、分区、归档和删除冗余数据。结果，我们主要数据文件的已使用部分减少到了原来的不到一半。但是携带着那么多的负担有什么意义呢？特别是因为与网络上的一些文章相反，数据文件的大小与备份/恢复持续时间直接相关。这是因为与许多文章假设的不同，现实生活中的场景在任何给定页面上都有比你可能删除的东西更多的数据。更重要的是，这为收缩开辟了一个很好的场景。

创建一个脚本，可以在你的数据库中找到所有对象及其文件组（网上有很多示例），使用此脚本创建删除语句，并为每个索引和约束创建定义。
创建一个新的文件和文件组，并将其设为默认。
删除所有非聚集索引（注意，某些索引可能是约束）。
使用 DROP_EXISTING = ON 在新的文件组上创建聚集索引（顺便说一下，与许多其他方法相比，这是一个非常快速、最小日志记录的操作）。
重新创建非聚集索引。
最后，收缩旧的数据文件（通常是 PRIMARY）。

这样，其中只会保留数据库的系统对象、统计信息、过程等数据。收缩操作应该会快得多，而且对于主要数据对象来说，不再需要进行任何进一步的索引维护，这些对象已经按照顺序创建，未来碎片化的风险也很小。

- Kahn

1

我不知道这个方法是否比缩小后重新索引更好，但另一个选择是创建一个适当大小的新数据文件，并将所有数据移动到其中。在这种情况下，我会先重新索引，以便您知道实际数据大小。有一个问题是，如果这是主数据文件中的第一个文件，我认为您无法清空它。您应该能够缩小它，然后再将数据移回去，这样可以避免页面反转。然而，如果您考虑迁移到固态硬盘，那么这应该没有太大区别。

- cfradenburg

- Aaron Bertrand · Accepted Answer

数据库会再次增长吗？如果是这样的话，你所做的收缩操作将只是浪费，因为当你将文件大小减小后再添加更多数据，文件将不得不再次增长，而事务必须等待该增长发生。如果你的自动增长设置不够优化，或者驱动器速度较慢，这种增长活动将非常痛苦。如果你确实要收缩数据库，那么你将利用释放出来的磁盘空间做什么呢？如果你只是为了以防万一数据库再次增长而保持该空间空闲，那么你只是在白白浪费时间。现在你有了这么多的文件空闲空间，你可以考虑重新构建索引，使其更好地优化（而且在有空闲空间时进行这样的操作会少很多痛苦——想象一下在一个小衣柜里换毛衣和在一个大卧室里换毛衣的区别）。所以，除非这是一次重大的清理操作，并且你真的不会再次增加相同级别的数据，否则我建议保持原样，并专注于其他优化领域。