All Products
Search
Document Center

ApsaraDB RDS:Modify parameters by using SQL statements

Last Updated:Sep 11, 2023

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.

Note

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:

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, 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'