Analyze slow SQL queries in an ApsaraDB RDS for SQL Server instance - ApsaraDB RDS

Slow SQL queries can significantly impact database stability. When you encounter issues such as high database load or performance fluctuations, the first step for an administrator or developer is often to check for running slow SQL queries. Database Autonomy Service (DAS) provides a slow query analysis feature that collects statistics and analyzes SQL statements whose execution time exceeds a specified threshold. This helps you quickly identify and resolve database performance issues to improve system stability and reliability.

Prerequisites

The instance must use a cloud disk.
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).

Notes

The maximum length of a slow query log entry is 16 KB. Entries longer than this limit are truncated.
Slow query log integrity: In the following extreme scenarios, slow query log records may be incomplete or partially lost:
- The instance is under high load and is experiencing performance bottlenecks.
- A large number of SQL requests are generated on the instance within a short period.
- The service component that collects slow query logs is malfunctioning.

Background

When you troubleshoot performance issues in SQL Server, identifying slow SQL queries (SQL statements with high resource consumption) is a common and effective method. SQL statements with high CPU usage, long execution times, high I/O, or a large number of affected rows can all be potential slow SQL queries. The Autonomy Services feature for ApsaraDB RDS records and analyzes these resource-intensive SQL statements. It then displays aggregated results on the Slow Log Statistics tab and detailed logs on the Slow Log Details tab. This helps you quickly pinpoint SQL statements that affect system performance and simplify the tuning process.

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, choose Autonomy Services > Slow Query Logs.
Select a time range. You can then view the Slow Log Trend, Event Distribution, Slow Log Statistics, and Slow Log Details for that period. Although you can query slow query log data from the past month, the selected time range cannot exceed seven days.
- Select nodes to view (for Cluster Edition instances only): You can filter slow query logs for all nodes in a cluster instance, or for a specific primary or standby node. In the Node ID drop-down list, select All Node IDs or a specific node (primary or standby node) to filter the slow query log data.
- In the Slow Log Trend chart, you can select a specific point in time to view the corresponding Slow Log Statistics and Slow Log Details.
  
  Note
  If a slow SQL statement is too long to be fully displayed, hover the pointer over the statement to view the complete text in a pop-up window.
- In the Event Distribution section, you can find slow log events within the specified time range. Click an event to view its details.
- On the Slow Query Log Statistics and Slow Query Log Details tabs, click to save the slow query log information to a local file.
- Click to go to OpenAPI Explorer and debug the API. The currently selected and entered parameters are passed automatically.
- In the Slow Log Statistics section:
  - For a target SQL template, click Details in the Actions column to view the Slow Log Sample for that template.
  - Above the list, select filter criteria to refine the data.