What If: My QuickBooks File is Badly Fragmented?

by

The most common form of fragmentation is associated with your computer's hard drive. It results from the way that the Windows Operating System stores files on the drive. A ProAdvisor's understanding of this process is the key to preventing and resolving it.

When a hard drive is new, it has only a few internal structures and, is otherwise, one contiguous disk of empty space. As a result, the Windows I/O subsystem is able to write files to any of the sectors of the disk.

Whenever a file is written, or rewritten, Windows attempts to store that file in one long string without any spaces. This is referred to as a "contiguous write," and the area of the disk where the data is stored is referred to as a "cluster."

On relatively new hard drives with lots of free space, it is easy for the Windows Disk I/O sub-system to write the data in a contiguous fashion and produce continuous clusters of data. But with the addition of more and more data to the drive, and modifications and deletions of the data within files, spaces and gaps begin to appear on the hard drive.

This results in disk fragmentation.

When Windows can no longer find one contagious area of the disk to write the entire file, it starts writing the file wherever it can by breaking up the data into smaller pieces and storing it all over the hard drive wherever it can find space.

This produces file fragmentation.

File system fragmentation increases disk head movement or seek time, which are known to hinder throughput. So, the most noted side effect of fragmentation is performance degradation caused by the disk I/Os taking longer than they optimally would.

Seek, read and write functions are all prolonged as the disk drive head may be required to move to multiple areas to service the data.

The computer’s hard drive cannot sustain unlimited fragmentation, sooner or later the number and location of fragments can overwhelm the disk I/O subsystem’s ability to respond to Operating System requirements.

This especially is true for consumer-grade hard disk drives, because of the increasing disparity between access speed for sequential requests and rotational latency, both of which impact seek times. 

Fragmentation Impacts QuickBooks Performance

Fragmentation has been defined as any condition causing greater than the optimal amount of Disk Input/Output to be performed in accessing a table, or causing disk IOs to take longer than they optimally should. In light of this definition, it's easy to see how fragmentation can impact QuickBooks performance.

Because of the way the Windows Operating System works in terms of data-to-disk allocation, any QuickBooks Company file may become fragmented almost as soon as it is created. That means it can get progressively worse as the file grows. 

This is because Windows does such a bad job of allocating contiguous disk space for new and growing files, even if a hard drive has just been defragmented.

So, a QuickBooks file can start out fragmented from almost the moment it is created, and becomes even more fragmented each time it is used. The larger the file, the more file fragments users can expect, especially when their disk drive is operating at greater than 50 percent capacity.

Since some QuickBooks databases are extremely large and complex, significant disk fragmentation can have major effects upon the performance of the QuickBooks program as well as integrity of each QuickBooks Company-file.

Because the most common type or form of data corruption is fragmentation, the No. 1 way ProAdvisors can minimize QuickBooks Company file corruption is to monitor and control file fragmentation.

One method for monitoring fragmentation related to QuickBooks is to track the number and growth of DB File Fragments reported in the Product Information window that can be opened by pressing the F2 key while QuickBooks is open. 

As a practical rule, this number should be as close to zero as possible. But there will almost always be some fragments. Many QuickBooks ProAdvisors believe that 20 or more file fragments represent a potential issue.

However, Intuit Technical support seems to consider a number of 50, or maybe even 100, as the threshold of significant fragmentation needing attention.

Evaluating and Resolving Disk Fragmentation

But this measure is not the sole indicator of fragmentation that can impact performance. I have seen examples of where QuickBooks files with as few as three or four fragments reported in the F2 window are struggling in terms of performance due to the overall fragmentation of the disk drive. 

As such, ProAdvisors also need to monitor the computer disk where the QuickBooks Company file is stored.

While there are more sophisticated third-party applications that can be installed on a computer to evaluate disk drive status, performance and fragmentation, there is absolutely nothing wrong with using the Windows Defragmentation tool for this purpose:

  • From the My Computer icon, right click to open the menu
  • Select the Properties menu option
  • Click on the "Tools" tab
  • Find the Defragmentation tool, and click "Degragment now" – Don't worry you aren't actually starting the defragmentation process, simply opening the Windows Disk Defragmenter

Let me call your attention to three different sections of the Windows Disk Defragmenter shown above. 

First, the area highlighted in the blue box shows the last time the disk was analyzed or defragmented. In this case, Drive C was last analyzed on April 26 and had 0 percent fragmentation at conclusion.

The reason the drive was so clean of fragmentation was that it was defragmented automatically as part of the schedule highlighted in the red box, which is set in this case to run every Wednesday night. 

Using the scheduled defragmentation option is a good way to keep fragmentation to a minimum. Over successive defragmentations, the total number of fragments should become less and less.

If you don't make use of "scheduled defragmentation," you need to "analyze" and most probably, "defragment your disk." To analyze your hard drive:

1. Click on the Volume (disk drive letter) that you want to analyze

2. Then click Analyze to begin the analysis of that volume

3. When the analysis is complete, Windows will display a pop-up advising you of the "complete" status (shown above). You can review the results of the analysis by clicking View Report. You can print or save a copy of the analysis tool as you determine appropriate.

If the analysis recommends that the volume be defragmented, proceed with defragmentation of the disk drive.

4. From the Analysis Report screen for the disk drive (volume) you have just finished analyzing, click Defragment. The progress of the defragmentation process is shown in the Display window. System files are green, contiguous files are blue and fragmented files on the disk appear in red. The Windows Defragmentation tool is designed to eliminate as many of the fragmented (red) files by reallocating as many of those files to associated contiguous areas of the disk, as possible.

In the second part of this series, we'll look at some other specific techniques ProAdvisors can use in managing, reducing and preventing fragmentation impacting QuickBooks performance.