SQL Server maintenance tips – Part 1

As already announced in the blog post “Optimal setup of the SQL database”, this time we provide you with some handy tips for maintaining the SQL server. Because even with proper operation, some problems occur over time, which can lead to a further loss of speed. That’s why you should always tighten the screws on your SQL server – you don’t let your car rust away over the years.

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 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 adapted. 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 settings after following the steps above:

• Limits the random access memory to 1 gigabyte to limit 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.