QB Bottlenecks - Boosting RAM
For years, one of the most significant performance bottlenecks affecting QuickBooks, especially for users with large Company files, resided in the QuickBooks Database Server. People would say things like "QuickBooks simply isn't designed with an adequate database to support files larger than 512 MB." The reality was that it wasn't the QuickBooks database which makes use of the Sybase database structure, or the Sybase Database Server Intuit adapted for QuickBooks, the problem was the 'limitations' Intuit had imposed on the Database Server's ability to make use of the hosting computer's RAM Cache memory. In order to get around this bottleneck, some of us began using a work around involving manual changes to the Windows Registry to 'trick' QuickBooks into using more memory than Intuit's imposed limitations and that worked pretty well until the introduction of Enterprise 13.
More than a year ago I wrote an article in another blog ("thanks Charlie") about the limitations Intuit had posed on QuickBooks use of RAM Cache memory. That article stemmed from several factors of historical nature as noted above, but became paramount when users migrating to QuickBooks Enterprise 13 suddenly started having significant performance issues they had not been having under Enterprise 12. Changes in the Enterprise memory manager was actually knocking down the amount of RAM Cache allocated to Enterprise below the standard defaults that had been used for years; in simple this meant that Enterprise 13 was using less memory than Enterprise 12 had been using. So with that history behind us let’s review how QuickBooks makes used of RAM Cache memory.
The QuickBooks Database Server controls the flow of data between the QuickBooks application and your QuickBooks Company file; it is also the Database Server that make use of your computer’s RAM resources in managing the flow of data. When QuickBooks is shut down the QuickBooks Company file stored on your Hard Drive holds a complete copy of all your financial data. When QuickBooks is started and users begin to access data the Database Server immediately begins to load the data from the QuickBooks file on the Hard Drive into RAM Cache memory.
If QuickBooks is using only a limited amount of RAM Cache memory (like 512 MB, the standard default maximum for QuickBooks Enterprise) and your QuickBooks file is in excess of 512 MB (like 700 MB), then your entire file cannot be loaded into RAM Cache memory. This means that the Database Server will need to write back information from Cache to the Hard Drive and then load additional data from the Hard Drive into Cache memory. The more frequently this occurs, the greater performance declines even with ‘very fast’ (10,000 RPM) hard drives.
On the other hand, if QuickBooks could use 2 GB of RAM Cache memory, even if your Company file was in excess of 1.5 GB, the entire Company file could be loaded into memory, significantly improving performance. 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, or where multiple Company files maybe in use simultaneously. There are a couple of important notes that need to be injected at this point, your computer’s available RAM which might be allocated to QuickBooks must be considered in light of the computer’s total RAM and ALL other operating requirements. You need to have a very good ‘feel’ for the amount of RAM your computer is using during ‘peak’ activities before you start undertaking any steps to change the allocation of RAM Cache memory to QuickBooks.
While it might be nice (for me) to take credit, by virtue of the critical articles I wrote on Intuit’s limitations of RAM, as the basis for their change of heart which came about approximately 6 months after the release of Enterprise 13; it is more likely the screams and shouts of their Enterprise 13 users who started crying foul regarding their file performance after conversion. In addition those users who had previously made use of the process of making ‘manual changes’ to the Windows Registry in order to ‘trick’ QuickBooks into using more RAM Cache memory were also finding that something in the Enterprise 13 memory manager was causing the manual Registry settings to either be ignored or overcome. Apparently when Intuit recognized that they had some real issues with how Enterprise 13 was behaving (or perhaps ‘misbehaving’) with respect to RAM Cache memory allocation, they began to openly discuss this topic and then responded with changes in their philosophy about allowing the users to determine memory allocation. Shortly thereafter Intuit provided 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. With that said I must take another opportunity to remind you that care should be taken before using any of the procedures outline in either that article, or in the steps below, in order to modify any of the settings addressed, in order to protect your overall computing environment.
So with that history and understanding of how QuickBooks uses RAM Cache memory, we have reached the point where we need to discuss reducing (or eliminating) the Database Server bottleneck by improving QuickBooks performance through allocation of additional RAM Cache memory to the QuickBooks Database Server. I am only going to address Intuit’s recommended method for making changes to the QBW.ini file when working with QuickBooks Enterprise 13R6 (or above), as well as QuickBooks Enterprise 14 (all releases to date). This method is less complex, and ‘touchy’ then making manual changes to the Windows Registry, and it also tends to ‘stick’ both when the computer acting as the ‘host’ must be re-booted, and when changes (updates) are made to QuickBooks on the host computer. You will note that I am talking about your ‘host’ computer for QuickBooks; these steps should NOT be used to increase the working memory capabilities of QuickBooks workstation computers. This procedure is designed to improve only the QuickBooks Database Server's performance.
With that said, my next word of caution is….."ALWAYS back-up or make a windows safety copy of your QuickBooks Company (data) file(s) before making any programming change of this type." To use the QBW.ini file to set the amount of RAM Cache memory you want QuickBooks to use follow these steps:
- Go to the computer (server) hosting your Company file(s).
- Insure that everyone is logged-out of QuickBooks.
- Go to the QBW.ini file in the Intuit folder (using the appropriate path below based upon your 'operating system'):
- Windows Vista and later: C:\ProgramData\Intuit
- Windows XP or Windows Server 2003: C:\Document and Settings\All Users\Application Data\Intuit\
4. Click the QBW.ini file. (Note: This file is different from the one you can access directly from inside the QuickBooks application.)
- Add a [DBSERVEROPTIONS] section with your chosen values.
- [DBSERVEROPTIONS] InitialCache=256 (if set to 0, meaning “None”) - 256 is the default amount. MaxCache=512 (if set to 0, meaning ‘Unlimited’) - 512 is the default amount.
- Notes about the InitialCache settings: The initial cache is used until the first company file is opened. The cache adjusts at that time. By default QuickBooks Enterprise will use 256 MB for the initial cache allocation.
- Notes about the MaxCache settings: The max cache is the maximum amount of memory QuickBooks database manager will use to communicate with the QuickBooks Company (data) file(s). By default QuickBooks Enterprise will use 512 MB for the maximum cache allocation. Increasing this MaxCache setting should improve performance, especially for files larger than 512MB. Intuit says that you can set the MaxCache Setting to "0" which makes it "Unlimited", they then go on to say that it is not truly unlimited but actually "Database managed".
Murph’s Commentary and Recommended settings: "First, I do not recommend that you use the '0' (unlimited) settings; I have not seen this work as well as using specified amounts. Second, my opinion is that the QB Database Server should be given as many 'multiples of 512 (MB)' as possible without compromising other computer functions." For example =2048 represents 2 GB, =3072 represents 3GB, and =4096 represents 4 GB.
"In most cases 4GB is sufficient for any 'single' QuickBooks file; however, if you are running multiple simultaneous files you may need to extend the Cache capabilities of the QB Server accordingly. Remember the 'same' QB Database Server is supporting every QuickBooks Company file in use. A business that has 30 Enterprise users, with 3 or 4 each using a different QuickBooks Company file simultaneously, needs to have the Database Server RAM Cache allocation based around the 'combined sizes' of all of those files, not just the single largest file."
5. After you have designated your InitialCache and MaxCache settings, you must SAVE the QBW.ini file. (Important Note: For these new QBW.ini file settings to "take affect" you need to turn off Hosting from the file menu and turn it back on. This will disconnect any user's sessions that are running at the time.)
You can 'monitor' the effects of these settings changes from the "Processes" tab of the Windows Task Manager. Prior to making any changes in your settings, run the Verify Utility and observe how much Working Set/Peak Working Set Memory the QBDBMgrN.exe process (QBDataServiceUser##) is using through the process. After you make your changes repeat the Verify Utility and see how much more memory is used during the process. You should also observe the length of time it took for Verify to run both prior to and after the MaxCache settings are changed, chances are you will see a significant decline in the time required to Verify the file. You can always open the QBWin.log file and actually see the time taken for each phase of the Verify process (before and after) memory changes, my opinion is that 'you will be impressed' by even a slight increase in memory allocation to the Database Server.
As you go about getting ready to make these performance improvements just remember to observe and follow all of the 'safeguards' that I have addressed within the article. If you follow the procedures and observe the safeguards any performance bottleneck you had centering upon the QuickBooks Database Server should be 'opened wide' (full speed ahead) after making the changes.