You can modify the parameters of an ApsaraDB RDS for SQL Server instance by using SQL statements or the ApsaraDB RDS console. This topic describes how to modify the parameters by using SQL statements.
The SQL statements described in this topic are supported only for RDS instances that run SQL Server 2012 or later. For information about how to modify the parameters of an RDS instance that runs SQL Server 2008 R2, 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)
Modification 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, the system displays a message. This section provides an example on how to use Microsoft SQL Server Management Studio (SSMS) 19.0
to modify parameters by using the following statements:
Data Management (DMS) does not support data of the VARIANT type. If an error occurs when you execute specific statements in the DMS console, adjust the statements based on the error.
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'