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
A database is created on your RDS instance. For more information, see Create 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.
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 left-side navigation pane, click Databases.
Find the database that you want to manage and click View Details in the Actions column.
NoteFor 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 left-side navigation pane, 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 a large number of 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.
NoteBy 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 left-side navigation pane, click Databases.
Click More > Update Database Statistics in the Actions column.
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 level of the compatibility between the database and the database engine version. Valid values:
|
parameterization | Specifies how SQL Server handles parameters. Valid values:
|
read_committed_snapshot | Specifies whether to use the snapshot isolation feature during data reading. Valid values:
|
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 the automatic statistics update feature. Valid values:
|
auto_update_statistics_async | Specifies whether to asynchronously update database statistics. Valid values:
|
allow_snapshot_isolation | Specifies whether to enable the snapshot isolation feature. Valid values:
|
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 backup. Valid values:
|
Advanced information
Attribute | Description |
ansi_nulls | Specifies whether to enable ANSI NULLS. If you enable ANSI NULLS, a value that is NULL or a literal NULL is not compared with other values and is considered as an unknown value. Valid values:
|
recursive_triggers | Specifies whether to allow the trigger to execute recursively. Valid values:
|
delayed_durability | Specifies whether to enable the delayed durability feature. If you enable the feature, data is asynchronously written to the disk after transactions are committed. This helps improve the transaction processing performance. Valid values:
|
ansi_warnings | Specifies whether to enable the ANSI warning feature. After you enable this feature, if you perform an operation that results in a warning, a warning message is returned. Valid values:
|
ansi_null_default | Specifies whether to accept NULL values during data insertion. Valid values:
|
ansi_padding | Specifies whether to enable the ANSI padding feature. After you enable the feature, if the length does not meet the minimum required length, padding characters are used. Valid values:
|
concat_null_yields_null | Specifies whether to return NULL when you concatenate NULL values. If you enable the feature, NULL is returned when you concatenate NULL values. Valid values:
|