The blog shares my experience about the important best practices to consider for SAP Business One landscapes for the database servers and some aspects of the hardware configurations.
During my years of troubleshooting performance issues for SAP Business One Implementations, I have seen a pattern emerging in most of the cases, which direct the root cause to the hardware/ server configurations and especially for the database servers. As you are already aware of the Business One architecture and know how important the database layer of MS SQL server is, hence a correct configuration and implementation of the database server is of utmost significance.
Having said that, let me take you through some basic best practices , which are often ignored, but if implemented correctly, help the product perform better than most of us can imagine.
Let us start with some basic MS SQL server configurations:
Maximum SQL server Memory : This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. Ideally, I would suggest it to be set at 70%-80% of the physical RAM available to the database server. The question arising now about the 70%-80% mark, can be answered by presuming to leave about 2GB for the windows OS to work, plus the memory for the internal worker threads , plus 1GB for the multi-page allocations, plus 1-3GB for other applications (if necessary.) Consider the calculation as a worst case scenario, to make sure the server is not paging out to disk. For example:
Physical RAM MaxServerMem Setting
This setting is dynamic, what it means is that it is enabled as soon as you change the settings in the SQL server. For changing the values,
- Through the SQL query editor :
-- Turn on advanced options
EXEC sp_configure'Show Advanced Options',1;
-- Set max server memory = 2048MB for the server
EXEC sp_configure'max server memory (MB)',2048;
- Through the SSMS GUI:
Lock Pages in Memory : This is a windows group policy, which determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Now, you'll find a lot of articles about this on the web and quite a few debates too, weather or not to enable it for database servers on windows machines for better memory management.So, to cut to the chase, as per my experience, enabling this feature for 64 bit operating system having SQL server mounted on them, has always helped the performance. So, if you have a 64 bit Database server operating system,this section is worth reading.
What it basically means, for 64 bit systems, it makes sure SQL Server’s memory is not paged out to disk. Otherwise, Windows may push this to the disk.
To lock memory pages, the SQL Server process needs to have the “Lock Pages in Memory” Advanced User Right in Windows which is achieved by granting the permission to the SQL Server service account. This permission is not granted automatically, even if the service account is a member of the Local Administrators group.
The following Microsoft article will help to know, how to enable it :
Once, this is successfully enabled, on checking the error log in SQL server, using 'XP_readerrorlog', you can see the following message displayed:
'Using locked pages for buffer pool'.
Maximum Degree of Parallelism (MAXDOP) : Every query executed in SQL server has a execution plan to it. Execution plan is the path and the operators and resources a query takes to complete itself. Some query designs have parellel plans, that means that a parallel operation is created for SQL Query and there are multiple threads for a single query. Now, MAXDOP value configured(or default) then determines the scheduling of these threads over multi core CPUs.
For more information on MAXDOP, refer to : http://msdn.microsoft.com/en-us/library/ms181007(v=sql.105).aspx
Now, during SQL server performance issues, I have observed many cases where the SQL server waittype CXPACKET is quite prominent as the maximum waittime for SQL query executions. This waittype directly relates to the configured value for MAXDOP. A default configuration of '0' is provided as a default installation of SQL server. As SQL server is expected to manage parallel plan execution and CPU selection. This '0' means that for every parallel plan execution, all CPU cores can be used for scheduling of the query threads. For example, in case you have a 16 core CPU and the MAXDOP value is '0' , then it will have to be scheduled on all 16 cores of the CPU.Now, this could lead to a performance issues in many scenarios. How? If the MAXDOP value is set to '0' in a 16 core machine , which hardly has parallel plan queries or lesser amount of concurrency, then all the parallel queries will have to still wait to be scheduled on all the 16 cores, hence a performance delay could be seen. Similarly a MAXDOP value of '1' means that all parallel plan queries will be scheduled on only 1 core of the CPUs available. Now, this could lead to huge performance problems, for highly concurrent systems or database applications which have a lot of parallel plan queries. Hence the MAXDOP value needs to be tested and set very carefully. It totally depends and differs from specific implementations as per their business processes. Hence, my recommendation here would be , during the testing phase, in a development server, this value should be tested gradually , by incrementing the values one by one untill the system performance is stable and the best possible. But a straight value of 0 or 1, should always be reconsidered. You can change the MAXDOP values here under SQL server properties:
Or using the SQL query editor:
sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
--Setting the MAXDOP at 2
sp_configure 'max degree of parallelism', 2;
RECONFIGURE WITH OVERRIDE;
Let us shift our focus towards storage now. Majority of the performance bottlenecks originate from storage or Disk I/O. To optimize the Disk or storage performance of a database server , here a few best practice tips:
Let us start with some Microsoft best practices for storage:
These guidelines recommend that all SQL Server log files should be placed on RAID 10 (or RAID 1) arrays,rather than RAID 5. RAID 10 excels at handling large write operations on SQL server log files.The faster the logs can be written, the faster transactions can take place.In addition, when choosing the right RAID level for a particular database server, it must be taken into account as to how many dedicated drive bays are available for a given database server. Servers with locally attached storage may only have a handful of drive bays and offer a limited amount of RAID configurations,whereas servers attached to a storage area network have a huge number of possibilities with more spindles, more cache, and more RAID flexibility.But , on the other hand, a storage area network may cause I/O problems if the physical drives for the database server are shared amongst other applications or server/ customers. So, a dedicated physical drive allocation is a must for a database server.
Some more storage tips :
Physical disk and database files
- Physical server RAID policy
- Use the ‘not read ahead’ for the RAID discs where SQL server database are stored.
- The SQL Server databases should be stored on a separate array (discs) as the server operating system.
- The TEMPDB
- Should be stored on a ‘RAID 1’ configured disc for performance.
- The DB setting for auto-shrink should be disabled
Ensure the tempdb files are big enough and have sufficient free space to avoid ‘dynamic growth’ of the database during the operations.
- The SAP company database
- Ensure the DB setting for auto shrink is disabled
- The recovery model should be ‘simple’. Further, please ensure a regular SQL backup job or a backup service.
- Ensure the DB files are big enough and have sufficient free space to avoid ‘dynamic growth’ of the database during the day. Auto-growth should be enabled where the growth percentage or absolute MB’s should be high enough to prevent dynamic growth from happening too frequent.
- The MDF should be placed on a RAID 10 configured disc
- The LDF (since the recovery model is simple) can be placed on a separate array RAID1 system but if a separate array with RAID 10 is available, this is also possible.
If the above basic parameters for CPU, memory, Storage and SQL server configuration are followed and implemented, majority of the performance bottlenecks can be avoided on the production servers.
In addition, to the above, some basic maintenance tasks like database re-indexing and update stats should always be configured as SQL jobs on the database server. For highly concurrent systems, these tasks should be configured for a daily run. For more information, please refer to DB optimization tasks on the SAP service market place on the performance landing page:
HTTP://service.sap.com > SAP Channel Partner Portal > Login > SAP Business Management Solutions > SAP Business One > Support > Support Self Help and Library > Performance.
Also, during hardware selection for the production database server, please keep in mind the following:
- Nature of Business processes (concurrency high or low)
- Number of users (current and forecast-ed)
- Throughput of transactions per day (ex. Number of invoices per day, for measuring DB growth aspects)
** Please note that the above recommendations are a result of my experience in performance issues troubleshooting for Business One customers. SAP Business One Product Support scope currently does not cover hardware troubleshooting.
In the coming few weeks , I would be covering more specific topics on database performance for SAP Business One systems and troubleshooting techniques. Enjoy the read till then and leave your questions as comments and I shall respond to them.