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

Use Database Autonomy Service (DAS) to optimize performance, diagnose failures, and reduce O&M costs for ApsaraDB RDS for SQL Server. DAS applies machine learning and expert experience to provide self-perception, self-healing, self-optimization, self-O&M, and self-protection for databases.

Feature overview

DAS provides the following features for RDS SQL Server:

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

Monitors storage from instance level down to individual tables, helping you identify storage issues:

Storage overview: Overall storage status with weekly changes, free space, used space, and estimated growth.
Storage data charts: Storage usage charts showing data-to-log ratio and top 5 databases by usage.
Storage trend: Storage change trend over time.
Top 10 databases: Details of the top 10 databases by storage usage.
Top 20 databases: Details of the top 20 databases by storage usage.

Performance optimization

Provides the following database performance tools:

Performance Insight: Evaluates database load, identifies performance bottlenecks, and improves stability.
Missing index: Shows missing indexes and provides the SQL statements to create them.
Index usage statistics: Shows index usage details and the SQL statements used to create the indexes.
Performance statistics: Shows performance statistics for the instance.
Top SQL: Ranks SQL statements across multiple dimensions. Query real-time and historical top SQL statements.
Top Objects: Retrieves real-time performance consumption at the object level (stored procedures, functions, triggers) to identify high-consumption objects and their SQL statements.

Locking optimization

Displays deadlock and lock blocking details caused by performance issues:

Deadlock: Shows detailed deadlock information.
Lock blocking: Shows detailed lock blocking information.
Slow SQL statements

Records and analyzes high-consumption SQL statements, showing both aggregated statistics and individual slow SQL details.
Monitoring dashboard

Lets you select instances and metrics to monitor, compare metrics across instances, and apply cross-metric filters.

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

To allow a RAM user to use DAS features, grant the required permissions.

Grant permissions to a RAM user using a system policy. DAS system policies:
- AliyunHDMFullAccess: Full DAS management permissions.
- AliyunHDMReadOnlyAccess: Read-only DAS permissions.
- AliyunHDMReadOnlyWithSQLLogArchiveAccess: Read-only DAS permissions plus the search (including export) feature of SQL Explorer and Audit.

Grant permissions to a RAM user using a custom policy. Example: grant read-only permissions:

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