Slow SQL queries degrade the stability of your ApsaraDB RDS for SQL Server instance and are often the root cause of high load and performance jitter. Database Autonomy Service (DAS) provides slow query analysis to help you:
-
Identify which SQL statements are degrading performance
-
Get optimization suggestions with expected performance benefits
-
Export slow query logs for offline analysis
Prerequisites
Before you begin, make sure your RDS instance meets the following requirements:
-
Uses cloud disks
-
Does not run SQL Server 2008 R2 with cloud disks
Background information
You can analyze slow SQL statements to troubleshoot the performance issues of an RDS instance. SQL statements that consume a large number of CPU resources or I/O resources, require a long period of time to execute, or affect a large number of rows may be considered slow SQL statements. The autonomy service records and analyzes these SQL statements, and displays statistics and details on the Slow Query Logs page to help identify performance bottlenecks and simplify optimization.
View slow query logs
-
Go to the Instances page. In the top navigation bar, select the region where your RDS instance resides. Find the instance and click its ID.
-
In the left-side navigation pane, choose Autonomy Services > Slow Query Logs.
-
On the Slow Query Logs page, select a time range. The page displays the Slow Log Trend, Slow Log Statistics, and Slow Log Details for that period.
The end time must be later than the start time, and the interval cannot exceed 24 hours. You can query slow query logs within the previous month.
-
(Optional) Click a point on the trend chart to view the statistics and details of slow queries at that specific time.
If an SQL statement is too long to display in full, hover over it to see the complete text.
-
(Optional) Click Export Slow Log to download the slow query logs to your computer.
Diagnose and optimize slow queries
On the Slow Log Statistics tab, you can run SQL diagnostics on any SQL template.
-
Find the SQL template you want to optimize and click Sample in the Actions column to review the slow query log details.
-
Click Optimize in the Actions column. The SQL Diagnostic Optimization dialog appears with diagnostic results.
DAS analyzes SQL statements based on their complexity, table data volume, and database load. Diagnostic results may take more than 20 seconds to return. Results include optimization suggestions and expected performance gains.
-
Review the suggestions:
-
To apply the suggestions, click Copy in the upper-right corner and paste the optimized SQL into your database client or Data Management (DMS) for execution.
-
To discard the suggestions, click Cancel.
-
You can also diagnose SQL templates from the Slow Log Details tab using the same Optimize action.
You can also click Database Expert Service to purchase the expert service. Database Expert Service provides value-added professional database services, such as emergency solutions, health diagnostics, performance optimization, security assurance, and data migration.