Recommended SQL settings for Active Roles performance (4383609)

This article outlines the recommended SQL settings for Active Roles for best performance.

Settings:

1. Disable Auto Shrink on the Active Roles databases
   •  Frequent shrink operations can result in performance and fragmentation issues. It is recommended to disable Auto Shrink. For more details, see:
     Considerations for the autogrow and autoshrink - SQL Server | Microsoft Learn 
2. Increasing the value of Cost Threshold of Parallelism
   • Too low of a value can cause serial operations to go parallel and result in poor performance. The default value is 5 and Microsoft recommends increasing this value. For Active Roles, a value of 50 is a recommended starting point. For more details, see this KB:
     Server configuration: cost threshold for parallelism - SQL Server | Microsoft Learn 
3. Determining the best value for Maximum Degree of Parallelism
   • This setting determines how many CPUs to use for parallel plan executions. For more details, see this KB:
     Server Configuration: max degree of parallelism - SQL Server | Microsoft Learn 
4. Setting memory limits for SQL service
   • By default, SQL uses all available system memory. Microsoft recommends setting SQL memory to 75%. For example, if the server has 32gb of memory, set SQL maximum to 24gb. For more details visit:
     Server Memory Configuration Options - SQL Server | Microsoft Learn 
5. Page file. Setting the page file size too low can cause performance issues. It is recommended to set it to either Automatically manage paging file size for all drives, or System Managed Size on a per-drive basis. For more details, visit:
   How to determine the appropriate page file size for 64-bit versions of Windows - Windows Client | Microsoft Learn 

In SQL Server Management Studio, do the following:

NOTES: 

• The only change that requires a service restart is Memory. All other changes are immediate and do not require any services to be restarted
• Consult with a DBA to ensure settings are acceptable for your environment
• When disabling Auto Shrink, ensure a SQL maintenance plan is in place

Auto Shrink

1. On each of the Active Roles databases (Configuration and Management History), right-click and select Properties
2. Click Options
3. Change Auto Shrink to False
4. Click Ok to save the changes

Additional reference:  When creating new databases using the Active Roles Configuration Center wizard, there is no option … 

Example:

Cost Threshold of Parallelism

1. On the SQL Server, right-click and select Properties
2. Navigate to Advanced
3. Change the value for Cost Threshold of Parallelism to 50
4. Click OK to save changes

Example:

Maximum Degree of Parallelism

This setting is a calculation that is dependent of various factors and a simple way to calculate these settings is to run the attached SQL Query to determine the results. For additional information or other methods on determining and setting the correct value, please see:

• Server Configuration: max degree of parallelism - SQL Server | Microsoft Learn 
• Configure the Max Degree of Parallelism (MAXDOP) - Azure SQL Database | Microsoft Learn

NOTE: This SQL query and recommendation is for the global server setting. There may be per-database settings for Max Degree of Parallelism that can override the server setting. Consult with a DBA to ensure this is applicable for your SQL Server and databases.

In the example below, the recommended MaxDOP (Maximum Degree of Parallelism) is 4. Once the script returns your specific recommended value, you may set the value as follows:

1. On the SQL Server, right-click and select Properties
2. Navigate to Advanced
3. Change the value for Maximum Degree of Parallelism to the value returned in the SQL Query
4. Click Ok to save changes

SQL Query Results Example:

Setting:

Memory

To limit the SQL Server memory:

1. Right-click properties of the SQL Server in SQL Management Studio
2. Click Memory
3. Set the limit to 75% of available system memory in MB. For example, if the system has 32gb, set the value to 24576
4. Click Ok to save changes

Example:

Page file

To check and set the correct page file size:

1. Open System Properties on the SQL Server
2. Click Advanced
3. Click Settings for Performance
4. Click Advanced tab
5. Click Change
6. Set the appropriate options - Either Automatically manage paging file size for all drives, or System Managed size per-drive
7. Restart Windows to apply the change

Example: