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