SOURCE: Diskeeper Corporation

May 13, 2008 17:52 ET

Defrag: The Hidden Secret to SQL Server Performance

BURBANK, CA--(Marketwire - May 13, 2008) - SQL Server is not one of those applications that can take a "back seat" when it comes to performance. Generally it is being used to service clients for functions such as order taking, order tracking, customer records, inventory, and just about any other database function within a company. Its performance must be first priority, and in many companies there are IT employees dedicated strictly to the database and, in a large part, its performance.

Unlike normal computer files, databases suffer from two distinctly different types of fragmentation: internal and external. Internal fragmentation occurs when records are removed from database pages leaving space behind. As this space is reused the data pages become fragmented leading to unnecessary I/O, especially in case of table scans where many data pages are read, one after another. The most common way to address internal fragmentation is the DBCC REINDEX command, which rebuilds indexes. Once indexes are rebuilt, data pages are now logically contiguous, and disk I/O is minimized.

But running this command does nothing for external fragmentation. External fragmentation refers to the fragmentation of files on the server's disks, which can cause as much, if not more, unnecessary I/O activity as internal fragmentation. Unnecessary I/O activity, as one might expect, drastically affects SQL Server's overall performance.

"We use Microsoft SQL Server and we were receiving hundreds of messages per day in the log like this one: SQL Server has encountered 21 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\mssql\data\...]," said Ron Young, System Architect with a large architectural firm in Irving, Texas.

Young, as with others encountering severe fragmentation-related performance problems with SQL, has countered the issues with Diskeeper® automatic defrag. Diskeeper runs invisibly, in the background, using otherwise idle system resources. The product requires no scheduling at all, and performance is consistently maximized. "After running Diskeeper, all of these errors disappeared," Young reported.

"After installing Diskeeper and doing a full defrag, we noticed around 10-15% reduction in the amount of I/O generated and in the amount of time it took for the SAN to service each request," said Andrew Wise, Senior Network Engineer with Datacore Marketing in Westwood, Kansas.

SQL Server databases are made up of large database and log files that have pre-allocated sizes when created. If there is enough continuous space on disk when the original files are created, they will not be fragmented. But if the empty space available is not continuous -- and it almost never is -- then these original database and log files will be fragmented. And 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, as can be attested by the volume of reports over time on SQL Server fragmentation and its effects on performance.

Diskeeper's automatic defrag is the best "companion" to SQL Server to ensure that it is always performing at its best.

Contact Information