Last time we looked at why fragmentation is the most common form of data corruption, we identified the No. 1 culprit producing fragmentation as the Windows Operating System. We also examined the way the Windows I/O Disk Sub-system writes data to your hard drive.
We also reviewed using the Windows Defragmentation tool for analysis and defragmentation of your hard drive(s).
In this article, we want to look at how fragmentation, including a slightly different form called "database" fragmentation, impacts your QuickBooks data.
Database fragmentation is similar to disk fragmentation in that data is stored in various places and not necessarily in sequential order or next to similar data. Database fragmentation can cause performance issues as well as consuming unnecessary space within the QuickBooks data structure, tables and indexes.
This form of fragmentation occurs when data records are stored on data pages in other than a contiguous fashion. To understand this form of fragmentation, we must make certain we understand how data is actually stored.
The various organized components of the QuickBooks database, such as list tables and transaction tables, that contain the actual data of your QuickBooks company file, as well as other "internal" elements of the data-structure are stored as pages that are fixed size portions of the computers’ hard disk. Database pages store the data in a defined format, depending on the type of page (data or index).
By definition a data page, sometimes known as a block, is 2KB in size or larger. While it holds one or more rows of data, a row must always exist on a single page. In QuickBooks, the page size is 4096 bytes. You'll find that on the F2 Product Information window, right below the File Size.
There are two types of pages – index pages contain rows of "index data" and the page header. Data pages contain only rows of data in addition to the page header.
The page header, of either type of page, contains the identification information that defines the page (index or data) and contains references to both the previous page and next page. The header also identifies the type and characteristics of the remaining data on the page.
Typically pages are arranges in groups of eight continuous pages called an Extent. An Extent typically contains one Index page and seven data pages. In Windows architecture, there are some alternative page configurations when a page containing a portion of the disk allocation map is written to an extent.
As I mentioned above, QuickBooks developers have chosen a data page allocation schema that contains 4096 bytes, each page holding one or more rows of data. The pages in the extent should be grouped contiguously, such a proper grouping is know as a page chain, and each extent should then be chained together contiguously as well.
Optional performance occurs when data pages are as contiguous as possible within the QuickBooks database and the data pages are packed as fully as possible.
The problem arises in that QuickBooks users don't use the product in a "contiguous" fashion. Through normal use involving the constant inserting, updating and deleting of QuickBooks data causes the data to become fragmented.
As new database pages are allocated to satisfy the storage requirements of tables within the growing QuickBooks database, the new pages are first allocated to unused pages within the table, and then to new free extents within the database segment.
This frequently results in a break in the contiguity of the page chain. Such randomness causes longer than optimum seek times during table scans and queries translating to slower response times. By definition, that's fragmentation.
SQL databases, like QuickBooks, tend to create fragmentation as part of their operational characteristics, especially when tables within the database are growing rapidly. Deletions of data pose significant issues both because of the "deletion" itself as well as the databases 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.
Fragmentation of these types can manifest itself in many ways, including messy page chains, poor page utilization, extent fragmentation and row fragmentation. Broken page chains are one source of data corruption that can impact QuickBooks, as with all other databases.
Since most QuickBooks data is stored on a server, the issue is how to resolve this fragmentation, without impacting other server operating requirements. Most Windows’ Server Administrators become "ill" at even the thought of running the Windows Defragmentation Utility on a server hard-drive.
ProAdvisors must therefore consider alternative tools or methodologies that will not involve Defragmenting an entire Server drive (volume).
One such methodology is fragmentation prevention. In Part 1 of this series, I mentioned how Windows could be configured to analyze and defragment hard drives on a scheduled basis as a means of substantially reducing fragmentation, essentially preventing major fragmentation if the scheduled procedure is performed often enough.
But again, we're still talking about running disk defragmentation.
Historically, fragmentation has been dealt with after it happened, even the scheduled defragmentation option under Windows is geared toward fixing the problem, nor preventing it, but fragmentation prevention via perpetual drive optimization can be accomplished with the right software.
Proactive solutions, like those available from Condusive, optimize by insuring that data is written sequentially, the result is a significant reduction in seek time for subsequent reads, as well as a reduction in the number of I/Os required to write a file or access it.
The benefits of fragmentation prevention are not simply in the act of preventing fragments or in the resultant need to perform defragmentation, but in the performance gained when accessing and reading files.
Diskeeper by Condusiv
Diskeeper by Condusiv
In order to attain the best performance, the prevention solution must be intelligent enough to factor in the placement of data for future access as well as in avoiding the creation of an actual third form of fragmentation known as "free space" fragmentation.
Otherwise, the solution simply will trade one form of fragmentation for another. Preventing file fragmentation at the expense of creating free space fragmentation, eventually will result in fragment files regardless of prevention method, taking us right back to square one.
There are other products out there – Diskeeper, V-locity and SSDKeeper by Condusive – that have proven, within my own ProAdvisor practice, to be the optimal solutions for proactive fragmentation prevention and disk optimization.
Turning our attention back to resolving fragmentation of QuickBooks data, any method that compresses the QuickBooks data can reduce or eliminate fragments. In effect, the "spaces or gaps" are squeezed out as the data as it is compressed.
Sometimes, simply making a Windows copy of the data will compress the file and reduce the number of db fragments, but on many occasions this has little effect.
Another method of compressing the data is using the QuickBooks Portable Company file utility. Compression of the file is accomplished, in part, through removal of all indexes within the data structure.
This process frequently removes fragmentation within the database itself as well. When a Portable Company File is restored, QuickBooks builds new indexes for the file not only improving file efficiency, but leaving the database free of fragmentation.
But if your Windows disk drive is highly fragmented, it's not uncommon to see the QuickBooks file, restored from a Portable Company File, have even more fragments than when you started the process.
This is because Windows does not truly overwrite your QuickBooks file with the restored file. It simply fits the restored file components into available space on the hard drive, and then once the file is confirmed fully restored, Windows deletes the prior data set.
In some cases, I have used a "windows"-style defragmentation utility upon just the QuickBooks data file. In order to do this, the entire "directory" containing the QuickBooks (*.QBW) file was copied to a suitable size flash-drive or other portable device that is free of other files.
Regardless of the type of device, it's necessary that the device have a capacity of at least twice the size of the entire QB directory. It it is preferentially three times the size, it will permit faster defragmentation and restructuring of the database into continuous pages on the drive.
Once the copy has been made, the defragmentation utility can be run on the drive containing only the QuickBooks data files. As a general rule, this process will take about two or three times the length of time that was necessary to "copy" the data to the drive.
Once the process is complete, the entire contents of the drive can be copied back to their original location. But the original data files at the location should be moved to a safe archive directory prior to the transfer of the defragmented data files.
But again, if the Windows hard drive is seriously fragmented, you may still end up with a QuickBooks file that's just as fragmented as it was, if not more so.
While fragmentation can and does impact Solid State Drives, they're less likely to significantly suffer the consequences in the form of performance degradation associated with I/Os in excess of the optimum.
It maybe that SSDs are not only the most optimal of solutions to eliminate the impact of fragmentation, but the most economical solution when it comes to system maintenance, energy savings and performance.