Onebeat Server Maintenance

Overview

This article was created to assist clients to Maintain Onebeat Server, in order to prevent errors and failed to load cases and to gain the best L&R’s performance. The maintenance is in the context of the following: 

• Onebeat Database
• Hardware
• SQL Server
• Partitions (relevant only for the multi-threading version)

The structure of this article is first a checklist of the required actions, and then the explanations and instructions for each action. 

Actions Checklist

Onebeat Database

#	Action	Recommended Frequency	Component	Limitations
1	Rebuild Indexes	Once a day/week	Auto Loader
2	Update Statistics	Once a week	Auto Loader
3	Compress DB tables	One time action	SQL Server
4	Delete unused MTS SKUs & history	Quarterly	DELETE_MTSSKUS	For large-scale (more than ~1M SKUs) use SQL script.

Hardware

#	Action	Recommended Frequency	Component	Limitations
1	Release unused RAM by restarting SQL server	Once a week	SQL server
2	Make sure there is a minimum of 20% free Space in Onebeat disks	Once a month	Disks

SQL Server

#	Action	Recommended Frequency	Component	Limitations
1	Limit the Maximum Server Memory	One time action	SQL Server
2	Change Database Recovery model to simple	One time action	SQL Server
3	Move the system database tempDB to a separate SSD disk	One time action	SQL Server
4	Shrink tempDB size (by restarting SQL server)	In case necessary	SQL Server

Database Partitions (Multi-Thread version)

#	Action	Recommended Frequency	Component	Limitations
1	Balance Partitions	Once a year / 2 years	SQL server

 Actions Explanations

Onebeat Database

Rebuild Indexes

The Autoloader argument is -IDX.

Read more about the Rebuild Indexes. 

Update Statistics

The Autoloader argument is -SQLUS.

Read more about the Update Statistics.

Compress DB tables

In most cases the largest tables are:

• Symphony_StockLocationSkuHistory
• Symphony_StockLocationSkus

In addition to the above tables, there might be other big tables that can be compressed and reduce dramatically the DB size. 

In order to find the Disk Usage by Top tables, do the following:

• Go to SSMS > Choose the relevant DB name > right click > Reports > Standard Reports > Disk Usage by Top Tables

In order to compress the chosen tables please refer to How to Reduce Database Size? article. 

Delete unused MTS SKUs & history

In order to delete unused MTSSKUs and their history, follow the below instructions:

1. Go to Admin > Supply Chain > SKUs > SKUs General Parameters > make sure the 'Delete SKU Permanently' parameter is on Yes (in this case the history data will also be deleted). 
2. Load the interface file DELETE_MTSSKUS with all the relevant SKUs that should be permanently deleted.

For large-scale deletion (more than ~1M SKUs with 365 days of history), contact the Support team for an SQL script. 

Hardware

Release unused RAM

In order to release unused RAM restart the SQL server. 

Check Disks Space

Make sure there is a minimum of 20% free Space in the disks where Onebeat database is stored.

SQL Server

Server Memory

SQL Server is designed to grab memory (as much RAM as possible) and keep hold of it. The memory will not be released unless it is explicitly required by the operating system.

The best approach is to limit the amount of RAM that the server can use which will allow the OS to have a set amount of resources to use no-matter-what.

Use the two server memory options, min server memory, and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.

1. In Object Explorer, right-click a server and select Properties.
2. Click the Memory node.
3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

Recovery Model

In order to reduce the size of the database, in case a client performs a daily DB backup, we recommend using the Simple recovery model. 

In order to change the recovery model database property: 

1. In Object Explorer, right-click a database and select Properties.
2. Click the Options node.
3. Under Recovery model, choose Simple.

TempDB disk location

In order to improve the L&R performance, we recommend moving the system database tempDB to a separate SSD disk (separate then the OS disk and Onebeat database disks). 

In order to move the tempDB use the "moveTempDB.sql" sql script. 

Shrink TempDB

In case the tempDB size increase dramatically, and it is required to shrink it, restart the SQL server. 

Database Partitions (Multi-Thread version) 

Balance Partitions

In Multi-Threaded version, the Database is partitioned according to the SL's, meaning the amount of data in each partition derives from the SLs that are ranged to the partition. 

After a period of time, in case of creating more SL_SKUs, the last partition might be bigger than all the rest of the partitions, therefore a 'balance partitions' process is required. 

The balance partitions process contains the following actions: unite partitions and create partitions.