Technology Topic – How QuickBooks uses Cache Memory
I have posted several ‘blog articles’ and National Advisor Network discussions on QuickBooks use of RAM, and been somewhat critical of the approach Intuit has taken in the past, to limit how much RAM QuickBooks makes use of. Every time I write concerning this topic, I receive numerous emails asking questions about why RAM memory is so important to QuickBooks. So I wanted to provide some technical information about how QuickBooks makes use of Cache memory which is directly dependent upon the amount of RAM installed in your computer.
The QuickBooks Database Server
QuickBooks is composed of three major ‘components’, the application, the database server, and the database file, which is your QuickBooks Company (*.QBW) file. We all know what the QuickBooks application is, it is what most of us think about when we talk about QuickBooks, the user interface by which we do our work. And we also know what our Company file is, and realize that it stores our data on the computer’s hard drive. But what we may not realize is the importance of the other component, the ‘database server’.
Several years ago, Intuit migrated QuickBooks from a C-index database structure to a Sybase database; QuickBooks provides two versions of the Sybase database server, which controls the flow of data between the application and the Company file. The personal database server executable is installed when QuickBooks has been set-up as a stand-alone application and QuickBooks and the data-files are on the same machine. The personal database server is provided for single-user, same-computer use, as an embedded database server. This database server runs as a windows process under the logged-in windows user’s account as QBDBMgr.exe.
When QuickBooks is installed for networking, or ‘hosting’ is turned on in the QuickBooks application, the network database server is activated; this server supports client/server communications over a local area or terminal server network, and permits multi-user use. Because the network database server can run unattended, and is not reliant upon an actual user being logged into QuickBooks, a unique windows user account is created for the server. The network version of the server is QBDBMgrN.exe, and the User Name is QBDataServiceUser ## (where the ## represents the year of the product; for example, 23 is the year designator for 2013).
QuickBooks also provides an interface utility for the network server version, called the QuickBooks Database Server ‘Manager’, but that is a topic for another day.
QuickBooks use of RAM Cache Memory
Once again, it is important for you to realize that it is the Database Server (personal or network) that controls the flow of data between the QuickBooks application and the QuickBooks company file, it is also the Database Server that make use of your computer’s RAM resources in managing the flow of data in order to provide the best possible performance.
The various organized components of the QuickBooks database, such as list and transaction tables, contain the actual data of your QuickBooks company file, and other ‘internal’ elements of the data-structure. This data is stored as “pages” which are fixed size portions of the computers’ hard disk; Intuit has chosen to use a data page size of 4096 bytes. These database pages store the data in a defined format depending on the type of page (data, index, etc.).
When QuickBooks is ‘shut down’ the QuickBooks Company (*.QBW) file should hold a complete and current copy of all of the pages that encompass the data; the pages are supposed to be cleanly written to the computer’s hard disk, as opposed to ‘dirty pages’ which are pages entered into the buffer cache during operations while the database is in use.
When the database is started and users begin to access various tables of data for transactions or lists, the database itself is not generally current rather the database server begins reading the appropriate pages from the QuickBooks file into RAM memory where they are held in cache. At the same time the database server also makes a copy of the original page (before any changes are made).
These ‘copied’ pages are written in a checkpoint log which is located at the end of the database file. Pages are added to the checkpoint log as needed while the database is in use, and the entire checkpoint log is deleted at the end of the session once all dirty pages (containing new or edited data) are written back to the disk.
In order to enhance performance, changes made to the pages of data are posted to the copy of the data in cache memory; they are not immediately written to the database file on the disk. When the database server is idle, or when the cache is full, the changed pages (of new or edited data) get written back to the Company file from cache. This action is known as a checkpoint, and occurs when all dirty pages are written back to the computer’s hard disk.
After each checkpoint, the checkpoint log’s contents are deleted. The empty checkpoint log pages remain in the checkpoint log during each session and are reused for new checkpoint log data. At each checkpoint, all the data in the database is held in the Company file, the information within the database file should match the data recorded in the transaction log. The purpose of the checkpoint is to always reflect a specific point in time when a consistent state of the data on the disk existed.
The QuickBooks transaction log, the *QBW.TLG file is a separate file from the QuickBooks (company data) file. It stores all changes to the database, inserts, updates, deletes, commits, rollbacks, and database schema changes. The transaction log is one of the primary components of the overall integrity of the QuickBooks ‘database’ by providing redundancy and possible recovery in the event of data loss or corruption. A few years back, Intuit made available a mechanism for potential recovery of data loss from certain types of events through the Auto Data Recovery capabilities now available in most current versions of QuickBooks.
Conclusion
Your computer’s RAM greatly affects the performance of QuickBooks which makes use of Cache to control the orderly and safe flow of data between the QuickBooks application and your Company file. As a general rule, the more RAM Cache made available for use by the QuickBooks Database Server the more efficient QuickBooks will operate, especially in a multi-user environment; however, available RAM must be considered in light of the computer’s total RAM and other operating requirements. While Intuit has recently provided a technical support article (SLN65343 “Improve QuickBooks performance by verifying it is using the correct amount of memory”) on the topic of improved performance and RAM, great care should be taken in modification of any of the settings addressed in that article in order to protect your overall computing environment.