On the other hand, the Reliability Report is not ambiguous at all. It claims the computer’s reliability is severely affected by the current level of fragmentation and that the drive needs to be defragged. The claim here is that the fragmentation can lead the OS to having trouble doing its normal, everyday work, and can cause crashes and program hangs if not corrected.
The Fragmentation Report provides a lot of interesting data, which is used to support the recommendations made earlier.
The Drive Map Report is the most visually interesting one to me, and show how the file fragments are spread about the disk. The blue indicates non-fragmented files, and the red indicates fragmented files. Green refers to files that cannot be moved, such as some system files.
Based on Diskeepers’s recommendation to defrag the drive, I manually selected this option and waited for the defragmentation to complete. When it did, here’s what the drive map looked like.
As you can see, Diskeeper virtually eliminated all disk fragmentation on this drive. I won’t bore you by showing you all of the other reports again, but they indicated that the disk was now 100% defragged. At this point, file fragmentation is not an issue and cannot negatively affect SQL Server’s performance.
Now What?
Now that the disk is defragged, are we done? No. Fragmentation never stops. Although NTFS will try to to minimize file fragmentation, it doesn’t do a very good job at it. Because of this, defragmentation need to be done continually, if you want optimal disk I/O performance. To accomplish this goal, Diskeeper allows you to schedule defragmentation on an as needed basis. While you have several scheduling options, the easiest to use is the “Set it and Forget It” option. By selecting this option, Diskeeper automatically works to defrag your system while using the least amount of resources possible, on an ongoing basis. This setting should work well for most SQL Server’s, assuming they are not already overextended. If your SQL Server is very busy, you will want to manually schedule defrags at time when the server’s resources are less taxed.
Can Diskeeper Defrag Open SQL Server MDF and LDF Files?
One of the biggest concerns about disk defragmentation is that in many cases, defragmentation can’t work if the file that needs to be defragged is currently is use. In other words, if the file is actively being used, Diskeeper, or any defrag utility for that matter, can’t defrag it. But what does in use really mean?
When I directed this question to a support person at Executive Software, here was the response: “Diskeeper defragments the drive on the OS level. Having this in mind, Diskeeper is able to safely defragment a drive on an OS level whether or not it is running a SQL or Exchange Server. However, if the database is occupied or open by another process, such as when database tables are being used, then it cannot be defragmented until it can be accessed by Diskeeper. In some cases then it is recommended that you turn off the services of SQL or Exchange in order to defragment the drive completely.”
When I ran my experiment above, the SQL Server MDF and LDF files were indeed defragged, even though they were open at the time. Is this always the case? My test is not conclusive, and they response I got back from Executive Software is a little ambiguous, so my recommendation is to test Diskeeper on your own system, and see for yourself how effective it is at defragging open SQL Server files. Whatever the answer, it does not negate the need to defrag your SQL Servers. For optimum SQL Server performance, file defragmentation is a necessity. If you find that you need to bring SQL Server down to defrag some or all of the open MDF and LDF files, then you need to schedule this into your maintenance schedule. Or you may be lucky, and Diskeeper will keep them defragged without having to bring SQL Server down, as in the case of the one server I tested.
Give Diskeeper a Try
Fortunately, you can try Diskeeper (any version) for free. That is the best way to find out if it will meet your
]]>