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
- 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 .
Click the TOP Objects tab.
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.