Intro

There are many ways how you can setup your infrastructure and SQL server for your SAP Business One project. It doesn’t matter, which way you implement, the following points are always helpful and increase your SQL server performance with SAP Business One a lot. Note, this post is not an SAP installation manual. If you are looking about installation guidelines, check the SAP Business One Administrator’s Guide which you can find in your SAP installation package.

sqlserver

Disk

Before you install any SAP and SQL software, you provide a server. An important point there is to think about the disk setup. Here you can create the Rolls Royce with plenty of disks processing lot of information. Or you can have a simple solution with fewer disks for smaller companies. Here it is not possible to give a general solution. But what I would consider at least: Do not save the log and data files of your SQL server on the same physical disk. Microsoft says following: “Placing both data and log files on the same device can cause contention for that device and result in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files.”

For more information, please see Microsoft Developer Network

Cluster size

A very important point, when we discuss about performance is the cluster size. The disks which store your SQL data and log files must have a cluster size of 64kb. This gives us about 30% more performance when you compare with a 4kb cluster size.

With the cmd-command chkdsk you can check, if your servers disk has 4kb or 64kb cluster size. Please select the correct volume to check it. The example below has 4kb, which is not ideal for an SQL server.

checkdisk

If you are interested in more details, check Disk Partition Alignment Best Practices for SQL Server from Microsoft.

SQL Server Settings: Max Memory

An important setting is the max memory in the server properties. If you don’t set here a useful value (total installed memory minus operating system minus other services minus some reserve), SQL server will take the whole memory, which can result in an unstable system.

MaxMemory

If you are interested in more information, check the useful blog of Pinal Dave: SQL SERVER – Optimal Memory Settings for SQL Server

SQL Server Settings: Ad-Hoc workload

Another useful setting is the Ad-Hoc workload, which you can find in the advanced settings of your SQL server. For SAP Business One installations, I recommend to turn this feature on.

AdHocWorkload

Database Settings

On many many systems, you can find database growth settings of 1MB, which does not result in a good performance. Think about a 10gb database, which is divided in 10’000 small files, spread on your whole disk: An absolute performance killer. When you create a new company database, ask yourself, what is a useful initial size. Ask yourself, how big the database could be in 3 years (check other existing databases). This could be a good value to begin, one reserved place on your disk with a good performance.

fileSizes

Also the growth of the database is important. Today, where everything is so fast in terms of processing, I would consider a bigger growth size. This helps to avoid hundreds of files in the end. Of course, when your growth size is something  fancy like 100gb, you have performance issue right then, when the database grows. But values like 512mb or 1gb, I don’t see any problems. But, please check the server and avoid settings like 1mb or 1% and avoid this.


Geri Grenacher

ERP Project manager at coresystems ag, Switzerland

3 Comments

Ian · October 31, 2014 at 11:16

Hi Geri

I have a question about the database growth setting. Lets say the DB was created and the growth setting was left as 1MB and now I change the 1MB to 1000MB, will this update the way the DB has written to disk? Or will this only have an affect on the performance and the way the DB writes to disk going forward?

Geri Grenacher · October 31, 2014 at 13:47

Hello. This will only affect the future increases. If you want to fix your database, just make a backup of it and overwrite the database by restoring the backup.

Tipps zum Aufsetzen MS SQL für SAP Business One | SAP Business One Blog · October 21, 2014 at 07:31

[…] Datenverarbeitung möglich. Wer nicht auf  SAP HANA zugreifen kann oder möchte, hat hier ein paar wertvolle Tipps für ein Microsoft SQL Set-Up für SAP Business One. Geri Grenacher geht […]

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *