To-Dos in Maintaining the eG Database

In addition to the above, the following list includes a set of activities that must be performed to keep the eG database functioning optimally.

1. Avoid disk contention

   Ensure that there is no contention amongst the log disk and the data store disk of the database. Make sure that the log files and database data files are stored in separate drives with their own channels to avoid high contention for write cycles.

2. Avoid fragmented files/file systems

   Ensure that the disks are periodically de-fragmented by using the OS tools/recommended tools. For example, in the Windows 2012 Server, use the Defragment Now button in the Defragmentation section that appears in the Properties window that appears when you navigate through the Administrative Tools ->Computer Management > Disk Management menu sequence. Note that this may not be needed on most Unix platforms.

3. Ensure adequate memory/buffers

   Ensure that the database buffers are sized adequately for optimal performance. Too low buffers would mean inadequate work memory and too many buffers without enough primary memory would result in thrashing and both would impact performance, hence caution has to be exercised when setting this.

4. Setting the Memory Management Preferences:

   If the backend is MSSQL and the database is hosted on a dedicated system, then it is appropriate to allow the database server to take up as much memory as possible. To do this, login to the enterprise manager, select the appropriate database server and right click on it and select SQL Server Properties ->Memory->Choose “Dynamically configure SQL Server Memory” option.

5. Ensure that logs do not clog

   Ensure that the redo log files/transaction logs/trace log files do not grow to un-manageable sizes. Make sure that these logs roll over after a specific size, or truncate these logs periodically.

6. Avoid setting resource limits for database queries

   Specific jobs or activities in the eG system may have long running queries. If resource limits are set in the database for queries, this could result in some of the eG activities being terminated abruptly, and this could in turn have a disruptive impact on the performance of the eG Enterprise system.

7. Ensure that day-end database backups do not overlap with eG’s day end activities

   Often database backups are scheduled during mid-night which is also around the time at which the day-end jobs of the eG Enterprise system start. This results in heavy load on the database server, which in turn causes rapid performance degradation. To avoid this, either schedule the backup jobs at a different time or schedule the eG day-end jobs to run at a different time via the SCHEDULED CLEANUP TIME AT option in the DATA MANAGEMENT page of the eG administrative interface.

8. Set the DELAYED_DURABILITY property of the eG database on a Microsoft SQL server to 'forced', if you see any latency in transaction log writes and/or a high wait time for locks

   Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system because:

   • The transaction commit processing does not wait for log IO to finish and return control to the client.

   • Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput

   Some of the cases in which you could benefit from using delayed transaction durability are:

   • You can tolerate some data loss: If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. If you cannot tolerate any data loss, do not use delayed transaction durability.
   • You are experiencing a bottleneck on transaction log writes: If your performance issues are due to latency in transaction log writes, your application will likely benefit from using delayed transaction durability.

   • Your workloads have a high contention rate: If your system has workloads with a high contention level much time is lost waiting for locks to be released. Delayed transaction durability reduces commit time and thus releases locks faster which results in higher throughput.

   To configure delayed transaction durability at the eG database-level, issue the following statement at the SQL prompt:

   ALTER DATABASE ... SET DELAYED_DURABILITY = { FORCED }