Microsoft SQL Server Settings - EcoSys - Help - Hexagon PPM

EcoSys Performance Tuning Requirements

PPMProduct
EcoSys
PPMCategory_custom
Help
Version_EcoSys
8.6
  • 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 to false.

    • Right click on ESFM database in SQL Server and select properties. Under options change Auto Update Statistics to false.

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

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

  • Update database statistics.

    • Execute the script \EcoSys\database\sqlserver\utils\fm_ss_job_update_stats.sql located on the installation media.

    • This script should be run as frequent as possible especially if a large amount of records are deleted/inserted.

  • Minimize index fragmentation by re-organizing indexes.

    • Execute the script \EcoSys\database\sqlserver\utils\fm_ss_reorg.sql located on the installation media.

    • This script should be run as frequent as possible especially if a large amount of records are deleted/inserted.