SOURCE: Diskeeper Corporation

August 23, 2006 11:39 ET

Defrag Your SQL Server Database or Go to Pieces

Fragmented SQL Server Databases Can Bring Your Business Down to a Crawl

BURBANK, CA -- (MARKET WIRE) -- August 23, 2006 -- All SQL Server databases will, over time, experience "internal" fragmentation of its data. This occurs when records are removed from database pages, but the space it previously 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 the case of table scans where many data pages are read, one after another. This is where the need to defrag comes into play.

In SQL Server, there are several ways to defrag internal fragmentation. One of these methods is to use the DBCC REINDEX command to rebuild the clustered and non-clustered indexes. Once indexes are rebuilt, data pages are now logically contiguous, and disk I/O is minimized.

Unfortunately, internal fragmentation is only part of the fragmentation problem. When DBCC REINDEX is run, it does nothing about "external" fragmentation. 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. Unnecessary I/O activity, as you would expect, hurts SQL Server's overall performance.

To defrag external fragmentation takes an operating system utility, not a SQL Server utility. One of the most popular tools to defrag a SQL Server database files is a tool from Diskeeper® Corporation called Diskeeper (www.diskeeper.com). Diskeeper has been around for many years, and many of you may already be familiar with it. What many DBAs aren't familiar with is that it is probably the best tool for defragmenting external fragmentation on their SQL Servers.

When an external fragmentation tool like Diskeeper runs, it does not restructure the internal contents of the file, unlike DBCC REINDEX. After Diskeeper defragments a file, the defragmented file will be a bit for bit duplicate of the original. Therefore, any holes within the database are still present and you will still need, from time to time, to rebuild your indexes to combat internal fragmentation.

So how do you find out if the files on your SQL Server are fragmented? Fortunately, this is easy. As part of Diskeeper's functionality, you can run a fragmentation analysis to see just how fragmented your SQL Server files are. As with defragmentation, this can be done while SQL is running.

As you can imagine it is hard to recommend a specific schedule, as each database is different and fragmentation occurs at different rates. A dynamic Diskeeper scheduler called Smart Scheduling® determines and automates defragmentation jobs as appropriate. And yes, you can still restrict Diskeeper "Smart" runtimes and resource usage.

So, it's plain to see that a defragmentation utility like Diskeeper can take care of the external disk fragmentation, while a SQL Server utility like DBCC REINDEX can address internal SQL Server disk fragmentation. As a team, they can work together to help ensure the optimum performance of your SQL Servers.

If there is any doubt, simply install Diskeeper and use the defrag analysis function to find out exactly how many individual pieces your files are broken into. I'm quite sure that you will be very surprised. I've seen reports from sites where their database files were in more than 287,000 pieces. OUCH!!!!

Contact Information

  • Contact:
    Colleen Toumayan
    Email: Email Contact
    Phone: 800-829-6468