Queries - List By Database

Service:

SQL Database

API Version:

2014-04-01

Gets a list of top queries by database.

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/topQueries?api-version=2014-04-01

URI Parameters

Name	In	Required	Type	Description
databaseName	path	True	string	The name of the database.
resourceGroupName	path	True	string	The name of the resource group that contains the resource. You can obtain this value from the Azure Resource Manager API or the portal.
serverName	path	True	string	The name of the server.
subscriptionId	path	True	string	The subscription ID that identifies an Azure subscription.
api-version	query	True	string	The API version to use for the request.

Responses

Name	Type	Description
200 OK	TopQueriesListResult	OK

Examples

List top queries

Sample request

HTTP

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/group1/providers/Microsoft.Sql/servers/sqlcrudtest/databases/db1/topQueries?api-version=2014-04-01

Sample response

Status code:

200

{
  "value": [
    {
      "type": "Microsoft.Sql/servers/databases/topQueries",
      "numberOfTopQueries": 1,
      "aggregationFunction": "sum",
      "executionType": "any",
      "observedMetric": "cpu",
      "observationStartTime": "2017-07-11T16:21:54.0916567Z",
      "observationEndTime": "2017-07-12T16:21:54.0916567Z",
      "intervalType": "PT1H",
      "queries": [
        {
          "queryId": "19",
          "intervals": [
            {
              "intervalStartTime": "2017-07-11T16:00:00",
              "executionCount": 12,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.000763888888888889
                },
                {
                  "name": "io",
                  "displayName": "Physical Io Reads",
                  "unit": "percentage",
                  "value": 0
                },
                {
                  "name": "logIo",
                  "displayName": "Log Writes",
                  "unit": "percentage",
                  "value": 0
                },
                {
                  "name": "memory",
                  "displayName": "Memory consumption",
                  "unit": "KB",
                  "value": 0
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 3308
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Definitions

Name	Description
QueryAggregationFunction	The function that is used to aggregate each query's metrics.
QueryExecutionType	The execution type that is used to filter the query instances that are returned.
QueryInterval	A database query.
QueryMetric	A database query.
QueryMetricUnit	The unit of measurement
QueryObservedMetricType	The type of metric to use for ordering the top metrics.
QueryStatistic	A database query.
TopQueries	A database query.
TopQueriesListResult	Represents the response to a get top queries request.

QueryAggregationFunction

The function that is used to aggregate each query's metrics.

Name	Type	Description
avg	string
max	string
min	string
sum	string

QueryExecutionType

The execution type that is used to filter the query instances that are returned.

Name	Type	Description
aborted	string
any	string
exception	string
irregular	string
regular	string

QueryInterval

A database query.

Name	Type	Description
executionCount	number	The number of times the query was executed during this interval.
intervalStartTime	string	The start time of the measurement interval (ISO8601 format).
metrics	QueryMetric[]	The list of query metrics during this interval.

QueryMetric

A database query.

Name	Type	Description
displayName	string	The name of the metric for display in user interface
name	string	The name of the metric
unit	QueryMetricUnit	The unit of measurement
value	number	The measured value

QueryMetricUnit

The unit of measurement

Name	Type	Description
KB	string
microseconds	string
percentage	string

QueryObservedMetricType

The type of metric to use for ordering the top metrics.

Name	Type	Description
cpu	string
duration	string
executionCount	string
io	string
logio	string

QueryStatistic

A database query.

Name	Type	Description
intervals	QueryInterval[]	The list of query intervals.
queryId	string	The id of the query

TopQueries

A database query.

Name	Type	Description
aggregationFunction	QueryAggregationFunction	The function that is used to aggregate each query's metrics.
executionType	QueryExecutionType	The execution type that is used to filter the query instances that are returned.
intervalType	string	The duration of the interval (ISO8601 duration format).
numberOfTopQueries	number	The number of requested queries.
observationEndTime	string	The end time for queries that are returned (ISO8601 format)
observationStartTime	string	The start time for queries that are returned (ISO8601 format)
observedMetric	QueryObservedMetricType	The type of metric to use for ordering the top metrics.
queries	QueryStatistic[]	The list of queries.

TopQueriesListResult

Represents the response to a get top queries request.

Name	Type	Description
value	TopQueries[]	The list of top queries.