Server Solutions

All SQL Server databases, over time, experience "internal" file fragmentation. This occurs when records are removed from database pages, but the space it occupied is still there after deletion. Eventually this space is reused, but as it is reused, the data pages become fragmented, which can lead to unnecessary I/O, especially in case of table scans where many data pages are read, one after another.

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. For example, if you set the original database size to 100MB and the log to 10MB, and you have them set to grow automatically, and if eventually the database grows to 5GB in size and the log grows to 100MB in size, external fragmentation could become great. Every time the database or log files grow automatically, there is the potential for external fragmentation.

Read more on SQL Server performance »

How do I get the most performance from my SQL Server?

One of the biggest hardware bottlenecks for any SQL Server is disk I/O. Anything a database administrator can do to reduce the amount of I/O necessary to complete a task will increase that server’s performance.

Some of the most common things DBAs often resort to are:

Tuning queries to minimize the amount of data returned.

Using fast disks and arrays.

Using lots of RAM so more data is cached.

Frequent DBCC REINDEXing of data to remove logical database deleted or inactive files.

But none of these actions will prevent hardware bottlenecks that occur due to fragmentation. In order to defrag external fragmentation and get the most performance from your SQL Server, you need an operating system utility, not a SQL Server utility.

There are two types of external fragmentation a utility like Diskeeper handles:

1. File Fragmentation
2. Free Space Fragmentation

File fragmentation concerns computer disk files that are not whole, but rather are broken into scattered parts; while free space fragmentation means that the empty space on a disk is broken into scattered parts rather than being collected all in one big empty space. File fragmentation causes problems with accessing data stored in computer disk files, while free space fragmentation may cause problems creating new data files or extending (adding to) old ones.

Fragmentation never stops. Without the real-time support of Diskeeper^® data performance software, fragmentation accumulates and heavily relied upon servers become slower and eventually must be taken off line for maintenance. NTFS does try to minimize file fragmentation but can’t begin to keep up.

When Diskeeper runs, it acts to defrag database and log files so that instead of being made up of many pieces, the file is one continuous segment. In addition, Diskeeper defrags free space so that when database or log files expand, that they can expand with little or no fragmentation. V-locity^® virtual platform optimizer does the same with VMs.

All file movement in a Diskeeper defragmentation job is handled by the operating system itself. In fact, the code in the operating system prioritizes safety in determining what can be defragmented and what cannot. SQL Servers databases (e.g. .LDF, .MDF) are perfectly safe to defragment. As Diskeeper sends requests to the operating system (through an API) to move files, if it comes across files that cannot be safely moved, they are simply skipped over without any error or concern.

"We offer a global Shared SQL Server hosting service within an oil major. The service hosts up to 150+ databases per host in some cases so very heavily shared. We experienced a production outage where SQL 2005 stalled as it could not write in a timely manner to the database file. It came as a great surprise that this was found to be due to the database files being so heavily fragmented.

"A manual Windows defrag of the 2TB volume achieved only 12% in 6 hours and was an unacceptably long outage of service. We chose Diskeeper to address this problem and have rolled it out on all production environments and it is fantastic, defragmenting pretty much 4TB of data within two weeks at most."

Download a free trial of Diskeeper Server or V-locity to test on your SQL Server today »