Regarding the settings to be changed on the SQL Server, these changes can be made using Transact SQL, or from within SQL Management Studio or Configuration Manager. Some notable examples and how to change them follow.
Please note that these are advanced options and should be changed only by an experienced database administrator or certified SQL Server professional.Parameterization ForcedForced parameterization is when the database engine parameterizes any literal value that appears in a SELECT, UPDATE, INSERT, or DELETE statement submitted in any form. There is more information available here:
Specify Query Parameterization Behavior by Using Plan Guides.
To determine the current setting of parameterization:
SELECT name, is_parameterization_forced FROM sys.databases
To enable Forced Parameterization (value of 1) use the following ALTER DATABASE statement, e.g.:
CPU and RAM
The System Information report and recommendations for Maximum Degree of Parallelism, Minimum Size of Server Memory, and Maximum Size of Server Memory refer to the running values for the number of CPU cores SQL Server will utilize for parallel processing and the minimum or maximum memory it will reserve or utilize.
- The default value of zero for parallelism allows all processor cores to be utilized. This may lead to issues with the DBQueue processing. A value of two (2) is recommended.
- The default value of zero for minimum memory for SQL server results in 16 MB being allocated. It is recommended that most system memory be reserved for SQL services supporting Identity Manager. A value of physical/virtual system memory allocated minus three gigabytes should be utilized (e.g. for a Dev system with the minimum 16 GB Windows Host running SQL Server Standard this value should be 13GB; for a Prod system with 512 GB of memory for the Windows Host running SQL Server Enterprise the value should be 509 GB).
- The default maximum memory value is All. This should be limited to the same value as the minimum memory value so as to ensure that some resources are available to the host system.
Server CollationThe server collation is specified during SQL Server installation. Default server-level collation is SQL_Latin1_General_CP1_CI_AS.
Please use caution as changing the default collation for an instance of SQL Server can be a complex operation.Set or Change the Server Collationmaximum degree of parallelism