☰

SQL 数据库物理文件碎片

tag-icon • tag-icon sql-server fragmentation
SQL 数据库物理文件碎片

我知道确实有三作为 DBA，我需要关注以下类型的碎片：

  1. 指数SQL 数据文件中的碎片，包括聚集索引（表）碎片。使用 DBCC SHOWCONTIG（在 SQL 2000 中）或 sys.dm_ db_ index_ physical_ stats（在 2005 及以上版本中）识别此碎片。

  2. VLF 碎片在 SQL 日志文件中。运行 DBCC LOGINFO 以查看每个 SQL 日志文件中有多少个 VLF。

  3. 硬盘上的数据库文件的物理文件碎片。使用 Windows 中的“磁盘碎片整理程序”实用程序来诊断此问题。（灵感来自这篇优秀的博客文章）

索引碎片化问题备受关注（参见这个优秀的 Serverfault 答案来自 Paul Randall)，所以这不是我的问题的重点。

我知道我可以防止物理碎片（和 VLF 碎片），当数据库最初创建时，通过规划合理的预期数据文件和日志大小，因为这种碎片最常发生在频繁的增长和收缩中，但我对如何使固定一旦识别出物理碎片：

  • 首先，物理碎片在企业 SAN 上是否重要？我是否可以/应该在 SAN 驱动器上使用 Windows 碎片整理程序，还是 SAN 团队应该使用内部碎片整理实用程序？我从 Windows 工具获得的碎片分析是否重要？准确的何时在 SAN 驱动器上运行？

  • 物理碎片对 SQL 性能的影响有多大？（我们假设一个内部驱动器阵列，等待上一个问题的结果。）它比内部索引碎片的影响更大吗？或者它真的是同种问题（驱动器必须进行随机读取而不是顺序读取）

  • 如果驱动器存在物理碎片，那么碎片整理（或重建）索引是否浪费时间？我是否需要先修复一个问题，然后再解决另一个问题？

  • 修复生产 SQL 框上的物理文件碎片的最佳方法是什么？我知道我可以关闭 SQL 服务并运行 Windows Defrag，但我还听说过一种技术，即执行完整备份，删除数据库，然后从备份还原到空驱动器。推荐后一种技术吗？从这样的备份中还原还从头开始构建索引，消除内部索引碎片？还是只是将页面顺序恢复到备份时的顺序？（如果这很重要，我们正在使用带压缩的 Quest Lightspeed 备份。）

更新：到目前为止，关于是否对 SAN 驱动器进行碎片整理（否）以及在物理碎片化的驱动器上进行索引碎片整理是否仍然值得（是）的答案都很好。

还有谁愿意谈谈实际进行碎片整理的最佳方法？或者估计一下对一个大型碎片驱动器（比如 500GB 左右）进行碎片整理需要多长时间？显然，这很重要，因为那时我的 SQL 服务器将停机！

此外，如果有人有关于通过修复物理碎片来改进 SQL 性能的轶事信息，那也很好。Mike 的博客文章谈论发现问题，但没有具体说明取得了什么样的进步。

答案1

我认为这篇文章对 SAN 驱动器的碎片整理进行了很好的概述

http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php

基本要点是，不建议在 SAN 存储上进行碎片整理，因为当呈现 LUN 时，当位置已被 SAN 虚拟化时，很难关联磁盘上块的物理位置。

如果您使用的是 RAW 设备映射，或者您可以直接访问您正在使用的 LUN 的 RAID 集，我可以看到 degfragmentation 具有积极的影响，但是如果您从共享 RAID-5 集中获得了“虚拟”LUN，则不会。

答案2

这个问题和答案有多个部分：

正如 Kevin 已经指出的那样，物理文件碎片与企业 SAN 存储实际上无关 - 因此无需补充。它实际上取决于 I/O 子系统以及您能够使驱动器在执行扫描时从更随机的 I/O 转变为更连续的 I/O 的可能性。对于 DAS，您更有可能这样做，但对于复杂的切片 SAN，可能就不行了。

文件系统级碎片整理 - 仅在 SQL 关闭时执行。我自己从未遇到过问题（因为我从未对 SQL 数据库文件执行过在线、打开文件碎片整理），但我从客户和客户那里听到了很多关于发生奇怪损坏问题的传闻证据。一般来说，不要对 SQL 在线执行此操作。

索引碎片与文件碎片完全无关。SQL Server 不知道文件碎片 - 中间有太多虚拟化层，因此它无法解决实际的 I/O 子系统几何问题。但是，SQL 确实了解索引碎片。我不再重复您已经引用的答案，索引碎片将阻止 SQL 进行有效的范围扫描预读，无论文件在文件系统级别有多碎片化（或没有碎片化）。因此 - 如果您发现查询性能下降，您绝对应该减轻索引碎片的影响。

你不有按照任何特定顺序执行这些操作，但如果您处理了文件系统碎片，然后重建所有索引，并通过在碎片整理的卷上增加多个文件而导致更多的文件系统碎片，您可能会生气。但这会导致任何性能问题吗？如上所述，这取决于 :-D

希望这可以帮助！

答案3

修复生产 SQL 框中的物理文件碎片的最佳方法是什么？

我在数据库文件上运行 SYSINTERNALS 的 contig。

看http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

答案4

我曾经尝试过使用 scsi 解决方案对物理磁盘进行碎片整理，但性能提升甚微甚至毫无提升。我学到的教训是，如果您由于磁盘系统而遇到性能缓慢的情况，那么就数据文件而言，这与碎片无关，因为它使用的是随机访问。

如果您的索引已进行碎片整理，并且统计数据已更新（非常重要），但您仍然将 I/O 视为瓶颈，那么您面临的问题不仅仅是物理碎片。您是否使用了超过 80% 的驱动器？您的驱动器是否足够？您的查询是否经过了充分优化？您是否进行了大量表扫描，甚至更糟的是，进行了大量索引查找，然后进行聚集索引查找？查看查询计划并使用“set statistics io on”来找出查询的真正情况。（查找大量逻辑或物理读取）

如果我完全错了，请告诉我。

/哈坎·温瑟

相关内容