SQL Server：高效维护数据库的关键技巧

SQL Server

高效维护数据库的关键技巧

Paul S. Randal

概览：

管理数据和事务日志文件
清除索引碎片
确保统计数据准确、最新
检测遭到破坏的数据库页
建立有效的备份策略

数据和日志文件管理
索引碎片
统计数据
损坏检测
备份
总结

在一周之内多次有人向我征求高效维护生产数据库的建议。有时问题来自 DBA，他们正在实施新的解决方案，希望得到帮助

对维护进行精细调整适合其新数据库的特点。但更为常见的情况是：提问的人不是专业 DBA，而是由于某种原因拥有数据库并承担相关责任的人员。我喜欢将这种角色称为“非自愿 DBA”。本文重点是为所有非自愿 DBA 提供数据库维护最佳实践的入门知识。

在 IT 世界里，大多数任务和程序都没有一个简单、通用的解决方案可以高效维护数据库，但却有一些必须受到重视的关键领域。我所关心的五大重要领域是（没有任何特殊的重要性顺序）：

数据和日志文件管理
索引碎片
统计数据
损坏检测
备份

一个未经维护（或维护不良）的数据库可能会在其中的一个或多个领域内引发问题，最终可能导致应用程序性能欠佳，甚至是停机以及丢失数据。

在本文中，我将说明这些问题很重要的原因并向您展示一些缓解这些问题的简单方法。我将以 SQL Server® 2005 为基础进行说明，但我还会着重指出您将会在 SQL Server 2000 和即将发布的 SQL Server 2008 中发现的主要差别。

数据和日志文件管理

我始终建议在接管数据库时检查的第一个领域涉及到与数据和（事务）日志文件管理相关的设置。具体地说，您应确保：

数据和日志文件彼此分开，而且还与其他所有内容相互隔离
自动增长已正确配置
即时文件初始化已配置
自动缩减未启用而且缩减不是任何维护计划的内容

当数据和日志文件（理想情况下应分别位于不同的卷中）与其他任何创建或扩展文件的应用程序共享一个卷时，可能存在文件碎片。在数据文件中，过多的文件碎片可能是导致查询（特别是扫描非常多数据的查询）效果不佳的一个因素。在日志文件中，它可能会对性能产生相当大的影响，尤其是在自动增长设置为需要增加每个文件的大小时，增量很小的情形。

日志文件在内部被划分为多个称为“虚拟日志文件”(VLF) 的片段，而且日志文件（我在这里使用单数是因为拥有多个日志文件并没有任何好处，每个数据库只应有一个日志文件）内的碎片越多，VLF 就越多。一个日志文件具有多个（比方说，200 个）VLF 后，与日志有关的操作（如为事务性复制/回滚而读取日志）、日志备份乃至 SQL Server 2000 中的触发器（触发器的实现已在 SQL Server 2005 中更改为行版本框架，而不是事务日志）可能会对性能产生负面影响。

调整数据和日志文件大小的最佳做法是创建它们时使用适当的初始大小。对于数据文件，初始大小应考虑短期内向数据库中添加其他数据的可能性。例如，如果数据的初始大小为 50GB，但您知道在接下来的六个月内将再添加 50GB 的数据，那么应创建 100GB 的数据文件，而不是多次将其增大以达到该大小。

对于日志文件而言要更复杂一些，您需要多考虑一些因素，例如事务大小（长时间运行事务在完成之前无法从日志中清除）以及日志备份频率（因为这将删除日志的非活动部分）。有关详细信息，请参阅我的妻子 Kimberly Tripp 编写的一篇很受欢迎的博客文章提高事务日志吞吐量的 8 个步骤，它发表在 SQLskills.com 上。

设置一旦完成，应不时监视文件大小，并在每一天的适当时间先行手动增加其大小。为以防万一，应保留自动增长，这样文件即使在发生一些异常事件的情况下仍可以完成所需的增长。反对将文件管理完全保留为自动增长的逻辑是步长极小的自动增长会导致文件碎片，而且自动增长会是一个耗时的过程，它可能会多次突然停止应用程序的工作。

应将自动增长大小设置为一个具体值，而不是一个百分比，以约束执行自动增长（如果发生）所需的时间和空间。例如，您可能希望将一个 100GB 的数据文件的自动增长大小设置为固定值 5GB，而不是（比方说）10%。这意味着无论文件每次变得多大，它均将按 5GB 进行增长，而不是一个持续增长的数量（10GB、11GB、12GB 等）。

当事务日志增长时（手动或自动增长），它将始终被初始化为零。数据文件在 SQL Server 2000 中具有同一默认行为，但从 SQL Server 2005 开始，您可以启用即时文件初始化，它会跳过零初始化文件，因此增长和自动增长会保持同步。所有版本的 SQL Server 中都提供了这一功能，这一点与正常的观点恰恰相佐。如欲了解详细信息，请在 SQL Server 2005 或 SQL Server 2008 的联机丛书索引中输入“即时文件初始化”。

最后，应注意不要以任何方式启用缩减。缩减可用于减小数据或日志文件的大小，但它是一个干扰很大、极耗资源的过程，会导致数据文件中产生大量的逻辑扫描碎片（详细信息请参见下文），并导致性能欠佳。我更改了 SQL Server 2005 联机丛书中的缩减条目，加入了一个警告，提醒注意此影响。但在特殊情况下，允许手动缩减单个数据和日志文件。

视频

观看 Paul Randal 演示缩减和自动缩减如何导致数据库发生严重的碎片问题。

自动缩减的后果极为严重，因为它每 30 分钟就会在背景中启动一次，并会尝试缩减自动缩减数据库选项被设置为 true 的数据库。从某种程度讲，它是一个无法预见的过程，因为它仅缩减拥有 25% 以上可用空间的数据库。自动缩减占用大量资源，会产生碎片并导致性能下降，因此在任何情况下都不是一个好计划。您应始终通过以下方式关闭自动缩减：

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

包含手动数据库缩减命令的定期维护计划几乎会产生同样糟糕的结果。如果您发现您的数据库在维护计划将其缩减后持续增长，原因在于数据库运行需要该空间。

最好的做法是允许数据库增长到稳定的大小，尽量避免运行缩减。您可以在我原来的 MSDN® 博客（位于 blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx）中找到有关使用缩减的缺点的详细信息，其中还有对 SQL Server 2005 中新算法的一些评论。

索引碎片

除了文件系统级和日志文件内的碎片以外，数据文件内存储表格和索引数据的结构中也可能存在碎片。数据文件内可能出现两种基本类型的碎片：

单个数据和索引页内的碎片（有时称为内部碎片）
由页面组成的索引或表格结构内的碎片（称为逻辑扫描碎片和扩展盘区扫描碎片）

内部碎片是页面中存在大量空白区域的位置。如图 1 所示，数据库中的每个页面大小为 8KB，页眉为 96 字节；因此，一个页面可以存储大约 8096 字节的表格或索引数据（我的博客 sqlskills.com/blogs/paul 有“深入了解存储引擎”一节，其中介绍了数据和行结构的特定表格和索引内部机制）。如果每个表格或索引记录超过页面大小的一半，可能会出现空白空间，因为每个页面只能存储一个记录。这可能很难或无法更正，因为它要求更改表格或索引架构，例如，将索引键更改为像 GUID 一样不会引发随机插入点。

图 1 数据库页的结构（单击图像可查看大图）

更常见的是，内部碎片源于数据修改（如插入、更新和删除），这可能会在页面中留下空白空间。管理不善的填充因子也可能会产生碎片；有关详细信息，请参阅“联机丛书”。根据表格/索引架构和应用程序的特征，此空白空间在其创建后可能就不再使用，导致数据库中的不可用空间量持续增长。

例如，我们来看一下一个 1 亿行表格，其中平均记录大小为 400 字节。后来，应用程序的数据修改模式为每个页面留下了平均 2800 字节的空白空间。该表格所需的总空间为 59GB，计算方法为 8096-2800 / 400 = 13 个记录/8KB 页面，然后将 1 亿除以 13 便可获得页面数。如果没有空间浪费，那么每个页面刚好容纳 20 条记录，所需的总空间下降至 38GB。这是多么大的节省！

如数据/索引页中出现空间浪费，可能导致存储同样数量的数据需要更多的页面。这不仅会占用更多的磁盘空间，还意味着查询需要执行更多的 I/O 才能读取同样数量的数据。所有这些多出的页面在数据缓存中占用了更多空间，因而占用了更多的服务器内存。

逻辑扫描碎片是由称为页面分隔的操作而引发的。当必须在特定索引页（根据索引键定义）中插入记录但页面中并没有足够的空间来容纳所插入的数据时，便会发生这种情况。该页面会被分割一半，大约 50% 的记录被移到新分配的页面。通常，这一新页面实际上并不与旧页面相邻，因此，被称为零碎的页面。扩展盘区扫描碎片在概念上与此类似。表格/索引结构内的碎片会影响 SQL Server 执行有效扫描的能力，无论是对整个表格/索引进行扫描还是按查询 WHERE 子句（例如，SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000）进行扫描都会受到影响。

图 2 显示的新建索引页填充率是 100%，没有碎片，这些页面完全被充满，而且页面的物理顺序与逻辑顺序相符。图 3 显示了在随机插入/更新/删除之后出现的碎片。

图 2 不带碎片的新建索引页；页面被完全填充（单击图像可查看大图）

图 3 随机插入、更新和删除之后出现内部和逻辑扫描碎片的索引页（单击图像可查看大图）

有时可以通过更改表格/索引架构来防止碎片，但正如我前面所提到的，这可能很难或根本无法实现。如果预防并不是办法，可以想办法在碎片产生后将其删除，具体来讲，是重新生成或重新组织索引。

重新生成索引涉及创建索引的新副本（有效压缩且尽可能连续），然后丢弃旧的零碎副本。在 SQL Server 删除旧的索引副本之前创建新副本时，在数据文件内所需的可用空间大致相当于此索引的大小。在 SQL Server 2000 中重新生成索引始终都是脱机进行的。在 SQL Server 2005 Enterprise Edition 中，索引重新生成可以联机进行，但有几个限制。而重新组织使用原位算法对索引进行压缩并整理碎片；它运行只需要 8KB 的额外空间，而且始终是联机运行的。实际上，在 SQL Server 2000 中，我专门编写了索引重新组织代码，用于替代重新生成索引，它的优点是联机且节省空间。

在 SQL Server 2005 中，用于调查的命令为 ALTER INDEX …… REBUILD 用于重新生成索引，ALTER INDEX … REORGANIZE 用于重新组织索引。此语法分别取代了 SQL Server 2000 中的命令 DBCC DBREINDEX 和 DBCC INDEXDEFRAG。

这些方法之间有许多种权衡选择，例如，所生成的事务记录量、所需的数据库可用空间量以及中断进程是否不会丢失数据等。您可以在 microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx 上找到一份白皮书，其中介绍了这些权衡选择以及更多信息。虽然白皮书内容是根据 SQL Server 2000 编写的，但其中的概念向后续版本的过渡性很好。

一些人只是选择每晚或每周重新生成或重新组织所有索引（例如，使用维护计划选项），而不是找出哪些索引被分割为碎片以及删除碎片是否会带来任何好处。对于那些只是希望不费多少气力就可适当放置内容的非自愿 DBA 来说，这是一个不错的解决方案，但请注意：对于一些资源非常珍贵的大型数据库或系统来说，它可是一个非常糟糕的选择。

更好的方法包括使用 DMV sys.dm_db_index_physical_stats（或 SQL Server 2000 中的 DBCC SHOWCONTIG）来定期确定哪些索引被分割为碎片，然后选择是否以及如何对其进行操作。本白皮书还介绍了对这些更有针对性的选项的使用。此外，您还可以看到一些进行这种筛选的示例代码，即 SQL Server 2005 联机丛书 DMV sys.dm_db_index_physical_stats 条目的示例 D (msdn.microsoft.com/library/ms188917) 或 SQL Server 2000 以及更新版本联机丛书 DBCC SHOWCONTIG 条目的示例 E (msdn.microsoft.com/library/aa258803)。

无论您使用哪种方法，定期调查并修复碎片都是非常明智的做法。

查询处理器是 SQL Server 的一个部件，用于决定应如何执行查询，具体来说，是使用哪些表格和索引以及对其执行哪些操作才能获得所需的结果；它称为查询计划。此决策制定过程所需的一些最重要的信息就是统计数据，它们说明表格或索引内各个列的数据值分步情况。很明显，统计数据必须要准确而且保持最新，才能为查询处理器提供帮助，否则可能会导致查询计划性能不佳。

统计数据是通过读取表格/索引数据并确定相关列的数据分布而生成的。可以通过扫描特定列的所有数据值（全面扫描）构建统计数据，也可以根据用户指定的数据百分比（采样扫描）建立统计数据。如果列中各值的分布非常均匀，那么采样扫描就应该足以能满足要求，与全面扫描相比，这种方式会使统计数据的创建和更新更快。

请注意，可通过打开 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 数据库选项自动创建和维护统计数据，如图 4 所示。这些选项默认情况下是打开的，但如果您只是继承了数据库，请进行检查加以确认。有时统计数据可能已过时，在这种情况下可以通过对特定的统计数据集使用 UPDATE STATISTICS 操作手动更新它们。或者，可以使用 sp_updatestats 存储过程，该过程会更新所有过时的统计数据（在 SQL Server 2000 中，sp_updatestats 更新所有统计数据，无论期限为何）。

图 4 通过 SQL Server Management Studio 更改数据库设置（单击图像可查看大图）

如果您想要在定期维护计划中更新统计数据，有一个问题您应当注意。UPDATE STATISTICS 和 sp_updatestats 均默认使用先前指定的采样级（如果有），这可能达不到全面扫描的效果。索引重新生成会自动使用全面扫描更新统计数据。如果您在重新生成索引之后手动更新统计数据，最后得到的统计数据可能不太准确！如果来自手动更新的采样扫描覆盖通过索引重新生成而产生的全面扫描，可能会发生这种情况。而另一方面，重新组织索引根本不更新统计数据。

同样，许多人的维护计划是在重新生成所有索引之前或之后的某个时刻更新所有统计数据，因此可能不知道得到的是不太准确的统计数据。如果您经常做出的选择只是重新生成所有索引，这也会处理统计数据。如果您选择了更为复杂的方式，其中涉及删除碎片，您也应该执行此操作以维护统计数据。以下是我的一些建议：

分析索引并确定要对哪些索引进行操作以及如何删除碎片。
对于尚未重新生成的所有索引，更新统计数据。
更新所有非索引列的统计数据。

有关统计数据的详细信息，请参阅白皮书“® SQL Server 2005 中的查询优化器所使用的统计信息”(microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx)。

损坏检测

我已经介绍了与性能相关的维护。现在我想换一项内容，介绍一下损坏检测与缓解。

您所管理的数据库一定会包含有用的信息，那么您如何确保数据在出现灾难时没有损坏或可以恢复呢？详细论述完整的灾难恢复和高可用性策略超出了本文的范围，但可以介绍一些简单的操作。

绝大多数的损坏是由“硬件”引起的。为什么我用引号将其引起来呢？因为此处的硬件实际上是指“SQL Server 下属 I/O 子系统中的某些内容”。I/O 子系统由操作系统、文件系统驱动程序、设备驱动程序、RAID 控制器、电缆、网络以及物理磁盘驱动器等组成。有许多地方确实会发生问题。

一个最常见的问题是当发生电源故障时磁盘驱动器正在写出数据库页。如果驱动器无法在电源耗尽之前完成写操作（或者写操作已缓存，但没有足够的备用电池来刷新驱动器的缓存），就可能在磁盘上产生不完整的页面映像。因为 8KB 数据库页实际上由 16 个连续的 512 字节扇区组成，所以这种情况可能会发生。不完整的写操作可能写出新页面中的一些扇区，但也会留下上一页面映像中的一些扇区。这种情况称为破损页。如果发生了这一情况，应如何检测呢？

SQL Server 提供了一种用于检测此情况的机制。它包括存储该页面每个扇区的几位并在其位置编写特定的模式（这恰好是在页面被写入磁盘之前发生的）。如果重新读取页面时此模式有变化，SQL Server 便会知道该页面已“破损”并引发错误。

在 SQL Server 2005 及后续版本中，提供了一种更加全面的机制，称为页面校验和，可以检查页中的任何损坏。这包括在写出页面之前编写整页校验和，然后在重新读取该页时对其进行检测，就象检测破损页一样。在启用页面校验和之后，必须将页读入缓冲池，以某种方式进行更改，然后在其受页面校验和保护之前将其重新写出到磁盘。

因此，最好的做法是为 SQL Server 2005 之后的版本启用页面校验和，为 SQL Server 2000 启用破损页检测。要启用页面校验和，请使用：

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;

要为 SQL Server 2000 启用破损页检测，请使用：

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;

通过这些机制，您可以在某个页面出现损坏时进行检测，但只能在读取页面时进行。如何能够便于强制读取所有分配的页面？执行此操作（以及查找其他任何类型的损坏）的最好方法是使用 DBCC CHECKDB 命令。无论指定的选项为何，此命令始终都会读取数据库中的所有页面，从而导致验证所有页面校验和或破损页检测。您还应该设置警报，这样您就可了解用户在运行查询时何时遇到了损坏问题。您可以使用 24 个严重性错误警报得到上述所有问题的通知（图 5）。

图 5 为所有 24 个严重性错误设置警报（单击图像可查看大图）

因此，另一种最佳做法是定期对数据库运行 DBCC CHECKDB 以验证其完整性。此命令有许多形式，其运行频率也有很多说法。遗憾的是，还没有任何对此做介绍的白皮书。但是，因为 DBCC CHECKDB 是我为 SQL Server 2005 编写的主要代码段，因此我已在博客中对此做了详尽的说明。请参阅我博客中的 "CHECKDB From Every Angle" (sqlskills.com/blogs/paul)，里面有许多有关一致性检查、最佳实践以及方法建议的精深文章。对于非自愿 DBA，一条经验法则是在每次进行完整数据库备份时都运行 DBCC CHECKDB（更多相关信息请参见下文）。我建议运行以下命令：

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, 
  ALL_ERRORMSGS;

如果此命令有任何输出，表明 DBCC 已在数据库中发现了一些损坏。随之而来的就是如果 DBCC CHECKDB 发现了任何损坏应如何处理。这时就轮到备份出场了。

当发生损坏或其他灾难时，最有效的恢复方法是从备份还原数据库。此方法假定您第一时间进行了备份，而且这些备份本身没有损坏。人们经常想知道在其没有做任何备份的情况下如何让严重损坏的数据库重新运行起来。如果某种形式的数据丢失对您的业务逻辑和关系数据完整性造成了严重破坏，这一点根本做不到。

因此，完全有理由主张进行定期备份。使用备份和还原的细节远远超出了本文的范围，但我可以简要介绍一些如何建立备份策略的基础知识。

首先，应定期进行完整数据库备份。这会为您提供一个之后还原可以达到的时间点。可以使用 BACKUP DATABASE 命令进行完整数据库备份。相关示例请参阅“联机丛书”。若要增加保护，可以使用 WITH CHECKSUM 选项，它会验证所读取页的页面校验和（如果有）并计算整个备份的校验和。您应选择一个频率，它会反映您的业务能容忍的数据或工作丢失量。例如，每天进行一次完整数据库备份，意味着在发生灾难的情况下您最多可能丢失一天的数据。如果您仅使用完整数据库备份，应采用 SIMPLE 恢复模型（通常称为恢复模式），以避免与事务日志增长管理相关的复杂性。

其次，始终保留几天的备份，如果发生了损坏，使用几天之前的备份也要好于根本没有备份。您还应使用 RESTORE WITH VERIFYONLY 命令验证备份的完整性（仍请参阅“联机丛书”）。如果您在创建备份时使用了 WITH CHECKSUM 选项，运行验证命令将检查备份校验和是否仍然有效，并重新检查备份内的所有页面校验和。

第三，如果每日完整数据库备份不满足业务所能承受的最大数据/工作丢失量，您可试用差异数据库备份。差异数据库备份以完整数据库备份为基础，并包含自上次完整数据库备份后所有更改的记录（一个常见误解是差异备份是增量备份，其实不然）。一个示例策略是采用每日完整数据库备份，并每四个小时进行一次差异数据库备份。差异备份额外提供了一个时间点恢复选项。如果您仅使用完整数据库备份和差异数据库备份，仍应使用 SIMPLE 恢复模型。

最后，可恢复性最终涉及的是使用日志备份。它们仅在 FULL（或 BULK_LOGGED）恢复模型中可用，提供了自上次日志备份后所生成的所有日志记录的备份。使用定期完整数据库（也可能是差异数据库）备份维护一组日志备份会提供无限数量的恢复目标时间点（包括最新的恢复）。需要权衡的是事务日志将继续增长，直到通过日志备份将其释放。此处的示例策略将每天进行完整数据库备份，每四个小时进行一次差异数据库备份并在每半小时进行一次日志备份。

决定备份策略并对其进行设置可能会相当复杂。最起码，您应定期进行完整数据库备份，以确保您至少拥有一个可从中恢复的时间点。

正如您所看到的，要确保数据库保持良好状况并可供使用，必须要完成几项任务。以下是我制订的最终检查表，适合接管数据库的非自愿 DBA：

删除多余的事务日志文件碎片。
正确设置自动增长。
关闭所有计划的缩减操作。
打开即时文件初始化。
定期检测并删除索引碎片。
打开 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS，并定期更新统计数据。
启动页面校验和（或者至少在 SQL Server 2000 上启动破损页检测）。
定期运行 DBCC CHECKDB。
定期进行完整数据库备份以及用于时间点恢复的差异和日志备份。

我在本文中给出了 T-SQL 命令，但您也可以利用 Management Studio 完成诸多操作。希望我的建议能帮您提高数据库的维护效率。如果您有任何反馈或问题，请发送电子邮件至 paul@sqlskills.com。