You may be familiar with the following scenario: You want to set up your first online store, download JTL-Wawi and install it. Then you quickly set up the Microsoft SQL server and off you go to enter the items and set up the store. Not all that difficult, is it? But over the next few months, you increasingly have the feeling that the Wawi is running more and more slowly. This is neither a bug nor a feature – the SQL server provides the solution. Because the optimal setup of your database contributes significantly to the efficiency and speed of your ERP.
Databases - the what and why
JTL-Wawi uses Microsoft SQL databases as the central storage location for all databases. This is where our software stores item and customer information for the respective client of the software, but also any data on orders, eBay or Amazon. As all Wawi installations within a company need to access the same database, this explains their decisive influence on overall speed. The higher the number of users on a possibly slow and poorly set up SQL server, the longer it takes the system to process the desired data requests.
The hardware basis
Choosing the right hardware is a decisive factor for fast data access to an SQL server. A processor that is as powerful as possible allows the system to process all queries in the shortest possible time. We also recommend the use of a total of four hard disks. As an SQL query involves several write actions, the use of several hard disks can achieve a significantly faster data throughput. Ideally, users should set up the data accesses as follows:
Ideally, users should set up data access as follows:
Apart from the operating system, the SQL server is the only installed program on the computer
Installs the SQL server on the same hard disk on which the operating system is located. (Hard disk 1)
Saves both the .mdf file and the .log file on separate data media (hard disks 2 and 3)
The TempDB files are also stored on a separate disk, preferably the fastest one (hard disk 4)
While users of the Express Edition already meet the recommended hardware requirements, there are some differences for owners of the Standard, Enterprise or Web Edition. As they can use more than one CPU per query and more random access memory, we recommend the following in addition to the points mentioned above:
As many CPUs as possible to improve the overall computing power for the large databases
At least 8 gigabytes of random access memory to be able to use as much data as possible from the fast cache.
Once you have fulfilled these hardware requirements, the first important step has been taken. The infrastructure for rapid database queries has been created.
Software fine-tuning
A big engine doesn’t make a fast car – it’s the fine-tuning that counts. The same applies to software. Just because your hardware is exemplary does not automatically mean that everything runs optimally. The right settings are crucial.
Your SQL server can be set to different versions for maximum compatibility. From version 1.0, Wawi supports all versions between SQL 2008R2 and 2014. The compatibility levels are graded as follows:
SQL 2008R2: 100
SQL 2012: 110
SQL 2014: 120
For the best possible performance, set your server to the actual version level. For example, if you are using SQL 2014, the value should be 120. To do this, select the database in the Object Explorer via SQL Management Studio, right-click to open the context menu and go to Settings. There you will find the compatibility level under the Options tab.
An important factor for better server performance is the deactivation of Autoshrink and Autoclose in the database. Autoshrink is constantly trying to reduce the size of the .mdf file. The result: the server is constantly busy with itself and also fragments the hard disk. Autoclose in turn empties your random access memory every time all connections to the server are closed. This may sound Good at first, but unfortunately it is not. This is because many important files have to be reloaded into RAM when the next connection is established – again reducing the performance of the server.
If you now predefine some file sizes, you save the system from having to change them itself in future. At the same time, you create a continuous file on the hard disk, while the automatic expansion by the system creates a fragmented version. This also has a negative impact on the speed of the database.
Enlarge the .mdf file of the database to at least 1 gigabyte, in the best case you set a size of 10 gigabytes directly.
The .log file also needs to be adjusted. The ideal size for this is 100 megabytes.
You should also set the .mdf and .log files of the Temp-DB to 100 megabytes.
Special features of the SQL Server editions
Both the Express Edition and the Standard, Enterprise and Web versions have a few special features when setting up the software.
Users of the Express version should make the following setting after the above steps:
Limits the random access memory to 1 gigabyte to restrict the swapping of larger files to the hard disk.
Users of the Standard, Enterprise and Web versions make the following settings after the above steps:
For each existing CPU core, you should create another .mdf file in the Temp DB with a size of 100 megabytes and an enlargement of 100 megabytes. As the CPUs can work in parallel, they each have access to their own temp DB files. They do not all have to access the same one, a bottleneck is avoided. You can also change this during active Wawi operation.
Limits the random access memory to 80 percent of the actual available memory. This leaves enough resources for the operating system so that there is no loss of performance. If other Applications are running on the computer, a greater restriction to 60 to 70 percent is appropriate.
Limits the maximum number of CPUs working in parallel. If you are using more than 4, we recommend the “half the number of CPUs -1” variant. So if you have 8 CPUs in use, you should use a maximum of 3 in parallel. For 4 or fewer CPUs, the parallel use of 2 cores is ideal. Don’t worry, the rest of your processors will not remain idle in the meantime. With this definition, you only specify the number of CPU cores that work on the same query at the same time. This leaves your processors with enough power to continue processing other tasks and queries with the remaining capacity in addition to large database queries for more than one processor.
Conclusion
Once you have made all the settings accordingly, your database should be able to handle all types of queries much more smoothly. In order to maintain this status in the longer term, we will provide you with useful information on database maintenance in a future blog post.
