Defragmenting Databases. Myth or Real-McCoy?

Posted by Colleen Toum... on Thursday, July 12, 2007 - 02:22 am:

Share | Create New | Reply | Edit

Database servers suffer from two distinct types of fragmentation: internal and external. Internal fragmentation occurs when records are removed from database pages causing the space the record occupied to remain void after deletion. Eventually the space is reused but as it is reused, the database pages become fragmented which can lead to unnecessary time spent with I/O processes. In SQL Server, there are several ways to defrag internal fragmentation. One of these methods is to use the DBCC REINDEX command to rebuild clustered and non-clustered indexes. Once indexes are rebuilt database pages are now logically contiguous, and disk I/O is minimized.

External fragmentation refers to the fragmentation of files on your server's disks, which can cause as much, if not more, unnecessary I/O activity as internal fragmentation. Database servers are made up of large database and log files that are pre-allocated in size at the point of their creation. If there is enough contiguous empty space on disk when the original files are created then they will not be fragmented. However, if there is not enough contiguous empty space on the disk then these original database and log files will be fragmented and spread across the empty space on your disk.

Even if the original database and log files are not fragmented when they are first created, they will almost certainly become fragmented as the database grows over time and the unnecessary I/O activity to read the fragmented database and log files hurts SQL Server's overall performance.

To defrag external fragmentation takes an operating system utility, not a SQL Server utility. When the fragmentation tool runs, it does not restructure internal contents of the file, unlike DBCC REINDEX.

After the database and log files are defragmented, the defragmented files will be a contiguous bit for bit duplicate of the original. Therefore, any holes within the database pages are still present and you will still need, from time to time, to rebuild your indexes to combat internal fragmentation.

File defrag is not going to solve all DB-optimization requirements, but it is often lesser-known piece in the puzzle. So the next time your colleague suggest that your database server does not need a defragmenter, think again.