Querying Top SQL Statements (RDS for SQL Server)

Function

This API is used to query top SQL statements of a DB instance.

Before calling this API, learn how to authenticate it.
Before calling this API, learn about request header parameters.

Authorization Information

Each account has all the permissions required to call all APIs, but IAM users must be assigned the required permissions.

If you are using role/policy-based authorization, see Permissions and Supported Actions for details on the required permissions.
If you are using identity policy-based authorization, the following identity policy-based permissions are required.

Action	Access Level	Resource Type (*: required)	Condition Key	Alias	Dependencies
rds:instance:listAll	read	-	-	rds:instance:list	-

URI

URI format
GET /v3/{project_id}/instances/{instance_id}/top-sqls

Parameter description

**Table 1** Parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Definition Project ID of a tenant in a region. To obtain the value, see Obtaining a Project ID. Constraints N/A Range N/A Default Value N/A
instance_id	Yes	String	Definition Instance ID. Constraints N/A Range N/A Default Value N/A

**Table 2** Request parameters
Parameter	Mandatory	Type	Description
sort_key	No	String	Definition Sorting field. Constraints N/A Range avg_cpu_time: average CPU time total_cpu_time: total CPU time total_duration_time: total execution duration avg_duration_time: average execution duration total_rows: total number of rows avg_rows: average number of rows total_logical_reads: total logical reads avg_logical_reads: average logical reads Default Value N/A
limit	No	Integer	Definition Limit of top statements. Constraints N/A Range A maximum of 15 statements are supported. Default Value N/A
statement	No	String	Definition Search text content. Constraints N/A Range N/A Default Value N/A
sort_dir	No	String	Definition Sorting rule. Constraints N/A Range desc asc Default Value desc

Request

None

Example Request

Query top SQL statements of an RDS for SQL Server instance.

GET https://{Endpoint}/v3/1783d762a2c7416c817810224d01ca22/instances/8862f0a6de6c4b4eaa35abe435f4edccin04/top-sqls?limit=1

Response

Normal response

**Table 3** Parameters
Parameter	Type	Description
total_count	Integer	Definition Total number. Range N/A
list	Array of objects	Definition List of top SQL statements. For details, see Table 4.
avg_cpu_time_top_values	Array of objects	Definition Top SQL statements by average CPU overhead. For details, see Table 5.
avg_duration_time_top_values	Array of objects	Definition Top SQL statements by average execution duration. For details, see Table 5.
avg_rows_top_values	Array of objects	Definition Top SQL statements by average returned rows. For details, see Table 6.
avg_logical_top_values	Array of objects	Definition Top SQL statements by average logical reads. For details, see Table 7.
total_cpu_time_top_values	Array of objects	Definition Top SQL statements by total CPU overhead. For details, see Table 5.
total_duration_time_top_values	Array of objects	Definition Top SQL statements by total execution duration. For details, see Table 5.
total_rows_top_values	Array of objects	Definition Top SQL statements by total returned rows. For details, see Table 6.
total_logical_reads_top_values	Array of objects	Definition Top SQL statements by total logical reads. For details, see Table 7.

**Table 4** list field description
Parameter	Type	Description
id	String	Definition A binary hash value calculated for the query. IDs are used to identify queries with similar logic. Range N/A
statement	String	Definition Ongoing SQL statement. Range N/A
query	String	Definition Full SQL text. Range N/A
db_name	String	Definition Database name. Range N/A
execution_count	String	Definition Total number of executions. Range N/A
execution_count_percent	String	Definition Total number of executions, in percentage. Range 0.0000 to 1.0000
total_cpu_time	String	Definition Total CPU time, in ms. Range N/A
total_cpu_time_percent	String	Definition Total CPU time, in percentage. Range 0.0000 to 1.0000
avg_cpu_time	String	Definition Average CPU time, in ms. Range N/A
avg_cpu_time_percent	String	Definition Average CPU time, in percentage. Range 0.0000 to 1.0000
total_duration_time	String	Definition Total execution duration. Range N/A
total_duration_time_percent	String	Definition Total execution duration, in percentage. Range 0.0000 to 1.0000
avg_duration_time	String	Definition Average execution duration. Range N/A
avg_duration_time_percent	String	Definition Average execution duration, in percentage. Range 0.0000 to 1.0000
total_rows	String	Definition Total number of returned rows. Range N/A
total_rows_percent	String	Definition Total number of returned rows, in percentage. Range 0.0000 to 1.0000
avg_rows	String	Definition Average returned rows. Range N/A
avg_rows_percent	String	Definition Average returned rows, in percentage. Range 0.0000 to 1.0000
total_logical_reads	String	Definition Total logical reads. Range N/A
total_logical_reads_percent	String	Definition Total logical reads, in percentage. Range 0.0000 to 1.0000
avg_logical_reads	String	Definition Average logical reads. Range N/A
avg_logical_reads_percent	String	Definition Average logical reads, in percentage. Range 0.0000 to 1.0000
avg_logical_write	String	Definition Average logical writes. Range N/A
avg_logical_write_percent	String	Definition Average logical writes, in percentage. Range 0.0000 to 1.0000
total_logical_write	String	Definition Total logical writes. Range N/A
total_logical_write_percent	String	Definition Total logical writes, in percentage. Range 0.0000 to 1.0000
avg_physical_reads	String	Definition Average physical reads. Range N/A
avg_physical_reads_percent	String	Definition Average physical reads, in percentage. Range 0.0000 to 1.0000
total_physical_reads	String	Definition Total physical reads. Range N/A
total_physical_reads_percent	String	Definition Total physical reads, in percentage. Range 0.0000 to 1.0000
last_execution_time	String	Definition Last execution time. Range N/A

**Table 5** TopSqlsTimeResult
Parameter	Type	Description
id	String	Definition A binary hash value calculated for the query. IDs are used to identify queries with similar logic. Range N/A
data_type	String	Definition Data type. Range AvgWorkerTime: average CPU overhead AvgDuration: average execution duration TotalWorkerTime: total CPU overhead TotalDuration: total execution duration
value	String	Definition Duration, in ms.

**Table 6** TopSqlsRowResult
Parameter	Type	Description
id	String	Definition A binary hash value calculated for the query. IDs are used to identify queries with similar logic. Range N/A
data_type	String	Definition Data type. Range AvgReturnRows: average returned rows TotalReturnRows: total returned rows
value	String	Definition Number of rows. Range N/A

**Table 7** TopSqlsLogicalReadResult
Parameter	Type	Description
id	String	Definition A binary hash value calculated for the query. IDs are used to identify queries with similar logic. Range N/A
data_type	String	Definition Data type. Range AvgLogicalReads: average logical reads TotalLogicalReads: total logical reads
value	String	Definition Logical reads. Range N/A

Example normal response

{ 
   "total_count" : 1, 
   "list" : [ { 
     "id" : "0xb1a8d62fcf665755", 
     "statement" : "select * from ::fn_trace_getinfo(default)", 
     "query" : "select * from ::fn_trace_getinfo(default)", 
     "db_name" : "test", 
     "execution_count" : "1", 
     "execution_count_percent" : "1.0000", 
     "total_cpu_time" : "294", 
     "total_cpu_time_percent" : "1.0000", 
     "avg_cpu_time" : "294.98", 
     "avg_cpu_time_percent" : "0.9999", 
     "total_duration_time" : "779", 
     "total_duration_time_percent" : "1.0000", 
     "avg_duration_time" : "779.92", 
     "avg_duration_time_percent" : "1.0000", 
     "total_rows" : "134", 
     "total_rows_percent" : "1.0000", 
     "avg_rows" : "134.00", 
     "avg_rows_percent" : "1.0000", 
     "total_logical_reads" : "75564", 
     "total_logical_reads_percent" : "1.0000", 
     "avg_logical_reads" : "75564.00", 
     "avg_logical_reads_percent" : "1.0000", 
     "total_physical_reads" : "1083", 
     "total_physical_reads_percent" : "1.0000", 
     "avg_physical_reads" : "1083.00", 
     "avg_physical_reads_percent" : "1.0000", 
     "total_logical_write" : "41", 
     "total_logical_write_percent" : "1.0000", 
     "avg_logical_write" : "41.00", 
     "avg_logical_write_percent" : "1.0000", 
     "last_execution_time" : "2025-08-03 10:49:02.267" 
   } ], 
   "avg_cpu_time_top_values" : [ { 
     "id" : "0xb1a8d62fcf665755", 
     "data_type" : "AvgWorkerTime", 
     "value" : "294.98" 
   } ], 
 "avg_duration_time_top_values" : [ { 
     "id" : "0xd9bfe0aeb1b674bf", 
     "data_type" : "AvgDuration", 
     "value" : "968.88" 
   } ], 
   "avg_rows_top_values" : [ { 
     "id" : "0xe2d481769b70e309", 
     "data_type" : "AvgReturnRows", 
     "value" : "1428.00" 
   } ], 
   "avg_logical_top_values" : [ { 
     "id" : "0xe2d481769b70e309", 
     "data_type" : "AvgLogicalReads", 
     "value" : "81663.00" 
   } ], 
   "total_cpu_time_top_values" : [ { 
     "id" : "0xcb9a020ae719df11", 
     "data_type" : "TotalWorkerTime", 
     "value" : "988334.79" 
   } ], 
 "total_duration_time_top_values" : [ { 
     "id" : "0xcb9a020ae719df11", 
     "data_type" : "TotalDuration", 
     "value" : "1003770.19" 
   } ], 
   "total_rows_top_values" : [ { 
     "id" : "0x84798563b8b1721c", 
     "data_type" : "TotalReturnRows", 
     "value" : "1742768.00" 
   } ], 
 "total_logical_reads_top_values" : [ { 
     "id" : "0xcb9a020ae719df11", 
     "data_type" : "TotalLogicalReads", 
     "value" : "50701726.00" 
   } ] 
 }