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

  • 项目

原始产品版本：SQL Server
原始 KB 编号： 315512

摘要

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

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

如何实现配置设置

  1. 可以使用下列方法之一配置或修改自动增长和自动收缩设置：

    注意

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

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

    若要查看当前设置，请运行以下 Transact-SQL 命令：

    sp_helpdb [ [ @dbname= ] 'name' ]

  2. 请记住，自动增长设置是每个文件。 因此，必须为每个数据库设置至少两个位置， (一个用于主数据文件，一个用于主日志文件) 。 如果有多个数据和/或日志文件，则必须在每个文件上设置选项。 根据环境，可能以每个数据库文件的不同设置结束。

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 大小增长之间的选择取决于环境中的许多因素。 可用于测试的一般经验法则是将自动增长设置设置为大约文件大小的 1-8。

  • 打开 \<MAXSIZE> 每个文件的设置，以防止任何一个文件增长到用尽所有可用磁盘空间的地步。

  • 尽可能减小事务大小，以防止计划外文件增长。

如果自动控制大小设置，我为什么要担心磁盘空间

  • 自动增长设置不能使数据库大小超出定义文件的驱动器上可用磁盘空间的限制。 因此，如果依赖于自动增长功能来调整数据库的大小，则仍必须独立检查可用硬盘空间。 自动增长设置还受 MAXSIZE 为每个文件选择的参数的限制。 若要减少空间不足的可能性，可以监视性能监视器计数器SQL Server：数据库对象：数据文件 () 大小 (KB) ，并在数据库达到特定大小时设置警报。

  • 数据或日志文件的意外增长可能会占用其他应用程序预期可用的空间，并可能导致其他应用程序遇到问题。

  • 事务日志的增长增量必须足够大，才能提前满足事务单位的需求。 即使启用了自动增长，如果事务日志的增长速度不足以满足查询的需求，你仍会收到一条消息，指出事务日志已满。

  • SQL Server不会持续测试已达到配置的自动收缩阈值的数据库。 相反，它会查看可用的数据库，并找到配置为自动收缩的第一个数据库。 它会检查该数据库，并根据需要收缩该数据库。 然后，它会等待几分钟，然后检查为自动收缩配置的下一个数据库。 换句话说，SQL Server不会一次性检查所有数据库，并且一次性收缩所有数据库。 它将以轮循机制方式通过数据库，在一段时间内错开负载。 因此，根据已配置为在特定SQL Server实例上自动收缩的数据库数，从数据库达到阈值到实际收缩可能需要几个小时。

References