搜索
概览：管理数据和事务日志文件清除索引碎片确保统计数据准确、最新检测遭到破坏的数据库页建立有效的备份策略目录数据和日志文件管理索引碎片统计数据损坏检测备份总结在一周之内多次有人向我征求高效维护生产数据库的建议。有时问题来自DBA，他们正在实施新的解决方案，希望得到帮助对维护进行精细调整适合其新数据库的特点。但更为常见的情况是：提问的人不是专业DBA，而是由于某种原因拥有数据库并承担相关责任的人员。我喜欢将这种角色称为“非自愿DBA”。本文重点是为所有非自愿DBA提供数据库维护最佳实践的入门知识。在IT世界里，大多数任务和程序都没有一个简单、通用的解决方案可以高效维护数据库，但却有一些必须受到重视的关键领域。我所关心的五大重要领域是（没有任何特殊的重要性顺序）：数据和日志文件管理索引碎片统计数据损坏检测备份一个未经维护（或维护不良）的数据库可能会在其中的一个或多个领域内引发问题，最终可能导致应用程序性能欠佳，甚至是停机以及丢失数据。在本文中，我将说明这些问题很重要的原因并向您展示一些缓解这些问题的简单方法。我将以SQLServer®2005为基础进行说明，但我还会着重指出您将会在SQLServer2000和即将发布的SQLServer2008中发现的主要差别。数据和日志文件管理我始终建议在接管数据库时检查的第一个领域涉及到与数据和（事务）日志文件管理相关的设置。具体地说，您应确保：数据和日志文件彼此分开，而且还与其他所有内容相互隔离自动增长已正确配置即时文件初始化已配置自动缩减未启用而且缩减不是任何维护计划的内容当数据和日志文件（理想情况下应分别位于不同的卷中）与其他任何创建或扩展文件的应用程序共享一个卷时，可能存在文件碎片。在数据文件中，过多的文件碎片可能是导致查询（特别是扫描非常多数据的查询）效果不佳的一个因素。在日志文件中，它可能会对性能产生相当大的影响，尤其是在自动增长设置为需要增加每个文件的大小时，增量很小的情形。日志文件在内部被划分为多个称为“虚拟日志文件”(VLF)的片段，而且日志文件（我在这里使用单数是因为拥有多个日志文件并没有任何好处，每个数据库只应有一个日志文件）内的碎片越多，VLF就越多。一个日志文件具有多个（比方说，200个）VLF后，与日志有关的操作（如为事务性复制/回滚而读取日志）、日志备份乃至SQLServer2000中的触发器（触发器的实现已在SQLServer2005中更改为行版本框架，而不是事务日志）可能会对性能产生负面影响。调整数据和日志文件大小的最佳做法是创建它们时使用适当的初始大小。对于数据文件，初始大小应考虑短期内向数据库中添加其他数据的可能性。例如，如果数据的初始大小为50GB，但您知道在接下来的六个月内将再添加50GB的数据，那么应创建100GB的数据文件，而不是多次将其增大以达到该大小。对于日志文件而言要更复杂一些，您需要多考虑一些因素，例如事务大小（长时间运行事务在完成之前无法从日志中清除）以及日志备份频率（因为这将删除日志的非活动部分）。有关详细信息，请参阅我的妻子KimberlyTripp编写的一篇很受欢迎的博客文章提高事务日志吞吐量的8个步骤，它发表在SQLskills.com上。设置一旦完成，应不时监视文件大小，并在每一天的适当时间先行手动增加其大小。为以防万一，应保留自动增长，这样文件即使在发生一些异常事件的情况下仍可以完成所需的增长。反对将文件管理完全保留为自动增长的逻辑是步长极小的自动增长会导致文件碎片，而且自动增长会是一个耗时的过程，它可能会多次突然停止应用程序的工作。应将自动增长大小设置为一个具体值，而不是一个百分比，以约束执行自动增长（如果发生）所需的时间和空间。例如，您可能希望将一个100GB的数据文件的自动增长大小设置为固定值5GB，而不是（比方说）10%。这意味着无论文件每次变得多大，它均将按5GB进行增长，而不是一个持续增长的数量（10GB、11GB、12GB等）。当事务日志增长时（手动或自动增长），它将始终被初始化为零。数据文件在SQLServer2000中具有同一默认行为，但从SQLServer2005开始，您可以启用即时文件初始化，它会跳过零初始化文件，因此增长和自动增长会保持同步。所有版本的SQLServer中都提供了这一功能，这一点与正常的观点恰恰相佐。如欲了解详细信息，请在SQLServer2005或SQLServer2008的联机丛书索引中输入“即时文件初始化”。最后，应注意不要以任何方式启用缩减。缩减可用于减小数据或日志文件的大小，但它是一个干扰很大、极耗资源的过程，会导致数据文件中产生大量的逻辑扫描碎片（详细信息请参见下文），并导致性能欠佳。我更改了SQLServer2005联机丛书中的缩减条目，加入了一个警告，提醒注意此影响。但在特殊情况下，允许手动缩减单个数据和日志文件。视频观看PaulRandal演示缩减和自动缩减如何导致数据库发生严重的碎片问题。自动缩减的后果极为严重，因为它每30分钟就会在背景中启动一次，并会尝试缩减自动缩减数据库选项被设置为true的数据库。从某种程度讲，它是一个无法预见的过程，因为它仅缩减拥有25%以上可用空间的数据库。自动缩减占用大量资源，会产生碎片并导致性能下降，因此在任何情况下都不是一个好计划。您应始终通过以下方式关闭自动缩减：ALTERDATABASEMyDatabaseSETAUTO_SHRINKOFF;包含手动数据库缩减命令的定期维护计划几乎会产生同样糟糕的结果。如果您发现您的数据库在维护计划将其缩减后持续增长，原因在于数据库运行需要该空间。最好的做法是允许数据库增长到稳定的大小，尽量避免运行缩减。您可以在我原来的MSDN®博客（位于blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx）中找到有关使用缩减的缺点的详细信息，其中还有对SQLServer2005中新算法的一些评论。索引碎片除了文件系统级和日志文件内的碎片以外，数据文件内存储表格和索引数据的结构中也可能存在碎片。数据文件内可能出现两种基本类型的碎片：单个数据和索引页内的碎片（有时称为内部碎片）由页面组成的索引或表格结构内的碎片（称为逻辑扫描碎片和扩展盘区扫描碎片）内部碎片是页面中存在大量空白区域的位置。如图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%的记录被移到新分配的页面。通常，这一新页面实际上并不与旧页面相邻，因此，被称为零碎的页面。扩展盘区扫描碎片在概念上与此类似。表格/索引结构内的碎片会影响SQLServer执行有效扫描的能力，无论是对整个表格/索引进行扫描还是按查询WHERE子句（例如，SELECT*FROMMyTableWHEREColumn1100ANDColumn14000）进行扫描都会受到影响。图2显示的新建索引页填充率是100%，没有碎片，这些页面完全被充满，而且页面的物理顺序与逻辑顺序相符。图3显示了在随机插入/更新/删除之后出现的碎片。图2不带碎片的新建索引页；页面被完全填充（单击图像可查看大图）图3随机插入、更新和删除之后出现内部和逻辑扫描碎片的索引页（单击图像可查看大图）有时可以通过更改表格/索引架构来防止碎片，但正如我前面所提到的，这可能很难或根本无法实现。如果预防并不是办法，可以想办法在碎片产生后将其删除，具体来讲，是重新生成或重新组织索引。重新生成索引涉及创建索引的新副本（有效压缩且尽可能连续），然后丢弃旧的零碎副本。在SQLServer删除旧的索引副本之前创建新副本时，在数据文件内所需的可用空间大致相当于此索引的大小。在SQLServer2000中重新生成索引始终都是脱机进行的。在SQLServer2005EnterpriseEdition中，索引重新生成可以联机进行，但有几个限制。而重新组织使用原位算法对索引进行压缩并整理碎片；它运行只需要8KB的额外空间，而且始终是联机运行的。实际上，在SQLServer2000中，我专门编写了索引重新组织代码，用于替代重新生成索引，它的优点是联机且节省空间。在SQLServer2005中，用于调查的命令为ALTERINDEX……REBUILD用于重新生成索引，ALTERINDEX…REORGANIZE用于重新组织索引。此语法分别取代了SQLServer2000中的命令DBCCDBREINDEX和DBCCINDEXDEFRAG。这些方法之间有许多种权衡选择，例如，所生成的事务记录量、所需的数据库可用空间量以及中断进程是否不会丢失数据等。您可以在microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx上找到一份白皮书，其中介绍了这些权衡选择以及更多信息。虽然白皮书内容是根据SQLServer2000编写的，但其中的概念向后续版本的过渡性很好。一些人只是选择每晚或每周重新生成或重新组织所有索引（例如，使用维护计划选项），而不是找出哪些索引被分割为碎片以及删除碎片是否会带来任何好处。对于那些只是希望不费多少气力就可适当放置内容的非自愿DBA来说，这是一个不错的解决方案，但请注意：对于一些资源非常珍贵的大型数据库或系统来说，它可是一个非常糟糕的选择。更好的方法包括使用DMVsys.dm_db_index_physical_stats（或SQLServer2000中的DBCCSHOWCONTIG）来定期确定哪些索引被分割为碎片，然后选择是否以及如何对其进行操作。本白皮书还介绍了对这些更有针对性的选项的使用。此外，您还可以看到一些进行这种筛选的示例代码，即SQLServer2005联机丛书DMVsys.dm_db_index_physical_stats条目的示例D(msdn.microsoft.com/-library/ms188917)或SQLServer2000以及更新版本联机丛书DBCCSHOWCONTIG条目的示例E(msdn.microsoft.com/library/aa258803)。无论您使用哪种方法，定期调查并修复碎片都是非常明智的做法。查询处理器是SQLServer的一个部件，用于决定应如何