View or modify the parameters of an ApsaraDB RDS for SQL Server instance in the ApsaraDB RDS console - ApsaraDB RDS

This topic describes how to modify the parameters of an ApsaraDB RDS for SQL Server instance in the ApsaraDB RDS console or by calling an API operation to meet your business requirements. This topic also describes how to view the parameter modification history.

Prerequisites

The RDS instance meets the following requirements:

The RDS instance uses a general-purpose or dedicated instance type. Shared instance types are not supported. For more information, see Instance families.
The RDS instance uses the subscription or pay-as-you-go billing method. Serverless RDS instances are not supported. For more information, see Serverless ApsaraDB RDS for SQL Server instances.

Note

If the parameters of the RDS instance cannot be modified in the ApsaraDB RDS console or by calling an API operation, you can execute SQL statements to configure the parameters. For more information, see Use SQL statements to modify instance parameters.

Usage notes

To ensure the stability of your RDS instance, you can modify only the parameters that are displayed in the ApsaraDB RDS console.
Parameter modification takes effect within 10 seconds. After you modify the parameters that are displayed in the ApsaraDB RDS console, you do not need to restart your RDS instance.
If your RDS instance runs RDS Cluster Edition, read-only RDS instances are attached to the primary RDS instance, and you modify the parameters of the primary RDS instance, the modification is not synchronized to the read-only RDS instances. You must separately configure the parameters for the read-only RDS instances.
If you modify the trace flags of your RDS instance and then manually restart the RDS instance, the system automatically applies the new values to the RDS instance. However, the new values take effect within approximately 10 minutes.

View parameter values

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane of the page that appears, click Parameters to view the value of each parameter.

Modify parameters

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane of the page that appears, click Parameters.
On the Modifiable Parameters tab, find the parameter that you want to modify and click the icon in the Running Value column.
Enter a new value and click OK.
Click Apply Changes. In the message that appears, click OK.
Important
You must click Apply Changes for the new value to take effect.

View the parameter modification history

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane of the page that appears, click Parameters.
Click the Edit History tab, specify a time range, and then click OK to view the modification history.

Appendix: Parameters that can be modified

Note

For more information about the parameters, see Microsoft documentation.

Server parameters

Parameter	Description	Default value	Value range
`Ad Hoc Distributed Queries`	Specifies whether to enable ad hoc distributed queries.	0	[0-1]
`Database Mail XPs`	Specifies whether to enable Database Mail.	0	[0-1]
`blocked process threshold (s)`	The threshold. If the threshold is exceeded, blocked process reports are generated. Unit: seconds.	0	[0-86400]
`clr enabled`	Specifies whether SQL Server can run user assemblies.	0	[0-1]
`clr strict security`	Controls the interpretation of the `SAFE`, `EXTERNAL_ACCESS`, or `UNSAFE` permission in SQL Server.	0	[0-1]
`cost threshold for parallelism`	The threshold at which SQL Server creates and runs parallel plans for queries.	5	[0-32767]
`default full-text language`	The default language value for full-text indexes.	1033	[0-6000]
`default language`	The default language for all new usernames used for logons.	0	[0-33]
`filestream access level`	The FILESTREAM access level of the RDS instance.	0	[0-2]
`max degree of parallelism`	The maximum degree of parallelism.	2	[0-64]
`remote query timeout (s)`	The timeout period of a remote operation in SQL Server. Unit: seconds.	600	[0-2147183647]
`remote login timeout (s)`	The timeout period for logon from a remote server. Unit: seconds.	10	[0-2147183647]
`query wait (s)`	The amount of time that a query waits for resources before it times out. Unit: seconds.	30	[-1-38400]
`optimize for ad hoc workloads`	Optimizes ad hoc workloads.	0	[0-1]
`nested triggers`	The nested trigger.	1	[0-1]
`max worker threads`	The maximum number of worker threads.	0	[128-65535]
`max text repl size (B)`	The maximum size of text replication.	65536	[0-2147483647]
`remote proc trans`	The Microsoft Distributed Transaction Coordinator (MS DTC) transaction that protects the actions of a server-to-server procedure.	0	[0-1]
`query governor cost limit`	The upper limit on the estimated cost that is allowed for a query to run.	0	[0-2147483647]
`recovery interval (min)`	The upper limit on the amount of time that is required to recover a database. Unit: minutes.	0	[0-30]
`min memory per query (KB)`	The minimum amount of memory that can be allocated for the execution of a query. Unit: KB.	1024	[512-2147483647]
`in-doubt xact resolution`	Controls the default output of transactions that MS DTC is unable to resolve.	0	[0-2]

Trace flags

Parameter	Description	Default value	Value range
1204	Returns the resources and types of locks that participate in a deadlock and the current command that is affected.	0	[0-1]
1211	Disables lock escalation based on the memory capacity or the number of locks. The Database Engine component of SQL Server does not escalate row or page locks to table locks.	0	[0-1]
1222	Returns the resources and types of locks that participate in a deadlock and the current command that is affected in an XML format that does not comply with any XSD schema.	0	[0-1]
1224	Disables lock escalation based on the number of locks. If you enable the trace flag, SQL Server disables lock escalation based on the number of locks. This prevents a large number of blocking issues in high-concurrency scenarios. Trace flag 1224 is more flexible than Trace flag 1211. Trace flag 1224 disables lock escalation only if the system detects that blocking issues may occur. However, Trace flag 1211 disables lock escalation in every case. In most cases, Trace flag 1224 is used in high-concurrency scenarios to prevent performance issues that are caused by table locks.	0	[0-1]
2528	Disables parallel checking of objects by using `DBCC CHECKDB`, `DBCC CHECKFILEGROUP`, and `DBCC CHECKTABLE`. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2014.	0	[0-1]
3205	Disables hardware compression for tape drivers.	0	[0-1]
3226	Cancels the backup of log entries.	0	[0-1]
4199	Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2016.	0	[0-1]
4616	Makes server-level metadata visible to application roles.	0	[0-1]
6527	Disables the generation of a memory dump on the first occurrence of an out-of-memory exception in common language runtime (CLR) integration.	0	[0-1]
692	Disables fast inserts if a large amount of data is loaded into the heap or clustered index. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2016.	0	[0-1]
1117	If a file in a filegroup reaches the autogrow threshold, all files in the filegroup grow. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2016.	0	[0-1]
1118	Forces page allocations on uniform extents instead of mixed extents to reduce contention on the Shared Global Allocation Map (SGAM) page. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2016.	0	[0-1]
1262	Optimizes the parallel processing of partitioned tables, especially in high-throughput scenarios. When you process a large number of partitioned tables, Trace flag 1262 helps alleviate performance bottlenecks caused by imbalanced load distribution or partition-level parallel processing. The trace flag is suitable for queries that require parallel access to a large amount of data.	0	[0-1]
2335	The memory that is configured for SQL Server is still used by data cache, query execution, and other consumers.	0	[0-1]
2371	Changes the fixed update statistics threshold to a linear update statistics threshold. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2016.	0	[0-1]
2430	Enables alternate lock class cleanup.	0	[0-1]
3604	Sends the diagnostic information to the client. In most cases, the trace flag is enabled when you use DBCC commands, such as DBCC PAGE. This way, you can view information such as the page structure and metadata to troubleshoot in-depth performance issues, such as lock contention. The trace flag is suitable for database performance diagnostics or data structure analysis.	0	[0-1]
6498	Keeps memory usage for the compilation of incoming queries under control to prevent compilation waits for concurrent large queries. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2014.	0	[0-1]
8048	Converts non-uniform memory access (NUMA) partitioned memory objects into CPU partitioned memory objects. Note You can modify the trace flag for RDS instances that run major engine versions earlier than SQL Server 2014.	0	[0-1]

FAQ

After I modify the parameters of my RDS instance, does the modification immediately take effect? Do I need to restart my RDS instance?

In most cases, the modification takes effect within 10 seconds. After you modify the parameters that are displayed in the ApsaraDB RDS console, you do not need to restart your RDS instance.

Why are the parameter settings not updated after I modify the parameters of my RDS instance?

After you modify the parameters, Make sure that you click Apply Changes.

References

You can query the parameters of your RDS instance by calling an API operation. For more information, see DescribeParameters.
You can modify the parameters of your RDS instance by calling an API operation. For more information, see ModifyParameter.
You can configure parameters for your RDS instance by executing SQL statements. For more information, see Use SQL statements to modify instance parameters.