All Products
Search
Document Center

ApsaraDB RDS:Query top N objects

Last Updated:Mar 20, 2024

This topic describes how to use the autonomy service to query the top N objects of an ApsaraDB RDS for SQL Server instance. These objects include stored procedures, functions, and triggers. You can identify and troubleshoot performance issues based on the object information.

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 Objects tab.

  4. In the upper-right corner of the tab, configure the Database, Every XX Seconds, Obtain, and TOP XX Entries parameters. Then, turn on Automatic Refresh.

Introduction to the TOP Objects tab

  • Top Object Overview: This section displays the last update time of the object information and allows you to specify the object information that you want to view. The following table describes the parameters in this section.

    Table 1. Parameters in the TOP Object Overview section

    Parameter

    Description

    Database

    The database whose objects you want to view. You can select more than one database.

    Every XX Seconds

    The interval at which the system updates the object information. This parameter takes effect only when you turn on Automatic Refresh. Valid values: 5, 10, 30, and 60. Unit: seconds.

    Obtain

    The metric based on which the system sorts the objects of the selected database in real time. Valid values include Average CPU Overhead, Average Execution Duration, Total CPU Overhead, Total Logical Reads, and Total Physical Reads.

    TOP XX Entries

    The number of objects that you want to view. Valid values: 5, 10, and 15.

    Automatic Refresh

    The switch that is used to manage the automatic refresh feature.

    Data Updated At

    The time when the object information of your RDS instance was generated.

    Save as PDF File

    If you want to save the object information to your computer as a file, click Save as PDF File.

  • Real-time Top Objects - Average Cost: This section displays the top N objects of your RDS instance based on the following metrics: Average CPU Overhead, Average Execution Duration, Average Logical Reads, and Average Returned Rows. The following table describes the parameters in this section.

    Table 2. Parameters in the Real-time Top Objects - Average Cost section

    Parameter

    Description

    Average CPU Overhead

    The average CPU overhead per statement execution for each object. Unit: milliseconds.

    Average Execution Duration

    The average running time per statement execution for each object. Unit: milliseconds.

    Average Logical Reads

    The average number of logical reads per statement execution for each object.

    Average Returned Rows

    The average number of rows that are returned per statement execution for each object.

  • Real-time Top Objects - Total Cost: This section displays the top N objects of your RDS instance based on the following metrics: Total CPU Overhead Percentage, Total Execution Duration Percentage, Total Logical I/O Percentage, and Total Returned Rows Percentage. The following table describes the parameters in this section.

    Table 3. Parameters in the Real-time Top Objects - Total Cost section

    Parameter

    Description

    Total CPU Overhead Percentage

    The percentage of the total CPU overhead that is produced by statement executions on each object.

    Total Execution Duration Percentage

    The percentage of the total running time that is required by statement executions on each object.

    Total Logical I/O Percentage

    The percentage of the total logical I/O that is required by statement executions on each object.

    Total Returned Rows Percentage

    The percentage of the total number of rows that are returned by statement executions on each object.

  • Real-time Top Objects - List

    • This section provides an overview of the real-time performance drains for objects in the RDS instance. The following table describes the parameters in this section.

      Table 4. Overview of parameters in the Real-time Top Objects - List section

      Parameter

      Description

      Object Name

      The name of the object. The value of this parameter consists of three parts: database name, schema name, and object name.

      Object Type

      The type of the object. Valid values: Stored Procedure, Function, and Trigger.

      Total Executions

      The total number of statement executions on the object after you restart your RDS instance or clear the cache.

      Total CPU Overhead

      The total CPU overhead for all statement executions on the object after you restart your RDS instance or clear the cache. Unit: milliseconds.

      Average CPU Overhead

      The average CPU overhead per statement execution on the object. Unit: milliseconds.

      Total Execution Duration

      The total running time of all statement executions on the object after you restart your RDS instance or clear the cache. Unit: milliseconds.

      Average Execution Duration

      The average running time per statement execution on the object. Unit: milliseconds.

      Total Returned Rows

      The total number of rows that are returned for all statement executions on the object after you restart your RDS instance or clear the cache.

      Average Returned Rows

      The average number of rows that are returned per statement execution on the object.

      Total Logical Reads

      The total number of logical reads on the object after you restart your RDS instance or clear the cache.

      Average Logical Reads

      The average number of logical reads per statement execution on the object.

      Total Physical Reads

      The total number of physical reads on the object after you restart your RDS instance or clear the cache.

      Average Physical Reads

      The average number of physical reads per statement execution on the object.

      Total Logical Writes

      The total number of logical writes on the object after you restart your RDS instance or clear the cache.

      Average Logical Writes

      The average number of logical writes per statement execution on the object.

      Total Logical I/O

      The total logical I/O for the object after you restart your RDS instance or clear the cache.

      Average Logical I/O

      The average logical I/O per statement execution on the object.

    • This section also provides the details about the performance drain for each SQL statement on an object. To view the details, you need to find the object on which the SQL statement is executed. Then, you need to click the plus sign (+) on the left. The following table describes the parameters in this section.

      Table 5. Detailed parameters in the Real-time Top Objects - List section

      Parameter

      Description

      Object Name

      The name of the object on which the SQL statement is executed. The value of this parameter consists of three parts: database name, schema name, and object name.

      Statement (View Details)

      The details about the SQL statement. You can click this button to view the complete SQL statement.

      Executions

      The total number of times that the SQL statement is executed.

      Obtain Query Plan

      The number of times that the system obtains the execution plan of the SQL statement.

      Total CPU Overhead

      The total CPU overhead of the SQL statement. Unit: milliseconds.

      Average CPU Overhead

      The average CPU overhead per execution of the SQL statement. Unit: milliseconds.

      Minimum CPU Overhead

      The minimum CPU overhead among all executions of the SQL statement. Unit: milliseconds.

      Maximum CPU Overhead

      The maximum CPU overhead among all executions of the SQL statement. Unit: milliseconds.

      Last CPU Overhead

      The CPU overhead for the last execution of the SQL statement. Unit: milliseconds.

      Total Execution Duration

      The total running time of the SQL statement. Unit: milliseconds.

      Average Execution Duration

      The average running time per execution of the SQL statement. Unit: milliseconds.

      Minimum Execution Duration

      The minimum running time among all executions of the SQL statement. Unit: milliseconds.

      Maximum Execution Duration

      The maximum running time among all executions of the SQL statement. Unit: milliseconds.

      Last Execution Duration

      The running time for the last execution of the SQL statement. Unit: milliseconds.

      Total Returned Rows

      The total number of rows that are returned for the SQL statement.

      Average Returned Rows

      The average number of rows that are returned per execution of the SQL statement.

      Minimum Returned Rows

      The minimum number of rows that are returned among all executions of the SQL statement.

      Maximum Returned Rows

      The maximum number of rows that are returned among all executions of the SQL statement.

      Last Returned Rows

      The number of rows that are returned for the last execution of the SQL statement.

      Total Logical Reads

      The total number of logical reads that are run for the SQL statement.

      Average Logical Reads

      The average number of logical reads that are run per execution of the SQL statement.

      Minimum Logical Reads

      The minimum number of logical reads that are run among all executions of the SQL statement.

      Maximum Logical Reads

      The maximum number of logical reads that are run among all executions of the SQL statement.

      Last Logical Reads

      The number of logical reads that are run for the last execution of the SQL statement.

      Total Physical Reads

      The total number of physical reads that are run for the SQL statement.

      Average Physical Reads

      The average number of physical reads that are run per execution of the SQL statement.

      Minimum Physical Reads

      The minimum number of physical reads that are run among all executions of the SQL statement.

      Maximum Physical Reads

      The maximum number of physical reads that are run among all executions of the SQL statement.

      Last Physical Read

      The number of physical reads that are run for the last execution of the SQL statement.

      Total Logical Writes

      The total number of logical writes that are run for the SQL statement.

      Average Logical Writes

      The average number of logical writes that are run per execution of the SQL statement.

      Minimum Logical Writes

      The minimum number of logical writes that are run among all executions of the SQL statement.

      Maximum Logical Writes

      The maximum number of logical writes that are run among all executions of the SQL statement.

      Last Logical Writes

      The total number of logical writes that are run for the SQL statement.

      Total Logical I/O

      The total logical I/O for the SQL statement.

      Average Logical I/O

      The average logical I/O per execution for the SQL statement.

      Minimum Logical I/O

      The minimum logical I/O among all executions of the SQL statement.

      Maximum Logical I/O

      The maximum logical I/O among all executions of the SQL statement.

      Last Logical I/O

      The logical I/O for the last execution of the SQL statement.

      Last Execution Duration

      The running time for the last execution of the SQL statement.