Return
-
Title
How to configure settings as per the System Information Overview -
Description
System information (available from the Help menu in front end tools: Help | Info... ) reports settings that should be meeting a recommended value, e.g.:
maximum degree of parallelism
How and where are these settings changed? -
Cause
Recommended values are those required for optimal performance. Highlighted rows are those that should be adjusted to optimize performance. -
Resolution
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 Forced
Forced 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.:
ALTER DATABASE AdventureWorks2012 SET PARAMETERIZATION FORCED
See also: SQL Server Simple and Forced Parameterization
AVG latency read/write
It is important to monitor Read/Write latency, and there are various tools available for this, however this is beyond the scope of this article.
Some relevant and useful articles:
Monitoring Read/Write Latency
How to examine IO subsystem latencies from within SQL Server
CPU and RAMThe 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 Collation
The 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 Collation
maximum degree of parallelism
Configure the max degree of parallelism Server Configuration Option
optimize for ad hoc workload
optimize for ad hoc workloads Server Configuration Option
Using SQL Server Management Studio
To configure the max degree of parallelism option:
1. In Object Explorer, right-click a server and select Properties.
2. Click the Advanced node.
3. In the Optimize for Ad hoc Workloads box, select True from the drop-down and click OK.
Cost Threshold for Parallelism
Configure the cost threshold for parallelism Server Configuration Option -
Additional Information
System Information overview is also available by executing the following query: select * from qbmvsystemoverview