Query top N objects - ApsaraDB RDS - Alibaba Cloud Documentation Center

TOP Objects identifies the stored procedures, functions, and triggers consuming the most resources in your ApsaraDB RDS for SQL Server instance, so you know exactly where to focus your optimization efforts. With TOP Objects, you can:

Rank database objects by CPU overhead, execution duration, logical reads, physical reads, and more
Compare average-cost and total-cost views to distinguish occasional outliers from sustained high consumers
Drill into any object to view SQL statement-level metrics, including minimum, maximum, and last-execution values

Prerequisites

Before you begin, make sure that:

Your RDS instance does not run SQL Server 2008 R2 with cloud disks.
Your RDS instance resides in one of 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).

View top objects

Log on to the ApsaraDB RDS console. In the top navigation bar, select the region where your instance resides, then click the instance ID.
In the left navigation pane, choose Autonomy Services > Performance Optimization.
Click the TOP Objects tab.
Configure the display options on the right side of the page: select the database, set the refresh interval, choose a sort metric, set the number of entries to display, and turn on Automatic Refresh.

Page overview

TOP Object Overview

This section controls the data displayed on the entire page and shows when the object statistics were last updated.

Parameter	Description
DB Filter	The database whose objects to display. Select multiple databases to view them simultaneously.
Auto-Refresh Interval	The page refresh interval when automatic refresh is on. Options: 5, 10, 30, or 60 seconds.
Top User Objects By Performance Consumption	The metric used to rank objects in real time. See Choosing a sort metric for guidance on which metric fits your diagnostic goal.
TOP XX Entries	The number of top objects to display. Options: 5, 10, or 15.
Automatic Refresh	Enables real-time automatic refresh of the TOP Objects page.
Data Updated At	The time when the current statistics were generated for the instance.
Save As PDF File	Saves the current page to your local computer as a PDF file.

Choosing a sort metric

Use the following table to match your diagnostic goal to the most relevant sort metric.

Diagnostic goal	Recommended metric
Find objects that spike CPU during individual executions	Average CPU Overhead
Find objects that drive the highest cumulative CPU load on the instance	Total CPU Overhead
Find objects with consistently slow individual executions	Average Execution Duration
Find objects that read the most data overall	Total Logical Reads or Total Physical Reads

Real-time Top Objects - Average cost

This section ranks objects by per-execution averages. Use it to identify objects that are individually expensive regardless of how often they run—these are the best candidates for query or index optimization.

Metric	Description
Average CPU Overhead	Average CPU overhead per statement execution for each object. Unit: milliseconds.
Average Execution Duration	Average running time per statement execution for each object. Unit: milliseconds.
Average Logical Reads	Average number of logical reads per statement execution for each object.
Average Returned Rows	Average number of rows returned per statement execution for each object.

Real-time Top Objects - Total cost

This section ranks objects by their share of the instance's total resource consumption. Use it to identify objects that drive the most overall load—these are the best candidates when you need to reduce instance-wide pressure.

Metric	Description
Total CPU Overhead Percentage	The object's share of total CPU overhead across the instance.
Total Execution Duration Percentage	The object's share of total running time across the instance.
Total Logical I/O Percentage	The object's share of total logical I/O across the instance.
Total Returned Rows Percentage	The object's share of total rows returned across the instance.

Real-time Top Objects - List

This section lists the top N objects with cumulative statistics.

All cumulative counts (Total Executions, Total CPU Overhead, and so on) reset when the instance restarts or the plan cache is cleared. These metrics reflect activity since the last reset, not historical totals.

Parameter	Description
Object Name	The fully qualified name of the object: database name, schema name, and object name.
Object Type	The type of object: stored procedure, function, trigger, and so on.
Total Executions	Total number of executions since the last instance restart or cache clear.
Total CPU Overhead	Total CPU overhead for all executions since the last instance restart or cache clear. Unit: milliseconds.
Average CPU Overhead	Average CPU overhead per execution. Unit: milliseconds.
Total Execution Duration	Total running time for all executions since the last instance restart or cache clear. Unit: milliseconds.
Average Execution Duration	Average running time per execution. Unit: milliseconds.
Total Returned Rows	Total rows returned for all executions since the last instance restart or cache clear.
Average Returned Rows	Average rows returned per execution.
Total Logical Reads	Total logical reads since the last instance restart or cache clear.
Average Logical Reads	Average logical reads per execution.
Total Physical Reads	Total physical reads since the last instance restart or cache clear.
Average Physical Reads	Average physical reads per execution.
Total Logical Writes	Total logical writes since the last instance restart or cache clear.
Average Logical Writes	Average logical writes per execution.
Total Logical I/O	Total logical I/O since the last instance restart or cache clear.
Average Logical I/O	Average logical I/O per execution.

View SQL statement-level metrics

To drill into an object, click the plus sign (+) to the left of the object row. The expanded view shows the following parameters for each SQL statement within the object.

Parameter	Description
Object Name	The parent object that contains this SQL statement.
Statement (View Details)	The SQL statement. Click to view the full statement text.
Executions	Total number of times the statement has been executed.
Obtain Query Plan	Number of times the system retrieved the execution plan for the statement.
Total CPU Overhead	Total CPU overhead across all executions. Unit: milliseconds.
Average CPU Overhead	Average CPU overhead per execution. Unit: milliseconds.
Minimum CPU Overhead	Lowest CPU overhead observed across all executions. Unit: milliseconds.
Maximum CPU Overhead	Highest CPU overhead observed across all executions. Unit: milliseconds.
Last CPU Overhead	CPU overhead of the most recent execution. Unit: milliseconds.
Total Execution Duration	Total running time across all executions. Unit: milliseconds.
Average Execution Duration	Average running time per execution. Unit: milliseconds.
Minimum Execution Duration	Shortest running time observed across all executions. Unit: milliseconds.
Maximum Execution Duration	Longest running time observed across all executions. Unit: milliseconds.
Last Execution Duration	Running time of the most recent execution. Unit: milliseconds.
Total Returned Rows	Total rows returned across all executions.
Average Returned Rows	Average rows returned per execution.
Minimum Returned Rows	Fewest rows returned across all executions.
Maximum Returned Rows	Most rows returned across all executions.
Last Returned Rows	Rows returned by the most recent execution.
Total Logical Reads	Total logical reads across all executions.
Average Logical Reads	Average logical reads per execution.
Minimum Logical Reads	Fewest logical reads across all executions.
Maximum Logical Reads	Most logical reads across all executions.
Last Logical Reads	Logical reads from the most recent execution.
Total Physical Reads	Total physical reads across all executions.
Average Physical Reads	Average physical reads per execution.
Minimum Physical Reads	Fewest physical reads across all executions.
Maximum Physical Reads	Most physical reads across all executions.
Last Physical Read	Physical reads from the most recent execution.
Total Logical Writes	Total logical writes across all executions.
Average Logical Writes	Average logical writes per execution.
Minimum Logical Writes	Fewest logical writes across all executions.
Maximum Logical Writes	Most logical writes across all executions.
Last Logical Writes	Logical writes from the most recent execution.
Total Logical I/O	Total logical I/O across all executions.
Average Logical I/O	Average logical I/O per execution.
Minimum Logical I/O	Lowest logical I/O across all executions.
Maximum Logical I/O	Highest logical I/O across all executions.
Last Logical I/O	Logical I/O from the most recent execution.

What's next

After identifying high-consumption objects, use the following tools to investigate further:

Check execution plans: View how SQL Server processes each statement to find missing indexes or inefficient join strategies. Use the Performance Optimization page to access execution plan analysis.
Explore additional diagnostics: Return to Autonomy Services > Performance Optimization to access other diagnostic features, including slow query analysis and instance-level performance trends.