Microsoft SQL Server Settings - EcoSys - Help - Hexagon

EcoSys Performance Tuning Requirements
Language
English
Product
EcoSys
Search by Category
Help
EcoSys Version
9.3

  • EcoSys production database is recommended to run on dedicated hardware with no other databases sharing the same hardware.

  • Hard set the amount of memory for the SQL Server instance leaving at least 4GB allocated to the OS and the remaining memory for the SQL Server instance.

    • This can be done in the instance properties under memory section.

  • Data files, logs and tempdb database should be located on dedicated disk with a maximum latency of 10ms.

    • Disk drives with 10k IOPS are recommended.

  • Identify the number of physical processors available on the SQL Server instance.

    • Set MAXDOP equal to the number of cores with a maximum value of 8.

  • Create tempdb files equal to the number of processors.

    • The maximum tempdb files is 8.

  • Set tempdb to grow at a fixed size.

    • The recommended size is 2,000 MB.

  • Set ESFM data files should grow at a fixed.

    • The recommended value of 2,000 MB.

  • The ESFM log files should grow at a fixed size.

    • The recommended value of 500MB.

  • Set Auto Create Statistics to true.

    • Right-click on ESFM database in SQL Server and select properties. Under Options, change Auto Create Statistics to true.

  • Set Auto Update Statistics from false to true.

    • Right-click on ESFM database in SQL Server and select properties. Under Options, change Auto Update Statistics from false to true.

    • For SQL Server 2014, configure the database instance to have TraceFlag 2371 set.

  • Set Cost Threshold for Parallelism to 50 at the instance level.

  • Set Optimize for Adhoc Workloads to True at the instance level.

  • To execute stats, execute one of the two sections from the script.

    \Ecosys\database\sqlserver\utils\stats\fm_ss_job_update_stats_options.sql, updating the @Databases parameter to match your database name.

    • It is recommended to run the 'Light' database statistics update process nightly. This method does not require EcoSys downtime.

    • It is recommended to run the 'Heavy' database statistics update process weekly. This method requires EcoSys to be offline during its execution.

  • In the ESFM database, enable Query Store under Properties and set the following values:

    • Operation Mode = Read write

    • Data Flush = 15 minutes

    • Statistics Collection = 15 minutes

    • Max Plans = 200

    • Max Size = 1024 MB

    • Query Store = Auto

    • Size Based = Auto

    • Stale Query = 367

    • Wait Statistics = On