Use SQL statements to modify the parameters of an ApsaraDB RDS for SQL Server instance - ApsaraDB RDS

You can use SQL statements or the ApsaraDB RDS console to modify the parameters of an ApsaraDB RDS for SQL Server instance. This topic describes how to use SQL statements to modify parameters.

Note

The SQL statements provided in this topic are supported for RDS instances that run SQL Server 2012 or later.
You can also modify the parameters of an RDS instance in the ApsaraDB RDS console. For more information, see Reconfigure the parameters of an ApsaraDB RDS for SQL Server instance by using the ApsaraDB RDS console.

Supported parameters

fill factor (%)
max worker threads
cost threshold for parallelism
max degree of parallelism
min server memory (MB)
max server memory (MB)
blocked process threshold (s)

Configuration method

Use the sp_rds_configure stored procedure to specify the parameters that you want to modify. If the new settings take effect only after your RDS instance restarts, a message is displayed. This section provides an example on how to use Microsoft SQL Server Management Studio (SSMS) 19.0 and the following statements to modify parameters.

Note

Data Management (DMS) does not support data of the VARIANT type. If an error occurs when you execute specific statements in the DMS console, modify the statements based on the error description.

USE master
GO

-- Query the SQL Server version of the RDS instance.
SELECT SERVERPROPERTY('edition')
GO

-- Create a database named testdb.
CREATE DATABASE testdb
GO

-- Query the current value of the max degree of parallelism parameter.
SELECT * 
FROM sys.configurations
WHERE NAME = 'max degree of parallelism'

-- Set the max degree of parallelism parameter to 0.
EXEC sp_rds_configure 'max degree of parallelism',0
WAITFOR DELAY '00:00:10'

-- Query the new value of the max degree of parallelism parameter.
SELECT * 
FROM sys.configurations
WHERE NAME = 'max degree of parallelism'

References

You can call an operation to query the parameter settings of an instance. For more information, see DescribeParameters.
You can call an operation to modify the parameter settings of an instance. For more information, see ModifyParameter.