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 *

Advertisement

Our Reviews

LED TV

The Frame is a head turning 4K QLED TV that hangs on your wall and looks like a piece of artwork.

Bookshelf Speakers

You don't need to speak french to hear how good a pair of $990 bookshelf speakers sound.

NAD Electronics

At $899 the NAD C 588 is an impressive well built turntable that has the ability to hear deep into every recording.

Over-Ear Headphones

Impressive, neutral sounding $500 over-ear headphones should appeal to audiophiles who want to hear everything, not just feel the bass.

Digital Music Systems

Easy to setup and use all-in-one wireless music player combines a stereo integrated amplifier, DAC, streaming player and app for audiophile-grade listening.

HiFiMAN

$349 open-back headphone will amaze first time listeners who have never tried planar magnetic headphones.

You May Also Like

Amazon

Locating the best deals for 2020 Black Friday and Cyber Monday on high-end consumer electronics including 4K TVs, audiophile headphones, home theater speakers and...

Home Audio

Sneak peek of the audiophile products we're reviewing right now. New reviews coming December 2020 and January 2021.

LED TV

The Frame is a head turning 4K QLED TV that hangs on your wall and looks like a piece of artwork.

Bookshelf Speakers

You don't need to speak french to hear how good a pair of $990 bookshelf speakers sound.

Advertisement

ecoustics is the unbiased resource for the latest technology news, coolest gadgets, and best consumer electronics. Plus get product reviews, roundups and deals from all over the web on everything electronic. Read more



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