Messy Page Chain
QuickBooks ProAdvisors have become increasingly aware of fragmentation within the database, in part due to errors (such as inability to back-up, inability to convert or upgrade data-files, etc.) commonly presenting themselves as the result of a significant number of file fragments. But typical QuickBooks users asks Why do I care about fragmentation? The answer is clear, all forms of fragmentation, at the very minimum increase the time needed to find the specific data being requested either from a drop-down menu in the middle of a transaction, or viewing a particular Names’ history, or when compiling a report. At the very worst fragmentation results in file corruption to the extent that the database becomes inaccessible.
There are two kinds of ‘fragmentation’ that can affect your QuickBooks data, disk fragmentation and database fragmentation.
Disk fragmentation (fragmentation of the computer’s hard drive) occurs because of the way the Windows Operating System stores files on the hard drive. Whenever a file is written or rewritten Windows attempts to store that file in one long string without any spaces. When the hard drive is new, with lots of free space, it is a relatively easy function for Windows to perform. But with the addition of more and more data to the drive, and modifications and deletions of data, spaces and gaps begin to appear on the hard drive. 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. The most noted side effect of this 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.
Because of the way Windows works the QBW file may become fragmented almost as soon as it is created, and it gets worse as the file grows because Windows does a really bad job of allocating contiguous disk space for new and growing files, even if a hard drive has just been defragmented. Thus a QuickBooks file starts 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.
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 QBW Company-file.
The second kind of fragmentation affecting QuickBooks is database fragmentation. Because of the QuickBooks data structure, tables, and indexes that are being used, fragmentation is another form of data-file corruption that can cause performance issues as well as using unnecessary space in the database. Database fragmentation is similar to disk fragmentation in that the data is stored in various places in the database file and not necessarily in sequential order or next to similar data within the database. Optimal performance occurs when data pages are as contiguous as possible within the database, and the data pages are packed as fully as possible.
Database fragmentation can be program related because the QuickBooks database grows in size as needed to satisfy requirements. Databases tend to create fragmentation as part of their operational characteristics, especially when tables within the database are growing rapidly, or when data is being ‘inserted’ or ‘deleted’ from tables frequently. See the headline illustration.
Deletions cause significant issues both because of the ‘deletion’ itself as well as the database response to the deletion. When ‘pages’ of data are removed from page chains due to ‘deletes’, the page chain is left discontiguous between the pages surrounding the deleted page.
As new database pages are allocated to satisfy the storage requirements of tables within the growing QuickBooks database, the new pages are allocated to unused pages within the table, and then to new free extents within the database. This results in a break in the contiguity of the page chain. The randomness of such data pages causes larger seek times. These larger seek times translate to slower response times for long-running queries and other seek operations thus resulting in increased QuickBooks latency experienced by Users waiting for their field to pop-up, their transaction to appear, or their report to compile and display.
So the next time you, or your ProAdvisor, look at the F2 Product Information page in QuickBooks, just remember those DB File Fragments displayed (no matter how few or how many) are a hint at underlying issues that need attention.