IBM Sterling B2B Integrator MS SQL Server DB best practices

Database Isolation level:

This is a very important feature that helps reducing excessive locking and deadlocks.

With the application down, run the following T-SQL statements to enable the READ_COMMITTED_SNAPSHOT for a database:

begin 

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; 
  
ALTER DATABASE  SET READ_COMMITTED_SNAPSHOT ON; 

ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON  

ALTER DATABASE  SET MULTI_USER; 

end 

GO 

 

This snapshot option increases the number of I/Os as well as the size of tempdb. It is important to have tempdb on fast disks as well as to have it sized according to your workload.

You can query sys.databases to check the isolation level status. 

SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases 

Database Collation:

• The default collation of Microsoft SQL Server Database used by Sterling B2B Integrator should be set to SQL_Latin1_General_CP850_Bin to prevent character data conversions before the data is stored in the database tables. 

• Also, the tempdb database that is used by Microsoft SQL Server must be created with the same collation as the default collation of Microsoft SQL Server.

Microsoft SQL Server settings:

• Allow Microsoft SQL Server to manage memory dynamically (default).
• IsAutoCreateStatistics = true
• IsAutoUpdateStatistics =true
• Alternatively, Use a maintenance plan if the previous two settings are set to false.

Reduce lock escalations:

Lock Escalation is the process of converting many fine-grained locks such as row/page locks into table locks. Microsoft SQL Server usually escalates the locks when an SQL statement acquires at least 5,000 locks on a single table. Lock escalation also happen when the memory allocated to SQL Server is limited.

In Sterling B2B integrator, lock escalations can occur during important housekeeping operations such as Purge, Index, AssociateBPToDocs, Archive, Linkage Purge, Message Purge. So it is important to reduce the size of transactions and use small batches.

Tips to improve the Database performance and reduce lock escalations:

• Reduce the number of steps of your Business Processes.

• Use External Purge.

• Use smaller batch size in the Purge process (Chunks in normal Purge or batch size in External Purge).

• Disable lock escalation for important transactional B2B tables like: WORKFLOW_CONTEXT, DOCUMENT, CORRELATION_SET and TRANS_DATA.

You can check the lock escalation mode on the tables in your database:

SELECT name, lock_escalation_desc from sys.tables

To change the lock escalation for a table (not always the best option):

ALTER TABLE tablename SET (LOCK_ESCALATION = DISABLE)

Other best practices:

• Disable any antivirus software that is running on the Microsoft SQL Server data, transaction log, and binary files directory.

Check MS SQL Server T-SQL helper procedures for Sterling B2B Integrator DB.