SQL Server 中的自动增长和自动收缩设置的注意事项

项目
12/14/2024

原始产品版本：SQL Server
原始 KB 数： 315512

总结

默认的自动增长和自动收缩设置适用于许多 SQL Server 系统。但在某些环境中，可能需要调整自动增长和自动收缩参数。本文提供了一些背景信息，用于指导何时为环境选择这些设置。

如果决定优化自动增长和自动收缩参数，请考虑以下事项。

如何实现配置设置

可以使用下列选项之一来配置或修改自动增长和自动收缩设置：
- SQL Server Management Studio
- ALTER DATABASE 语句
  - 使用文件和文件组选项修改自动增长设置
  - 使用 SET 选项配置 AUTO_SHRINK 设置。
注意

有关如何在数据库文件级别设置这些设置的详细信息，请参阅向数据库添加数据或日志文件。

还可以在创建数据库时配置自动增长选项。

若要查看当前设置，请运行以下 Transact-SQL 命令：
```
sp_helpdb [ [ @dbname= ] 'name' ]
```
请记住，自动增长设置是每个文件。因此，必须为每个数据库设置至少两个位置（一个用于主数据文件，一个用于主日志文件）。如果有多个数据和/或日志文件，则必须为每个文件设置选项。根据环境，可以为每个数据库文件使用不同的设置结束。

AUTO_SHRINK注意事项

AUTO_SHRINK 是 SQL Server 中的数据库选项。为数据库启用此选项时，此数据库有资格通过后台任务收缩。此后台任务评估满足收缩和收缩数据或日志文件的条件的所有数据库。

必须仔细评估为 SQL Server 实例中的数据库设置此选项。频繁增长和收缩操作可能会导致各种性能问题。

如果多个数据库频繁收缩和增长操作，则这很容易导致文件系统级别的碎片。这可能会影响性能。无论是使用自动设置还是手动增长和收缩文件，都是如此。
成功收缩数据或日志文件后 AUTO_SHRINK ，如果需要空间且文件需要增长，后续 DML 或 DDL 操作可能会显著降低速度。
当需要收缩的许多数据库时， AUTO_SHRINK 后台任务可能会占用资源。
后台 AUTO_SHRINK 任务需要获取与其他常规应用程序活动冲突的锁和其他同步。

请考虑将数据库设置为所需大小并预先扩展数据库。如果认为应用程序使用模式需要它们，请将数据库文件中未使用的空间留出。这可以防止数据库文件的频繁收缩和增长。

AUTOGROW 注意事项

如果运行的事务需要的日志空间比可用空间多，并且你已为该数据库的事务日志启用自动增长选项，则事务完成所需的时间将包括事务日志按配置的量增长所需的时间。如果增长增量很大，或者有一些其他因素导致它花费很长时间，则打开事务的查询可能会因为超时错误而失败。同一类问题可能是数据库数据部分的自动增长造成的。
如果运行需要日志增长的大型事务，则其他需要写入事务日志的事务也必须等到增长操作完成。
如果日志文件中存在许多文件增长，则可能具有过多的虚拟日志文件（VLF）。这可能会导致数据库启动/联机操作、复制、镜像和更改数据捕获（CDC）出现性能问题。此外，这有时可能会导致数据修改的性能问题。

注意

如果合并了自动增长和自动收缩选项，则可能会产生不必要的开销。确保触发增长和收缩操作的阈值不会导致频繁的上下大小更改。例如，可以运行导致事务日志在提交时增长 100 MB 的事务。一段时间后，自动收缩将事务日志启动并收缩 100 MB。然后，运行相同的事务，并导致事务日志再次增长 100 MB。在该示例中，你将创建不必要的开销，并可能创建日志文件的碎片，这两者都可能会对性能产生负面影响。

如果按小增量增长数据库，或者如果增大数据库，然后收缩数据库，则最终可能会有磁盘碎片。在某些情况下，磁盘碎片可能会导致性能问题。小型增长增量的方案还可以降低系统上的性能。

在 SQL Server 中，可以启用即时文件初始化。即时文件初始化仅加快数据文件的文件分配速度。即时文件初始化不适用于日志文件。有关详细信息，请参阅数据库即时文件初始化。

自动增长和自动收缩的最佳做法

对于托管的生产系统，必须考虑自动增长只是意外增长的应变。自动增长不管理数据和记录每天的增长。
可以使用警报或监视程序来监视文件大小并主动增长文件。这有助于避免碎片，并允许你将这些维护活动转移到非高峰时段。
训练的数据库管理员（DBA）必须仔细评估自动收缩和自动增长;它们不得不受管理。
自动增长增量必须足够大，以避免上一节中列出的性能损失。在配置设置中使用的确切值以及百分比增长与特定 MB 大小增长之间的选择取决于环境中的许多因素。可用于测试的一般经验法则是将自动增长设置设置为大约 18 个文件大小。
\<MAXSIZE>打开每个文件的设置，以防止任何一个文件增长到它占用所有可用磁盘空间的点。
尽可能减小事务的大小，以防止计划外的文件增长。

如果自动控制大小设置，为何必须担心磁盘空间

自动增长设置不能将数据库大小超出定义文件的驱动器上可用磁盘空间的限制。因此，如果依赖于自动增长功能来调整数据库的大小，则仍必须独立检查可用的硬盘空间。自动增长设置也受 MAXSIZE 为每个文件选择的参数的限制。为了减少空间不足的可能性，可以监视性能监视器计数器 SQL Server：数据库对象：数据文件（KB）大小，并在数据库达到特定大小时设置警报。
数据或日志文件的计划外增长可能会占用其他应用程序预期可用的空间，并可能导致其他应用程序遇到问题。
事务日志的增长增量必须足够大，才能提前满足事务单位的需求。即使启用自动增长，也可以收到一条消息，指出事务日志已满（如果增长速度不够快以满足查询需求）。
SQL Server 不会持续测试已达到自动收缩配置的阈值的数据库。而是查看可用的数据库，并查找配置为自动收缩的第一个数据库。它会检查数据库并根据需要收缩该数据库。然后，它会等待几分钟，然后检查为 autoshrink 配置的下一个数据库。换句话说，SQL Server 不会一次性检查所有数据库，并一次性收缩所有数据库。它将以轮循机制方式通过数据库，在一段时间内错开负载。因此，根据已配置为在特定 SQL Server 实例上自动收缩的数据库数，数据库在达到阈值之前可能需要几个小时才能真正收缩。

通过