ApsaraDB RDS:Query top N SQL statements

Prerequisites

Your RDS instance does not run SQL Server 2008 R2 with cloud disks.

Procedure

1. 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.

2. In the left-side navigation pane, choose Autonomy Services > Performance Optimization.

3. Click the TOP SQL tab.

   • Query real-time top SQL statements.

     Specify the criteria based on which you want to sort SQL statements. Then, turn on Automatic Refresh.

     Note

     • The statistics of real-time top SQL statements for the RDS instance are based on the data in the cache after the RDS instance is started. When a new SQL statement or an existing SQL statement is executed, the data in the cache is simultaneously updated. You can check the last execution time of the SQL statement in the Last Executed At column of the table that is displayed in the Real-time Top SQL Statements - List section.

     • In the table that is displayed in the Real-time Top SQL Statements - List section, you can click an SQL statement in the Statement or SQL Block column. In the dialog box that appears, you can click Copy to copy the SQL statement.

     

   • Query historical top SQL statements.

     You can use the SQL Explorer and Audit feature to view the historical top SQL statements of an RDS instance.

Introduction to the Top SQL tab

• Top SQL Statement Overview: This section displays the most recent time at which the SQL statement statistics were updated and allows you to specify the SQL statement statistics that you want to view.

• Real-time Top SQL Statements - Average Overhead: This section displays the SQL statements that consume the most resources on average based on the following six metrics: Average CPU Overhead, Average Execution Duration, Average Returned Rows, Average Logical Reads, Average Physical Reads, and Average Logical Writes. The following table describes the parameters in this section.

  Table 1. Parameters in the Real-time Top SQL Statements - Average Overhead section

  Parameter	Description
  Average CPU Overhead	Sorts SQL statements based on the average CPU overhead per execution of each SQL statement. Unit: milliseconds.
  Average Execution Duration	Sorts SQL statements based on the average running time per execution of each SQL statement. Unit: milliseconds.
  Average Returned Rows	Sorts SQL statements based on the average number of rows that were returned per execution of each SQL statement.
  Average Logical Reads	Sorts SQL statements based on the average number of logical read operations that were performed per execution of each SQL statement.
  Average Physical Reads	Sorts SQL statements based on the average number of physical read operations that were performed per execution of each SQL statement.
  Average Logical Writes	Sorts SQL statements based on the average number of logical write operations that were performed per execution of each SQL statement.

• Real-time Top SQL Statements - Total Cost: This section displays the SQL statements that consume the most resources in total based on the following six metrics: Total CPU Overhead, Execution Duration, Total Returned Rows, Total Logical Reads, Total Physical Reads, and Total Executions. The following table describes the parameters in this section.

  Table 2. Parameters in the Real-time Top SQL Statements - Total Cost section

  Parameter	Description
  Total CPU Overhead	Sorts SQL statements based on the total CPU overhead of each SQL statement. Unit: milliseconds.
  Total Execution Duration	Sorts SQL statements based on the total running time of each SQL statement. Unit: milliseconds.
  Total Returned Rows	Sorts SQL statements based on the total number of rows that were returned for each SQL statement.
  Total Logical Reads	Sorts SQL statements based on the total number of logical read operations that were performed for each SQL statement.
  Total Physical Reads	Sorts SQL statements based on the total number of physical read operations that were performed for each SQL statement.
  Total Executions	Sorts SQL statements based on the total number of times that each SQL statement was executed.

• Real-time Top SQL Statements - List: This section displays the real-time SQL statements that consume the most resources based on different metrics. The following table describes the parameters in this section.

  Table 3. Parameters in the Real-time Top SQL Statements - List section

  Parameter	Description
  Database	The name of the database on which the SQL statement was executed.
  Statement	The SQL statement that was executed. You can click the SQL statement to view the details about the SQL statement.
  SQL Block	The text content of the SQL statement. You can click the SQL statement to view the text content of the SQL statement.
  Executions	The total number of times that the SQL statement was executed.
  Total CPU Time	The total CPU overhead of the SQL statement.
  Average CPU Time	The average CPU overhead per execution of the SQL statement.
  Execution Duration	The total running time of the SQL statement.
  Average Execution Duration	The average running time per execution of the SQL statement.
  Total Returned Rows	The total number of rows that were returned for the SQL statement.
  Average Returned Rows	The average number of rows that were returned per execution of the SQL statement.
  Total Logical Reads	The total number of logical read operations that were performed for the SQL statement.
  Average Logical Reads	The average number of logical read operations that were performed per execution of the SQL statement.
  Total Physical Reads	The total number of physical read operations that were performed for the SQL statement.
  Average Physical Reads	The average number of physical read operations that were performed per execution of the SQL statement.
  Total Logical Writes	The total number of logical write operations that were performed for the SQL statement.
  Average Logical Writes	The average number of logical write operations that were performed per execution of the SQL statement.
  Last Executed At	The most recent time at which the SQL statement was executed.