Avoiding SQL Server Fragmentation

Any discussion on disks would not be complete without considering fragmentation. Fragmentation can occur in several forms with SQL Server:

• Internal SQL fragmentation occurs when pages are split due to many inserts, updates, and deletes. This
• External fragmentation, which you are interested in here, can take two forms:
  • Classic file fragmentation occurs when a file is created and the file system doesn’t have enough contiguous disk space to create the file in a single fragment. You end up with a single file spread across multiple file fragments.
  • Autogrow fragmentation is the fragmentation that occurs when you enable autogrow and the database size continuously grows with the addition of more file fragments. These fragments may or may not have classic file fragmentation as well.

One important point to consider here is that SQL database files don’t become more fragmented after they have been created. If files are created when there isn’t enough contiguous free space, they are created in multiple fragments. If the disk is defragmented (and the OS has enough space to fully defragment all files) right after the files are created, the files are no longer fragmented and won’t ever become fragmented.

In the ideal scenario, you have dedicated disks for your SQL database files, and you can correctly size each file, create the files, and disable autogrow. In this situation, you start with clean disks, create one or two files that aren’t fragmented, and they stay that way forever. That way, you need to deal only with internal fragmentation.

However, back in the real world, you start from the previous situation but enable autogrow at some tiny size or percentage, so you end up adding hundreds or thousands of small files. In this case, those files may or may not be fragmented, depending on how much free space is available on the disk when each autogrow operation occurs. Your only solution here to remove the fragmentation is to schedule server downtime to rebuild each database using a few large files, sized correctly for the expected database growth, and then disable autogrow. This way, you resolve any disk fragmentation that occurs. The ideal way to prevent external, filesystem fragmentation from ever occurring is to disable autogrow on the database files. This is, however, in the majority of cases, not practical.

In the worst-case scenario, you don’t have dedicated disks; you used the default database sizes and enabled autogrow. Now you may have several problems to resolve. Your SQL database files are competing for I/O capacity with the OS, and anything else running on the server. Until you add dedicated disks for SQL Server, this won’t be resolved. In addition, you may also end up with a lot of file fragments because each autogrow operation adds another data or log file fragment. As each new file fragment is created by autogrow, it might be fragmented over the disk surface. As more fragments are added and the disk fills up, the chance of creating fragmentation increases.

The best way to avoid problems is to follow these steps:

1.   Install the OS.

2.   Defragment the disk.

3.   Install any applications (SQL Server).

4.   Defragment the disk.

5.   Create data and log files at maximum size.

6.   Check for fragmentation and defragment if necessary.

7.   Disable autogrow.

8.   Routinely defragment the disk to clean up fragmentation caused by other applications. This preserves the free space should you ever need to add more SQL data or log files.

In most cases, the operating system’s disk defragmenter does a great job and is all you need. In some situations, however, you may need to consider purchasing a third-party disk defragmentation utility. Some benefits of using a third-party tool include increased speed of operation, background processing, multiple simultaneous volume defragmentation, and the ability to schedule defragmentation operations.