Microsoft SQL Server Settings - EcoSys - Help - Hexagon PPM

EcoSys Performance Tuning Requirements

PPMProduct
EcoSys
PPMCategory_custom
Help
Version_EcoSys
8.7
  • 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.

  • Execute the script \Ecosys\database\sqlserver\utils\stats\PrerequisiteRunOnce.sql

    • This script needs to be executed only once to configure the required database tables/procedures needed for the stats collection process

    • 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.