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

Latest Products

Anker Nebula X1 Projector

New Products

Go wireless with the Anker Nebula X1 projector—perfect for creating a backyard movie theater without the hassle of cords. Maybe one.

JL Audio C6-653cs Speakers for Ford F-150 JL Audio C6-653cs Speakers for Ford F-150

Car Speakers

Upgrade your Ford F-150's audio with Garmin's new JL Audio C6 three-way speakers and Stealthbox subwoofer. Experience powerful sound and custom fit for your...

Bowers and Wilkins Px7s3 headphones in Indigo Blue and Anthracite Black. Bowers and Wilkins Px7s3 headphones in Indigo Blue and Anthracite Black.

New Products

Some of our favorite over-ear wireless noise cancelling headphones are getting a major update. But will tariffs make these practically unaffordable in the United...

Andover Audio SpinBase Max 2 Wireless Speaker under Turntable in Black Andover Audio SpinBase Max 2 Wireless Speaker under Turntable in Black

New Products

Designed to sit under any turntable, the $549 SpinBase MAX delivers room-filling sound with minimal footprint—ideal for vinyl lovers seeking performance and convenience in...

Roku New Products Unveiled - 2025 Roku New Products Unveiled - 2025

HDTV 4K & 8K TV

New Roku Streaming Stick and Streaming Stick Plus are Around 35% slimmer than the competition and can be powered by a TV's built-in USB...

LG xboom with Will.i.am 2025 Bluetooth Speakers and Buds LG xboom with Will.i.am 2025 Bluetooth Speakers and Buds

Earbuds & In-Ear Headphones

LG adds "abracadabra" to their xboom product line with the Grab, Bounce, and Stage 301 bluetooth speakers and Buds, tuned by will.i.am

Gift Ideas?

Cost-no-object Audiophile Gear

Floorstanding Speakers

When money is no-object in building a high-end audio system, this is the audiophile gear you'll want to check out first.

You May Also Like

Advertisement

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