Recommendations for MS SQL

As a database administrator you want your database to achieve top performance. This topic provides basic maintenance recommendations for installations with MS SQL databases.

  • Reorganize the Automation Engine database (including all Clients) on a regular basis.

  • Do not limit resource consumption. Aborting transactions due to limitations specified in the database hamper processing in the AE system. Additionally, inconsistent database contents can occur which cause subsequent errors or endless loops.

  • Make sure that your server and client has TCP/IP connection enabled (MS SQL Configuration Manager).

  • UTF8 and MS SQL Server/MS SQL Server on Linux: Make sure that the new Trust Server Certificate option is activated in the Microsoft Server DNS Configuration window and that the Perform translation of character data option is not activated in the Connection Encryption. For details, see Setting Up the Data Source for ODBC Access

  • The transaction log, the TEMPDB of the database and the page file should not be filed in a RAID 5 file system. Use RAID 1 or 0 instead.

  • Do not, by any means, activate the option autoshrink in the database. This might occasionally cause a standstill of the Automation Engine.

  • Activate Versioning in order to reduce the occurrence of deadlocks. Doing so has the effect that the file tempdb increases. Ensure that it is stored on a fast device:

    alter database database name set READ_COMMITTED_SNAPSHOT ON

  • Add the Mars_Connection=Yes option to the database connection parameters in the INI files of the Automation Engine and the utilities. This setting ensures optimal database access using the performance options of MS SQL Server.

    Example

    SQLDRIVERCONNECT=ODBCVAR=NNNNNNRN,DSN=UC4;UID=uc4;PWD=­­--1037B2E22BF022EBE2;Mars_Connection=Yes

  • Split the database to as many files as there are CPUs.

  • MS SQL escalates from row to table locking if more than 5000 locks are allocated, which could lead to a down time in the system. To avoid it, the LOCK_ESCALATION is set to "DISABLED" for all tables.

    Example

    ALTER TABLE ECV SET(LOCK_ESCALATION=DISABLE)

    ALTER TABLE EEC SET(LOCK_ESCALATION=DISABLE)

    ALTER TABLE EEDB SET(LOCK_ESCALATION=DISABLE)

See also: