The TOP SQL tab in the autonomy service identifies which SQL statements consume the most database resources. Use it to pinpoint high-cost queries and prioritize optimization work—without waiting for a full workload analysis.
With TOP SQL, you can:
Rank SQL statements by CPU overhead, execution duration, logical reads, physical reads, logical writes, or returned rows
Switch between per-execution averages (to find inefficient queries) and cumulative totals (to find high-frequency queries)
Copy any SQL statement directly from the console for immediate inspection
Access historical query data through SQL Explorer and Audit
Prerequisites
Before you begin, ensure that:
Your RDS instance does not run SQL Server 2008 R2 with cloud disks
Query real-time top SQL statements
Go to the Instances page. In the top navigation bar, select the region where the instance resides. Find the instance and click its ID.
In the left-side navigation pane, choose Autonomy Services > Performance Optimization.
Click the TOP SQL tab.
Select the metric to rank SQL statements by, then turn on Automatic Refresh.
Real-time statistics come from an in-memory cache that resets each time the instance starts. The cache updates instantly as SQL statements execute. Check the Last Executed At column to confirm when a statement last ran.
To copy a SQL statement, click it in the Statement or SQL Block column. In the dialog box that appears, click Copy.
Query historical top SQL statements
Use SQL Explorer and Audit to view historical top SQL statements for the instance.
TOP SQL tab reference
The TOP SQL tab has four sections.
Top SQL statement overview
Displays the most recent time the SQL statement statistics were updated and lets you choose which statistics to view.
Real-time top SQL statements - Average overhead
Ranks SQL statements by resource consumption per execution. Use this section to identify inefficient queries—those that consume disproportionate resources each time they run, regardless of how often they run.
| Sort metric | Description |
|---|---|
| Average CPU overhead | Average CPU overhead per execution. Unit: milliseconds. |
| Average execution duration | Average running time per execution. Unit: milliseconds. |
| Average returned rows | Average number of rows returned per execution. |
| Average logical reads | Average number of logical read operations per execution. |
| Average physical reads | Average number of physical read operations per execution. |
| Average logical writes | Average number of logical write operations per execution. |
Real-time top SQL statements - Total cost
Ranks SQL statements by cumulative resource consumption since the instance started. Use this section to identify high-frequency or long-running queries that generate the largest overall load—the best candidates for index optimization or query rewriting.
| Sort metric | Description |
|---|---|
| Total CPU overhead | Total CPU overhead across all executions. Unit: milliseconds. |
| Execution duration | Total running time across all executions. Unit: milliseconds. |
| Total returned rows | Total number of rows returned across all executions. |
| Total logical reads | Total number of logical read operations across all executions. |
| Total physical reads | Total number of physical read operations across all executions. |
| Total executions | Total number of times the SQL statement was executed. |
Real-time top SQL statements - List
Displays all tracked SQL statements with per-statement metrics. Click a statement in the Statement or SQL Block column to view its full text and copy it.
| Parameter | Description |
|---|---|
| Database | The database on which the SQL statement ran. |
| Statement | The SQL statement that was executed. Click to view details and copy. |
| SQL Block | The full text of the SQL statement. Click to view and copy. |
| Executions | Total number of executions. |
| Total CPU time | Total CPU overhead across all executions. |
| Average CPU time | Average CPU overhead per execution. |
| Execution duration | Total running time across all executions. |
| Average execution duration | Average running time per execution. |
| Total returned rows | Total number of rows returned across all executions. |
| Average returned rows | Average number of rows returned per execution. |
| Total logical reads | Total number of logical read operations across all executions. |
| Average logical reads | Average number of logical read operations per execution. |
| Total physical reads | Total number of physical read operations across all executions. |
| Average physical reads | Average number of physical read operations per execution. |
| Total logical writes | Total number of logical write operations across all executions. |
| Average logical writes | Average number of logical write operations per execution. |
| Last executed at | The most recent time the SQL statement ran. |