ApsaraDB RDS for SQL Server performance optimization and diagnosis-ApsaraDB RDS(RDS)-阿里云帮助中心

For daily operations and maintenance (O&M) of ApsaraDB RDS for SQL Server, you can use Database Autonomy Service (DAS) to handle database failures, optimize performance, improve efficiency, and reduce O&M costs. DAS is an Alibaba Cloud service that uses machine learning and expert experience to provide self-perception, self-healing, self-optimization, self-O&M, and self-protection for databases. DAS simplifies database O&M tasks and ensures the stability, security, and efficiency of your database service.

Feature overview

The autonomy service for ApsaraDB RDS for SQL Server includes the following features:

SQL Explorer and Audit (Recommended)

Enable SQL Insight and Audit to automatically record SQL changes in the database kernel, including the executing account, IP address, and execution details. This feature has negligible impact on instance performance and supports security and compliance audits, performance analysis, and troubleshooting.

Storage Management

This feature provides hierarchical monitoring and analysis that lets you drill down from the instance to the database, and from the database to the table. This helps you identify and locate database storage issues. It consists of the following parts:

Storage overview: Displays the overall storage status, including weekly changes, free space, used space, and estimated growth.
Storage data charts: Displays the storage usage of the instance in charts, including storage usage, the data-to-log ratio, and the top 5 databases by storage usage.
Storage trend: Displays the storage change trend of the instance in a chart.
Top 10 databases: Displays the details of the top 10 databases by storage usage in a table.
Top 20 databases: Displays the details of the top 20 databases by storage usage in a table.

Performance optimization

This feature displays key information about the database and consists of the following parts:

Performance Insight: Allows you to quickly evaluate the database payload, find the source of performance issues, and improve database stability.
Missing index: Displays information about missing indexes for the instance in a chart and provides the SQL statements required to create them.
Index usage statistics: Displays details about index usage for the instance in a chart and provides the SQL statements that were used to create the indexes.
Performance statistics: Displays the details of performance statistics for the instance in a chart.
Top SQL: Displays SQL statements sorted by multiple dimensions in charts. You can query real-time and historical top SQL statements.
Top Objects: Retrieves the real-time performance consumption at the object level, such as for stored procedures, functions, and triggers, in your instance. This helps you find the objects with the highest performance consumption and the SQL statements within those objects.

Locking optimization

This feature displays details about deadlocks and lock blocking in the database that are caused by performance issues. It consists of the following parts:

Deadlock: Displays detailed information about deadlocks in a chart.
Lock blocking: Displays detailed information about lock blocking in a chart.
Slow SQL statements
Records and analyzes high-consumption SQL statements. This feature displays aggregation results (slow SQL statistics) and slow SQL details.
Monitoring dashboard
Allows you to select the database instances and metrics to monitor. You can compare metrics across different database instances and use filter interaction for multiple metrics. This helps you monitor the running status of your database instances.

Limits

This feature is not available for ApsaraDB RDS for SQL Server 2008 R2 instances that use cloud disks.
This feature is available only for ApsaraDB RDS for SQL Server instances in the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Chengdu), China (Hong Kong), Singapore, or UAE (Dubai).

Appendix: Authorization

If a Resource Access Management (RAM) user needs to use DAS features, you must grant the required permissions to the RAM user.

Grant permissions to a RAM user using a system policy. The system policies for DAS are as follows:
- AliyunHDMFullAccess: Grants the RAM user full management permissions for DAS.
- AliyunHDMReadOnlyAccess: Grants the RAM user read-only permissions for DAS.
- AliyunHDMReadOnlyWithSQLLogArchiveAccess: Grants the RAM user read-only permissions for DAS and the permissions to use the search (including export) feature of SQL Explorer and Audit.

Grant permissions to a RAM user using a custom policy. For example, to grant read-only permissions to a RAM user:

{
           
            "Action": [
                "hdm:Get*",
                
                "hdm:Describe*",
                
                "hdm:Query*",
                
                "hdm:Support*"
            
            ],
            
                "Effect": "Allow",
            
                "Resource": "acs:rds:*:*:*"
        
}

上一篇: Performance optimization and diagnosis 下一篇: High CPU usage in RDS SQL Server