Connect with us

Hi, what are you looking for?

New Products

Defragmenting Databases. Myth or Real-McCoy?

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.

Advertisement. Scroll to continue reading.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *


You May Also Like


ecoustics is a hi-fi and music magazine offering product reviews, podcasts, news and advice for aspiring audiophiles, home theater enthusiasts and headphone hipsters. Read more

Copyright © 1999-2024 ecoustics | Disclaimer: We may earn a commission when you buy through links on our site.

SVS Bluesound PSB Speakers NAD Cambridge Audio Q Acoustics Denon Marantz Focal Naim Audio RSL Speakers