All Products
Search
Document Center

ApsaraDB RDS:View the SQL statement statistics of an ApsaraDB RDS for SQL Server instance

Last Updated:Oct 08, 2023

The autonomy services feature allows you to query real-time top SQL statements and historical top SQL statements. This way, you can view the performance overheads at the statement level.

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 Execution Time 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 Cost: 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 the Real-time Top SQL Statements - Average Cost section.

    Table 1. Parameters in the Real-time Top SQL Statements - Average Cost 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 the Real-time Top SQL Statements - Total Cost 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.

    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 the Real-time Top SQL Statements - List section.

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

    Parameter

    Description

    Databases

    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 Execution Time

    The most recent time at which the SQL statement was executed.