Boost SQL Server Database System Performance.

BURBANK, Calif. -- 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 impact system performance, 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 system performance tools to defrag a SQL Server database file is a tool from Diskeeper[R] Corporation called Diskeeper. 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.

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 and reliability of your SQL Servers.

If there is any doubt, simply install Diskeeper system performance software and use the defrag analysis function to find out exactly how many individual pieces your files are broken into. I've seen reports from sites where their database files were in more than 287,000 pieces. OUCH!!!!