View or modify SQL Server database attributes - ApsaraDB RDS

You can view or modify various attributes for an ApsaraDB RDS for SQL Server instance in the ApsaraDB RDS console. You can also manually shrink database transaction logs and update database statistics to optimize database performance and improve query efficiency.

Prerequisites

You have created a database.

View or modify attributes

Usage notes

If you modify specific attributes of a database, the database enters the exclusive mode for a short period of time. In this case, the system closes all connections to the database and rolls back existing transactions for the modifications to take effect. If the load on the database is heavy, the modifications may fail. To prevent business interruptions, we recommend that you perform the operation during off-peak hours.

Note

If a database enters the exclusive mode, only one user or process can access and manage the database. Other users or processes can access or manage the database only after all operations on the database are completed.

Procedure

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 navigation pane on the left, click Databases.
Find the database that you want to manage and click View Details in the Actions column.
Note
For more information about the database attributes, see Appendix: Database attributes or Microsoft documentation.
In the Allowed Values column, modify the attribute values and click Submit.

Shrink database transaction logs

Scenarios

If the transaction log files of a database occupies a small amount of the total storage that is allocated to store the transaction log files of the database, and the log_reuse_wait_desc attribute of the database is set to Nothing. In this case, you can shrink database transaction logs to reduce the disk space usage of the database. The storage used by the transaction log files is specified by the LogUsedSizeInMB parameter, and the total storage allocated to store the transaction log files is specified by the TotalLogSizeInMB parameter.

Procedure

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 navigation pane on the left, click Databases.
Choose More > Shrink Database Transaction Logs in the Actions column.
In the message that appears, click OK.

Update database statistics

The amount and distribution of data in a database change over a specific period of time. To ensure the accuracy and performance of the query optimizer, you must update the database statistics on a regular basis.

Scenarios

If you upgrade the major engine version of your RDS instance, new data types, storage engines, or query optimizers are used by the new engine version. The original database statistics may become inaccurate or unavailable. In this case, you must update database statistics to adjust data distribution for the new database engine.
After you migrate a self-managed database to the cloud, the environment changes and database performance needs to be optimized. In this case, you can update database statistics to improve query performance and optimize query plans.
If your business data is unevenly distributed, you perform many DELETE or UPDATE operations but the amount of data that you update does not exceed 20%, or the automatic statistics update feature of SQL Server is no longer applicable, you can manually update the database statistics to improve query performance.
Note
By default, SQL Server enables the automatic statistics update. In some cases, the automatic update frequency may be lower than the data change frequency. This results in poor query performance. For more information about the automatic statistics update feature in SQL Server, see Microsoft documentation.

Usage notes

Updating database statistics may cause heavy I/O loads. To prevent service interruptions, we recommend that you perform the operation during off-peak hours.

Procedure

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 navigation pane on the left, click Databases.
In the Actions column, click More > Update Database Statistics.
In the message that appears, click OK.

Appendix: Database attributes

Basic information

Attribute	Description
page_verify	The level of the page-level database verification. The default value is CHECKSUM, which uses the checksum of the data page for verification.
target_recovery_time_in_seconds	The amount of time that is required to recover a database. Unit: seconds. Default value: 60.
compatibility_level	The compatibility level of the database with a specific version. Valid values: 100: SQL Server 2008 or later 110: SQL Server 2012 or later 120: SQL Server 2014 or later 130: SQL Server 2016 or later 140: SQL Server 2017 or later 150: SQL Server 2019 or later 160: SQL Server 2022 or later
parameterization	How SQL Server handles parameters. Valid values: SIMPLE (default): parameterizes only constant parameters. FORCED: forcefully parameterizes all parameters.
read_committed_snapshot	Specifies whether to use snapshot isolation when reading data. Valid values: OFF (default): disables READ COMMITTED SNAPSHOT ISOLATION and uses read locks. ON: enables READ COMMITTED SNAPSHOT ISOLATION. Concurrent read operations are allowed and not blocked by write operations.
collation_name	The character set collation and character encoding of the database. For more information, see Change the character set collation and time zone of system databases.
auto_close	Specifies whether to automatically close database connections. Default value: OFF, which indicates that the system does not automatically close database connections.
recovery_model_desc	The recovery model of the database. Default value: FULL, which indicates the full recovery model.
auto_update_statistics	Specifies whether to enable automatic statistics updates. Valid values: ON (default) Disabled
auto_update_statistics_async	Specifies whether to asynchronously update statistics. Valid values: OFF (default): synchronously updates database statistics. ON: asynchronously updates database statistics.
allow_snapshot_isolation	Specifies whether to enable the snapshot isolation level. Valid values: OFF (default) ON
state_desc	The status of the database. The value ONLINE indicates that the database is online.
create_date	The time when the database was created.
log_reuse_wait_desc	The reason why the current log file cannot be reused. The value NOTHING indicates that no limits are imposed.

Runtime information

Attribute	Description
TotalDataSizeInMB	The total space that is allocated to store the data files of the database. Unit: MB.
DataUsedSizeInMB	The space that is used by data files of the database. Unit: MB.
TotalLogSizeInMB	The total space that is allocated to store the log files of the database. Unit: MB.
LogUsedSizeInMB	The space that is used by log files of the database. Unit: MB.
VLFCount	The number of virtual log files (VLFs) for the database.
LastestBackupTime	The time when the most recent backup was performed on the database.
LastestBackupType	The type of the most recent database backup. Valid values: D: full backup I: differential backup L: log backup

Advanced information

Attribute	Description
accelerated_database_recovery	Specifies whether to enable Accelerated Database Recovery (ADR). ADR achieves database recovery in seconds through versioned storage and logical rollback. This significantly reduces downtime caused by restarts or failures. ADR is suitable for online transactional processing (OLTP) systems that frequently handle long-running transactions and require high availability. Valid values: OFF (Default) ON Note This feature is supported only on Standard Edition and Enterprise Edition instances that run SQL Server 2019 or later. Enabling this feature can significantly reduce recovery time in scenarios with long-running transactions and improve system availability. However, it increases storage consumption and introduces minor performance overhead.
ansi_nulls	Specifies whether to enable ANSI NULLS. If enabled, a value that is equal to NULL is not compared with other values and is treated as an unknown value. Valid values: OFF (default) ON
recursive_triggers	Specifies whether to allow triggers to execute recursively. Valid values: OFF (default) ON
delayed_durability	Specifies whether to enable delayed durability. Delayed durability allows transactions to write data to disk asynchronously after they are committed, which improves transaction processing performance. Valid values: DISABLED (default): disables the feature. After transactions are committed, data is synchronously written to the disk at the earliest opportunity. ALLOWED: enables the feature. However, the feature is not forcefully used for all transactions. FORCED: forcefully enables the feature. The feature is forcefully used for all transactions.
ansi_warnings	Specifies whether to enable ANSI warnings. If enabled, a warning message is returned if an operation causes a warning. Valid values: OFF (default) ON
ansi_null_default	Specifies whether the column accepts NULL values when a NULL value is specified during data insertion. Valid values: OFF (default) ON
ansi_padding	Specifies whether to enable ANSI padding. If enabled, padding characters are used if the inserted data is shorter than the required length. Valid values: OFF (default) ON
db_owner	Specifies the true owner of the database. The specified owner has the highest permissions for the database, including but not limited to deleting the database, modifying all objects, and managing user permissions. The default value is typically the creator of the database. The owner must be a valid user account in the current instance. Note Modifying the `db_owner` attribute of a database directly changes the ownership identity of the database, affecting the principal to which the database belongs. In contrast, setting the permission of an account to "Owner" for a database on the Account Management page assigns the `db_owner` role to the destination account. This is essentially an allocation of permissions and does not change the ownership principal of the database.
concat_null_yields_null	Specifies whether to return NULL when concatenating NULL values. If enabled, NULL is returned when concatenating NULL values. Valid values: OFF (default) ON

Related operation

You can modify the attributes of an RDS SQL Server database by calling the ModifyDatabaseConfig API.