One of the biggest hardware bottlenecks of any SQL Server is disk I/O. And anything that we, as DBAs, can do to reduce SQL Server’s use of disk I/O will help boost its performance. Some of the most common things DBAs do to reduce disk I/O bottlenecks include:
- 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 fragmentation.
Another less frequently used method to reduce overall disk I/O, but nonetheless important, is to perform physical defragmentation of SQL Server program files, database files, transaction logs, and backup files. Physical file fragmentation occurs in two different ways. First, individual files are broken into multiple pieces and scattered about a disk or an array (they are not contiguous on the disk). Second, free space on the disk or array consists of little pieces that are scattered about, instead of existing as fewer, larger free spaces. The first condition requires a disk’s head to make more physical moves to locate the physical pieces of the file than contiguous physical files. The more physically fragmented a file, the more work the disk drive has to do, and disk I/O performance is hurt. The second condition causes problems when data is being written to disk. It is faster to write contiguous data than noncontiguous data scattered over a drive or array. In addition, lots of empty spaces contributes to more physical file fragmentation.
If your SQL Server is highly transactional, with mostly INSERTS, UPDATES, and DELETES, physical disk fragmentation is less of an issue because few data pages are read, and writes are small. But if your are performing lots of SELECTS on your data, especially any form of a scan, then physical file fragmentation can become a performance issue as many data pages need to be read, causing the disk head to perform a lot of extra work.
Built into Windows 2000 and Windows 2003 is a built-in defragmentation utility, but it is slow, doesn’t always do a good job of defragmentation, and cannot be easily scheduled. Because of this, I decided to give Executive Software’s Diskeeper 8.0 software a try. This defragmentation utility comes in five different versions, including:
- Home
- Professional
- Administrator
- Standard Server
- Server Enterprise
For this article, I used both Professional and Standard Server. Both are very similar, but have a slightly different feature set, as you might expect. The Professional Edition is designed for your desktop, while the Standard Server is designed for servers. The goal of this article is to tell you how Diskeeper works, it is not an article testing the pros and cons of the software.
Diskeeper in Action
One of the best ways to see what Diskeeper can do for you is to give it a try, and that’s what I am going to do here. To see what Diskeeper can do for one of my SQL Server’s, I installed it on a SQL Server that had been in production for about 5 months, and had never been defragged before.
Diskeeper includes a tool that allows you to analyze the amount of fragmentation before and after the defragmentation occurs, which can be enlightening, as you will soon see.
So after installing Diskeeper, I started it up, and here’s what the screen looks like (modified to fit the web page).
The first thing I wanted to do was to see how fragmented the files were on the server, especially on drive F, where the database MDFs, LDFs, and backup files were located. In addition, the SQL Server executable files were also located on drive F. Drive C only had the OS.
So my first step was to analyze drive F. Diskeeper produces several reports after an an analysis is done, as you can see here.
As you can see in this report, it has found 244 fragmented files and 1,831 fragments, which Diskeeper considers a heavily fragmented disk, and it recommends that fragmentation take place. Besides the summary above, it also produces some addition reports, which we see below.
The Performance Report above is a little more ambiguous than the first report. The graph above indicate that disk I/O performance will only be improved about 1% if defragmentation is performed. The recommendation is to defragment now, but under the section that says, “What do the numbers mean?” the report says that overall, performance is not affected by the current level of fragmentation.