I used to work at a multi-billion dollar organization and, like most organizations that big, they kept backups of all their databases offsite in case some major catastrophe destroyed the data center. There was only one problem with their otherwise excellent plan; the offsite location for the backup tapes was the building next door, a mere 50 yards from the data center. Any disaster spectacular enough to take out the data center would most probably have damaged or leveled the building next door. Continue reading →

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

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.

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.

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.

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.

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?