RAM
If you call SOS with a complaint about software performance, the first thing a technician will check is the amount of RAM you have configured for the database to use for cache. The larger your cache, the more of your database that can be copied from the relatively slow hard drive to much faster RAM memory, and the faster your database can respond to the software’s requests for data. Insufficient cache because of too little physical RAM in the computer or because of a simple configuration oversight can be deadly, especially for reporting.
CPU
Everyone has had the experience of standing in a long line at a supermarket because there was only one cashier on duty. In most cases, at some point a manager will notice the log-jam and open additional registers. Waiting shoppers quickly spread out among the new cashiers, and in short order the situation is resolved. New shoppers arriving at check-out have little or no wait.
A very similar thing can happen with your SOS database server. Retrieving, adding, updating, and deleting data in SOS all take the form of database queries or commands. Each must be executed in turn, and the execution of these commands requires computing resources – specifically memory and CPU resources — on the server computer.
Recently SOS technicians received a couple of calls from customers who were experiencing poor performance in SOS. In both cases they discovered that the customers had adequate RAM but just one CPU core available on the database server to handle the work being sent to it by between 9 and 30 users. It was therefore no surprise that reports were taking “forever” to run, and that users were complaining about getting “locked up”. In that situation, frustrated users often resort to drastic measures, like rebooting their computers or force-stopping SOS while it is waiting to complete some process that has not run to completion. That just adds to the problem because the database must now also clean up the mess left by the rebooting users. The single CPU core is similar to the one cashier in the supermarket. He or she may be working at top speed, but to the person at the end of the line, it feels like nothing is happening.
For the store, the solution is to add more cashiers. For your SOS database, the solution is to move the database to a computer with more CPU cores. Fortunately, modern computers, even relatively inexpensive ones, typically have at least two to four core CPU’s. More and more frequently you will find computers with advanced processors that allow six or eight processor paths, even if there are only three or four actual CPU cores. The Intel Core i7, for example, is a high-performance, quad-core processor found in many computers in the $700 to $1,000 range, but which provides software with eight parallel execution pathways. In an environment of that kind, thirty to fifty users will rarely find themselves waiting around for very long!
In the screenshot below, showing the Performance tab of Windows Task Manager, you can see the eight parallel processing paths available on an Intel Core i7 CPU:
How Do I Know If I Have One of These Issues?
There is a quick and easy way to check your cache and CPU situation. From the top menu in OM or CM select Help > Version Information, then click the DB tab.
Examine the cache values (best done at the end of a busy day). If the peak size is less than the maximum size setting, you have ample cache, at least for now. If the numbers are the same, then it is likely that you would benefit from adding more cache, either by increasing the maximum size setting in your SERVER.PRM file, or if necessary, by adding more RAM to your system (if possible). For more detail about cache settings, see http://www.sosoft.com/fod/doc435-enhancing_perf_large_db.pdf
To see how many CPU execution pathways are available in your computer, check the “Logical Server Processors Available” line. If you see “1/1” here and you have more than two people on your system, you are going to really suffer. Even one person running a standalone copy of SOS can encounter issues in that situation and would see much better computer performance across the board by upgrading to at least a dual-core computer.
Some settings make use of virtual servers that can be configured to use a specified number of the host computer’s CPU cores. Often the default setting for virtual machines is just one processor core, even when the host system has four or more available. If your database is running on a virtual server and SOS shows “1/1” Logical Processors Available, you should adjust the virtual machine settings to allow it to use more of the host computer’s cores.
Database Settings Can Make a Difference
There are two internal database options that can make a significant difference in performance: max_query_tasks and optimization_goal. In most cases, the former should be set to 1, and the latter to “All-rows”. To determine your own settings, check in Reports > Other Reports in OM to see if you have the report “Database Performance Options”. If not, you can download it from here: http://www.sosoft.com/files/downloads/dbperfopts.rpt and install it in the Other Reports menu using these instructions: http://www.sosoft.com/fod/doc474-adding_new_report.pdf.
If max_query_tasks is not set to 1, you can change it using a tool on the tools menu in DBA Utilities. Using an SOS login with security administrator rights, click the Admin icon on the login window, then go to Database Tools > DBA Utilities. Click Set Max Query Task. That item is a toggle that switches the setting back and forth between 0 and 1. When set to 1 it should display a checkmark next to the menu item, but it is a good idea to confirm the setting with the report and re-toggle if it is not set as you wish.
There is no utility to reset optimization_goal, but an SOS support tech can establish a remote connection to your system and reset it for you.
Consider a Server that is not a Server
One thing that is important to understand is that it is not necessary to run the database on a computer that says “server” on the box, nor is it necessary to have Windows Server xxxx as the operating system on your SOS database computer. For example, instead of adding an official “server,” you can add a computer running Windows 7 Pro on an Intel Core i7 or similar CPU to your network, install a copy of SOS using the “server” option, move your database over, and fire it up. Remember that it is not recommended that you map the database server’s SOS folder as a network share, so Microsoft’s 10 user limit on non-server versions of Windows is not an issue. Database access over the network is not done with Windows shares and therefore does not count against that limit. You can therefore have dozens of users on the database even though it is running on Windows 7 rather than Server.When upgrading a server is not a possibility for one reason or another, this approach can provide the same results for a fraction of the cost.
Note that if you plan to use your server as both the database server and a terminal server, then you must run a Windows Server operating system (such as Windows Server 2008, or Server 2008 R2). You cannot use Windows 7 as a terminal server.
