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

Sharp TIVO TV Lifestyle Man View

HDTV 4K & 8K TV

Would you buy a 55-inch Sharp 4K TV with built-in TiVo for $299.99? Let's take a look at what it offers.

Bang & Olufsen Beosound Balance Natura Wireless Speakers Bang & Olufsen Beosound Balance Natura Wireless Speakers

New Products

This convergence of Danish Hi-Fi and some of the finest Italian stone or petrified wood will not be cheap. And that was before the...

Advance Paris Link Cables Advance Paris Link Cables

Audio Cables

High-end cables made in France? Do British and American manufacturers need to be concerned? The Advance Paris LINK have us excited to try them.

https://www.audioadvice.com/products/kaleidescape-strato-c-4k-ultra-hd-movie-player?referral=ecoustics Strato M Movie Player https://www.audioadvice.com/products/kaleidescape-strato-c-4k-ultra-hd-movie-player?referral=ecoustics Strato M Movie Player

New Products

At only $1,995, Strato M is Kaleidescape's lowest cost movie server/player, but there is a catch.

Sony's new 2025 model year BRAVIA TVs Sony's new 2025 model year BRAVIA TVs

HDTV 4K & 8K TV

Last month, we got a sneak peek at Sony’s new BRAVIA 4K TVs. Here’s what’s coming down the pike this year.

Sony BTAVIA Projectors - 2025 Sony BTAVIA Projectors - 2025

New Products

Sony just brought its most powerful video processor for projectors down to what should be a much more affordable price point.

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