Use the sp_rds_configure stored procedure to set configuration options on an ApsaraDB RDS for SQL Server instance directly from a SQL client, without going through the ApsaraDB RDS console.
SQL statements in this topic require SQL Server 2012 or later. To set parameters in the console instead, see Reconfigure the parameters of an ApsaraDB RDS for SQL Server instance using the ApsaraDB RDS console.
Prerequisites
Before you begin, ensure that you have:
-
An ApsaraDB RDS for SQL Server instance running SQL Server 2012 or later
-
A SQL client connected to the instance (the examples in this topic use Microsoft SQL Server Management Studio (SSMS) 19.0)
Data Management (DMS) does not support data of the VARIANT type. If an error occurs when you run specific statements in DMS, adjust the statements based on the error description.
Supported parameters
The following parameters can be set using sp_rds_configure.
| Parameter | Description | Notes |
|---|---|---|
fill factor (%) |
Percentage of space on each index leaf-level page to fill during index creation or rebuild | — |
max worker threads |
Maximum number of worker threads available to SQL Server processes | — |
cost threshold for parallelism |
Cost threshold above which SQL Server creates and runs parallel plans for queries | — |
max degree of parallelism |
Maximum number of processors to use in parallel plan execution | — |
min server memory (MB) |
Minimum amount of memory allocated to the SQL Server memory pool | — |
max server memory (MB) |
Maximum amount of memory allocated to the SQL Server memory pool | Cannot be set on shared instances |
blocked process threshold (s) |
Threshold in seconds at which the server generates blocked process reports | — |
Set a parameter
The sp_rds_configure stored procedure accepts a parameter name and a new value. If the change requires a restart to take effect, a message is displayed after you run the command.
The following example queries the current value of max degree of parallelism, sets it to 0, and then confirms the new value.
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'What's next
Use the ApsaraDB RDS API to manage parameters programmatically:
-
DescribeParameters — query the current parameter settings of an instance
-
ModifyParameter — modify the parameter settings of an instance