Understanding How QuickBooks Uses RAM
While databases such as Microsoft SQL, Oracle, and Sybase, allow for allocation of RAM by increasing cache size as their databases grow, Intuit chose not to take this approach even though QuickBooks uses the Sybase database. Instead, they have always limited the use of RAM by setting specific cache sizes for their two levels of product: Pro/Premier and Enterprise. For Pro/Premier the default maximum is 256MB, and for Enterprise the default maximum is 512MB.
In order to make the database rapidly accessible, the database server makes use of “cache”, storing the data temporarily in the computer’s RAM memory. The QuickBooks database server will begin loading the file into cache until it reaches the maximum permitted. When data needs to be accessed that is not already in cache, cached data must be written back to the disk, and data on the disk must then be loaded into cache. Large QuickBooks files which exceed the RAM cache limitations suffer from performance issues, because more frequent cache swapping is required and the number of disk read/writes are increased.
For several years, some ProAdvisors and Computer Professionals have manually adjusted windows registry settings so that QuickBooks could make use of more RAM than the limits imposed by Intuit’s restrictions. However, these ‘tricks of the trade’ have never been openly discussed by Intuit personnel.
Performance Issues Lead To Changes
Around the start of 2013 many users of QuickBooks Enterprise 2012 and 2013 began seeing performance declines, and for many of these users the declines came after upgrading to 2013, or installing a maintenance release (for either version). In the case of a couple of my own clients, when we checked the registry settings, we observed that they were no longer ‘boosted’, but had been returned to Intuit defaults. Even though we could reset the registry settings, they appeared to ‘re-set’ immediately when the QBDatabaseServer database engine loaded. It seemed as though Intuit had injected some new code into the start-up routine that was always setting the RAM cache to Intuit’s defaults. Users began to complain, and complain loudly.
At the same time, users who had never made use of registry setting changes, were also seeing significant performance issues. It appeared in some cases that QuickBooks was making use of even less RAM then the standard defaults. Once again, users began complaining.
Prior to these issues arising, Intuit had never really addressed RAM cache performance for users. Their typical response to users complaining about performance was geared toward telling Pro/Premier clients to upgrade to Enterprise, and telling Enterprise clients to start a new data file. But with this new round of complaints (and perhaps my earlier article in this Blog on the effects of RAM Cache limiting QuickBooks capabilities), Intuit has apparently changed their way of thinking, as evidenced by the information now being provided in their Technical Support Knowledge Base Article (KBA) SLN65343 “Improve QuickBooks performance by verifying it is using the correct amount of memory”
In this article, Intuit implies that these performance issues may result when QuickBooks is installed when memory cache settings get changed to less than optional amounts of cache. They note that these incorrect values may “get stuck and seem to keep coming back” and that they have observed that “cache may revert back to lower (incorrect and non-default) values if the server reboots, a Windows update is installed, a QuickBooks update is installed, or even if QuickBooks runs a rebuild…” In other words, Intuit seems to blame the RAM Cache problems on almost any cause other than a “program glitch”, or intentional coding change.
Changes Intuit Has Made Available
The Intuit KBA outlines a variety of approaches regarding RAM Cache, something Intuit has never openly discussed in the past. However, it is important to note that they discuss these approaches only in relationship to QuickBooks Enterprise, not Pro or Premier.
For QuickBooks Enterprise 12 and earlier, they have developed a new ‘fix tool’ which will reset cache settings to the ‘default’ values of 256 MB initial cache, and 512MB maximum cache. The ‘fix tool’ can be downloaded from the KBA. The use of this tool is simple; it runs a batch file which resets the windows registry parameters to Intuit’s recommended values. Once the changes are made by the tool, the server must be rebooted to ensure that the changes take.
Alright, you are saying, that doesn’t sound like a ‘change in attitude’ by Intuit about QuickBooks’ use of cache, and you would be right. However, they proceed to also outline the basic steps for checking and manually changing the windows registry settings. This is unprecedented, prior to this KBA this type of information has been shared between QuickBooks experts who used these procedures to enhance performance for their clients. To my knowledge, Intuit has never made a recommendation or even discussed making such changes.
I am not going to go through the procedures in this article, because they are outlined in the KBA. However, I also want to point out Intuit’s own caution found in their instructions:
“The settings are stored in the Windows registry and need to be corrected there. Do not attempt this if you are not an IT professional with experience editing the registry.”
In the same KBA Intuit continues to surprise when they discuss QuickBooks Enterprise 13. They note “if you altered the memory settings in R5 or before, you may have to ‘re-enter’ the settings when the server updates or reboots.” They go on to note that “Using the .ini file settings…is more permanent and dependable.” What this means is that Intuit has now provided in QBES-13 a new way (without manually changing the windows registry) to configure QuickBooks use of RAM cache.
The KBA explains how to create a DBSERVEROPTIONS section in the QBW.ini file, and designate the InitialCache and MaxCache settings. While Intuit illustrates the 256 and 512 figures which have been the default, they also go on to explain about using zero ‘0’ values for each of the cache settings.
Setting InitialCache to 0 should have no impact on performance since this is only the amount of cache first assigned by the QBDatabaseServer until it begins to load the first file. In reality there is no reason to use the 0 setting, in fact any amount less than the 256 would seem to be counterproductive, in my opinion.
The MaxCache setting is the maximum amount of memory the QBDatabaseServer will use, and thus the maximum amount of the file data that will be loaded into cache. Intuit notes that “increasing this can improve performance”. The KBA then explains that “setting this to 0 makes it ‘Unlimited’”, but clarifies that “it is not truly unlimited but actually Database managed” so that the database server can use more memory when needed.
In Conclusion
Obviously the fact that Intuit has created a new method to enhance QuickBooks use of RAM cache, and explained the various possibilities intended to allow users to configure the best alternatives to use system cache in order to improve performance represents a significant deviation from their past practices. I for one can only commend Intuit for this needed change.
This post was written by William Murphy.