物理文件碎片整理

Physical File Defragmentation

物理文件碎片整理

Database Administration,Opinion | May 1, 2009 | 12:45 pm

Do You Include Physical File Defragmentation as Part of Your SQL Server Maintenance?

 

你是否在你的SQL Server维护工作中把物理文件的碎片整理考虑在内了呢？

Ever since I can remember, beginning with MS-DOS 1.0, physical file fragmentation has often been a problem on many systems I have used, hurting I/O performance as the disk heads have to thrash about to find all the many file fragments that make up a single physical file. Generally, using either the built-in OS defragmentation tools, or third-party tools, I have kept my physical files defragmented, so they are contiguous, helping to optimize my system’s I/O.

从我能回忆起以来（始于MS-DOS1.0），物理文件的碎片整理就是系统使用中经常出现的性能问题。由于磁头必须反复读取大量的文件碎片来拼凑成一个物理文件，IO性能将严重受到影响。我通常使用操作系统内置的碎片整理工具或者第三方的碎片整理工具来进行物理文件碎片整理，以确保物理文件连续从而优化系统IO性能。

As technology has changed (SANs or SSD drives) physical file fragmentation has become less important. See Linchi Shea’s blog series on this topic. On the other hand, there are still a lot of servers with local storage that can be still be negatively affected by physical file fragmentation.

由于技术的改进（SAN和SSD驱动器）物理文件碎片的问题已显得无关紧要。请看Linchi Shea关于此话题的系列博客（Linchi Shea’s blog series）。而另一方面，仍有大量的服务器采用本地存储，这种情况下物理文件碎片仍然会对IO性能产生负面影响。

When I build a new physical box to run SQL Server, the new box has little or no physical file fragmentation to start with. Then, when I create my MDF and LDF files, I pre-size them to as close as I can to their final size (or at least as large as I expect them to grow in the next year or so). This way, when the MDF and LDF files are created on a new server, they are created in a contiguous manner, and there is no physical file fragmentation. Of course, if I do need to grow the MDF or LDF files, I do so in a controlled manner to minimize fragmentation. I avoid using autogrowth to grow my databases, as this can greatly contribute to file fragmentation as the MDF and LDF files grow over time.

当我新搭建一个物理机器来运行SQL Server时，这台新机器上很少或者几乎没有物理文件碎片。接着我创建了MDF和LDF文件并将它们的大小预设成尽可能接近它们的最终大小（或者至少预设成来年该文件预期增长到的大小）。这样在MDF和LDF在心服务器上创建时，它们都是连续的，没有任何物理文件碎片。当然，如果我需要增长MDF或者LDF文件，我将以可控的方式增长以文件碎片最少。我避免使用自动增长功能来增长数据库，因为随着MDF和LDF文件的增长，自动增长将引起大量的文件碎片。

In other cases, I have to deal with SQL Servers that have been around for a long time and have not been properly maintained. In those cases, I check for how bad fragmentation is, and if it is bad, I fix it before I create any new pre-sized MDF or LDF files. As a DBA, I prefer to be proactive and prevent physical file fragmentation from occurring in the first place.

在很多情况下，我必须应付一些运行了很长时间而没有进行维护的SQL Server系统。在那些工作中，我会检查文件碎片的严重程度，如果文件碎片很严重，我会在创建任何预设大小的MDF和LDF文件之前整理文件碎片。作为一位DBA，我倾向于未雨绸缪而将文件碎片整理作为第一要务。

What I would like to know is what has been your experience with physical file fragmentation on your SQL Servers? Have you experienced it? How has it affected performance? How do you fix it if you have it? What defragmentation tools do you use, and why? How do you prevent it from happening in the first place? In other words, how do you deal with physical file fragmentation on your SQL Servers?

我希望了解你经历了那些物理你在SQL Server系统中的文件碎片问题？你碰到此类问题了吗？文件碎片是如何影响你的系统性能的？如果你碰到过此类问题，你是如何解决的？你使用什么碎片整理工具，你为什么使用它呢？你又是如何在第一时间避免文件碎片的出现呢？换言之，你如何在你的SQL Server系统中处理物理文件碎片问题？

Share and Enjoy:

Read more: http://www.bradmcgehee.com/2009/05/physical-file-defragmentation/#ixzz110ICM8Ks
Under Creative Commons License: Attribution