SQL Server for SAP Business One - Best practices

Introduction

For an optimized SAP Business One use, it is important to take good care of the SQL Server installation and configuration. Please considering the following settings while doing so.
These settings will help you to prevent critical performance issues in your environment.

1. General Prerequisites

For optimal performance, the platform Microsoft SQL is running on, needs to be tailored to fit the performance requirements. The steps below are absolutely critical and some of them are hard to fix after the server is installed. Please consider them before even buying new server hardware.

Storage

For good performance, a fast storage solution is key. On the majority of small and medium enterprise customers system, the storage is the bottleneck of database performance.
With storage solutions that use traditional hard drives to store data, the quantity of physical hard drives (actual platters and spindles) is important. The more drives, the faster the storage will be, as file access requests will be split up among all drives.
Further improvement can be achieved by separating different types of access behavior to different physical drives. This comes mainly down to saving the database transaction logs to it's own set of physical hard drives. The transaction logs are strictly sequential data transfers, while everything else is random access to a very large amount. Be sure to store any backups on separate drives as well and use off site permanent storage to secure them.
If you need to troubleshoot database performance, start with improving your storage.

Memory

Main memory availability is important for avoiding storage access in the first place. Memory is comparably cheap and the responsiveness of all database applications will improve if a lot of memory is available for caching.
The actual amount of memory required can be easily calculated. Adding up the database sizes of all databases in use will give a rough total of memory required for the SQL Server process. Add to this 8 to 16 GB of memory for the operating system and all other processes running on a server, and the total will be the requirement for best performance.
Using SQL Server 2014 Standard, the maximum usable memory is 128 GB. For SQL Server 2008 R2 Standard and SQL Server 2012 Standard the maximum usable memory is 64 GB. The host operating system can still make use of any additional memory for other processes and for file system caching. These limits only affect the SQL server process itself.

Power Savings

This topic is simple. On a database server, it is best practice to always switch power saving off. Usually database servers are under high load and will never fall asleep. So the only effect of power saving will be slower response times. For best results, set your operating systems power options to "high performance". Be aware the default setting is "balanced" so this change has to be done on every new server.

Antivirus

Protection against viruses, trojans and other kind of malware is very important. However, antivirus software can interfere with database performance, if the actual database files are being scanned real time. It's a good practice to always define exceptions for the directories where .MDF and .LDF files reside. These files must not be protected by antivirus software to prevent file system locking issues.

2. Installation

SAP delivers a preconfigured SQL Server installation setup which considers most of the necessary settings. It is important to double check some installation settings before finishing the installation setup.

Feature Selection

If you're not using or planning the include the Coresuite Cube to your SAP B1 Installation the following features are required:

• Database Engine Services
• Client Tools Connectivity
• Documentation Components
• Management Tools - Basic 
• Management Tools - Complete

If you do not use Coresuite Cube, please skip the next step as analysis and reporting services are not required.

Additionally to the default installation features the Coresuite Cube will require as well the following features:

• Analysis Services
• Reporting Services - Native

Please be aware that the above services increase memory requirements of the server almost by two.

Collation Settings

The required collation setting for SAP B1 is SQL_Latin1_General_CP1_CI_AS 

Seperate physical drives for MDF and LDF

As described in the Storage part it is really important to separate the physical drives for the MDF and LDF file.
In that case it is necessary to change the path for the MDF and LDF for every database in the Properties of the database.

The printscreen below shows an example where C:\ is on the physical Drive A and D\ is on the physical Drive B.

Expamle to illustrate that the MDF and LDF files have to be on a separate hard drive and not just a virtual drive.

3. Configuration

To optimise performance even further, some easy configuration steps can be taken. These steps are mainly necessary to adapt the MS SQL Server installation to modern day operation environments. Some default settings of MS SQL are rather konservative in nature.

Adjust database growth

MS SQL Server will automatically allocate space on the storage for you. However, if this happens too often, performance penalties can be the result. Whenever new disk space is allocated, make sure it's a good chunk of space that will last for a while. With modern storage devices the value to recommend is 500 MB or 1 GB for both files. 

Configure this for all application databases on your server.

Adjust system database growth

Repeat the above steps also for all system databases shown below.

The databases master, model, msdb and tempdb are usually not very huge, so the optimal value is 100 MB to 500 MB.

Memory Settings

MS SQL Server will use main memory to cache execution plans and query results. This is a very good thing as it will prevent storage access and can speed up queries by a huge amount. Unfortunately the SQL Server can get greedy, so for overall best server performance it is recommended to set a limit for maximum memory consumption so other applications on the server will not choke.

The optimal value is to allow for as much memory as possible, while retaining enough memory for the operating system and other apps on the server to be able to work fine. Example: If your server has 64 GB of main memory and is hosting not only MS SQL but also other apps like SAP Business One and other services, a recommended value would be 48 GB for SQL and 16 GB for Windows and other services.

Parallelism Threshold

To prevent the SQL scheduler to overly make use of parallelism, adjust the Cost Threshold for Parallelism to a value higher than it's default, which is 5.
As a general rule, the higher the single core performance of your servers CPU is, the higher this value should be. For modern systems (as of 2016) use a value between 10 and 20.

4. Maintenance

To prevent data index fragmentation during operation, please see the following FAQ. It will guide you through installation steps to establish a periodic service that will tidy up all indexes. The goal is to prevent database slowdown after prolonged use. The recommendation is to run a job like this weekly or at least monthly.

Look here for further instructions: Locking and Blocking