Ensure proper Exact Online Replication Performance

Summary

The replication of Exact Online to SQL Server needs maintenance on SQL Server. This note gives some guidelines. Consult your local DBA for assistance.

Exact Online Replication to SQL Server

Many sites replicate the contents of Exact Online data using Invantive Data Replicator and the REST and XML APIs to a relational database on SQL Server or other platforms. This scales great up to thousands of Exact Online companies.

Replication Performance

However, we have had various reports on SQL Server performance going down over time. Often, replication runtime changes from minutes to hours or from hours to days. This especially occurs on small environments that have been live over months or years, and on large environments that have been live over months.

SQL Server is a great database platform, but still needs some maintenance. A typical error scenario is that the delete of obsolete facts such as General Ledger Transaction Lines takes over 30 minutes even with a batch size of 10.000 rows:

For instance, the dc_event_log_r view or the trace file present an error like:
Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

when executing a SQL Server statement like:
delete top(10000) [DB].dbo.dcd_gyo6s where ( tpn_id$ in (@i1tpn_id$) )

Resolution

The actual solution is to follow Microsoft’s recommendations for SQL Server maintenance. To avoid timeouts during Exact Online replication execute the following steps:

This solution is platform-specific. SQL Server has a specific implementation of index maintenance with advantages and disadvantages.

On Oracle, MySQL or PostgreSQL, other and or similar recommendations apply.

Please consult your local DBA for optimizing your SQL Server environment.

Defragmentation

The defragmentation compacts fragmented indexes, reducing runtime for delete statements as shown above. The following query present a list of possible issues:

select dbschemas.name schema_name
,      dbtables.name  table_name
,      dbindexes.name index_name
,      round(indexstats.avg_fragmentation_in_percent, 0) fragmentation_pct
,      indexstats.page_count
,      'ALTER INDEX ' + dbindexes.name + ' on ' + dbtables.name + ' REBUILD' stmt
from   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
join   sys.tables dbtables 
on     dbtables.object_id = indexstats.object_id
join   sys.schemas dbschemas 
on     dbtables.schema_id = dbschemas.schema_id
join   sys.indexes AS dbindexes 
on     dbindexes.object_id = indexstats.object_id
and    indexstats.index_id = dbindexes.index_id
where  indexstats.database_id = DB_ID()
and    indexstats.avg_fragmentation_in_percent > 25
order 
by     indexstats.avg_fragmentation_in_percent desc

Consult your local DBA for their opinion on the suggested optimizations. The last column ‘stmt’ contains suggested SQL statements to execute.

Depending on the SQL Server platform, the defragmentation can also be automated.