Managed Database Queries - List By Query

Service:: SQL Database

API Version:: 2025-01-01

Get query execution statistics by query id.

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/managedInstances/{managedInstanceName}/databases/{databaseName}/queries/{queryId}/statistics?api-version=2025-01-01

With optional parameters:

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/managedInstances/{managedInstanceName}/databases/{databaseName}/queries/{queryId}/statistics?api-version=2025-01-01&startTime={startTime}&endTime={endTime}&interval={interval}

URI Parameters

Name	In	Required	Type	Description
databaseName	path	True	string	The name of the database.
managedInstanceName	path	True	string	The name of the managed instance.
queryId	path	True	string
resourceGroupName	path	True	string minLength: 1 maxLength: 90	The name of the resource group. The name is case insensitive.
subscriptionId	path	True	string (uuid)	The ID of the target subscription. The value must be an UUID.
api-version	query	True	string minLength: 1	The API version to use for this operation.
endTime	query		string	End time for observed period.
interval	query		QueryTimeGrainType	The time step to be used to summarize the metric values.
startTime	query		string	Start time for observed period.

Responses

Name	Type	Description
200 OK	ManagedInstanceQueryStatistics	Azure operation completed successfully.
Other Status Codes	ErrorResponse	An unexpected error response.

Security

azure_auth

Azure Active Directory OAuth2 Flow.

Type: oauth2
Flow: implicit
Authorization URL: https://login.microsoftonline.com/common/oauth2/authorize

Scopes

Name	Description
user_impersonation	impersonate your user account

Examples

Obtain query execution statistics

Obtain query execution statistics. Example with all request parameters.

Obtain query execution statistics. Minimal example with only mandatory request parameters.

Obtain query execution statistics

Sample request

HTTP

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/databases/database_1/queries/42/statistics?api-version=2025-01-01

Sample response

Status code:: 200

{
  "value": [
    {
      "type": "Microsoft.Sql/managedInstances/databases/queries/statistics",
      "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/databases/database_1/queries/42/statistics/35",
      "properties": {
        "databaseName": "db1",
        "endTime": "03/02/2020 18:34:58",
        "intervals": [
          {
            "executionCount": 160,
            "intervalStartTime": "03/02/2020 08:00:00",
            "intervalType": "PT1H",
            "metrics": [
              {
                "name": "cpu",
                "avg": 1.665347222222222E-05,
                "displayName": "Cpu",
                "max": 2.5243055555555557E-05,
                "min": 1.507638888888889E-05,
                "stdev": 1.4894345929850385E-06,
                "sum": 0.0026645555555555554,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "io",
                "avg": 0,
                "displayName": "Physical Io Reads",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "logIo",
                "avg": 0,
                "displayName": "Log Writes",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "memory",
                "avg": 0,
                "displayName": "Memory consumption",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "KB",
                "value": 0
              },
              {
                "name": "duration",
                "avg": 5026.625,
                "displayName": "Query duration",
                "max": 18490,
                "min": 4373,
                "stdev": 1487.3520882343225,
                "sum": 804260,
                "unit": "microseconds",
                "value": 0
              }
            ]
          },
          {
            "executionCount": 20,
            "intervalStartTime": "03/02/2020 09:00:00",
            "intervalType": "PT1H",
            "metrics": [
              {
                "name": "cpu",
                "avg": 4.479774305555555E-05,
                "displayName": "Cpu",
                "max": 0.00014645833333333332,
                "min": 2.3430555555555557E-05,
                "stdev": 3.830118344204395E-05,
                "sum": 0.0008959548611111111,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "io",
                "avg": 0,
                "displayName": "Physical Io Reads",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "logIo",
                "avg": 0,
                "displayName": "Log Writes",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "memory",
                "avg": 0,
                "displayName": "Memory consumption",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "KB",
                "value": 0
              },
              {
                "name": "duration",
                "avg": 12963.2,
                "displayName": "Query duration",
                "max": 42289,
                "min": 6813,
                "stdev": 11040.140794392071,
                "sum": 259264,
                "unit": "microseconds",
                "value": 0
              }
            ]
          },
          {
            "executionCount": 80,
            "intervalStartTime": "03/02/2020 15:00:00",
            "intervalType": "PT1H",
            "metrics": [
              {
                "name": "cpu",
                "avg": 1.9315538194444445E-05,
                "displayName": "Cpu",
                "max": 2.764236111111111E-05,
                "min": 1.8215277777777777E-05,
                "stdev": 1.0716305801875179E-06,
                "sum": 0.0015452430555555556,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "io",
                "avg": 0,
                "displayName": "Physical Io Reads",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "logIo",
                "avg": 0,
                "displayName": "Log Writes",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "memory",
                "avg": 0,
                "displayName": "Memory consumption",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "KB",
                "value": 0
              },
              {
                "name": "duration",
                "avg": 5586.2625,
                "displayName": "Query duration",
                "max": 7982,
                "min": 5264,
                "stdev": 310.4915757210727,
                "sum": 446901,
                "unit": "microseconds",
                "value": 0
              }
            ]
          },
          {
            "executionCount": 80,
            "intervalStartTime": "03/02/2020 17:00:00",
            "intervalType": "PT1H",
            "metrics": [
              {
                "name": "cpu",
                "avg": 1.9085373263888888E-05,
                "displayName": "Cpu",
                "max": 2.782638888888889E-05,
                "min": 1.7819444444444443E-05,
                "stdev": 1.2309244108727927E-06,
                "sum": 0.0015268298611111112,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "io",
                "avg": 0,
                "displayName": "Physical Io Reads",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "logIo",
                "avg": 0,
                "displayName": "Log Writes",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "memory",
                "avg": 0,
                "displayName": "Memory consumption",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "KB",
                "value": 0
              },
              {
                "name": "duration",
                "avg": 5517.2,
                "displayName": "Query duration",
                "max": 8052,
                "min": 5147,
                "stdev": 356.8763581410226,
                "sum": 441376,
                "unit": "microseconds",
                "value": 0
              }
            ]
          }
        ],
        "queryId": "35",
        "startTime": "03/01/2020 18:34:58"
      }
    }
  ]
}

Obtain query execution statistics. Example with all request parameters.

Sample request

HTTP

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/databases/database_1/queries/42/statistics?api-version=2025-01-01&startTime=03/01/2020 16:23:09&endTime=03/11/2020 14:00:00&interval=P1D

Sample response

Status code:: 200

{
  "value": [
    {
      "type": "Microsoft.Sql/managedInstances/databases/queries/statistics",
      "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/databases/database_1/queries/42/statistics/28",
      "properties": {
        "databaseName": "db1",
        "endTime": "03/11/2020 14:00:00",
        "intervals": [
          {
            "executionCount": 1,
            "intervalStartTime": "03/11/2020 00:00:00",
            "intervalType": "P1D",
            "metrics": [
              {
                "name": "cpu",
                "avg": 0.0015934667245370371,
                "displayName": "Cpu",
                "max": 0.0015934667245370371,
                "min": 0.0015934667245370371,
                "stdev": 0,
                "sum": 0.0015934667245370371,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "io",
                "avg": 4.224537037037037E-06,
                "displayName": "Physical Io Reads",
                "max": 4.224537037037037E-06,
                "min": 4.224537037037037E-06,
                "stdev": 0,
                "sum": 4.224537037037037E-06,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "logIo",
                "avg": 0,
                "displayName": "Log Writes",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "memory",
                "avg": 8336,
                "displayName": "Memory consumption",
                "max": 8336,
                "min": 8336,
                "stdev": 0,
                "sum": 8336,
                "unit": "KB",
                "value": 0
              },
              {
                "name": "duration",
                "avg": 11091296,
                "displayName": "Query duration",
                "max": 11091296,
                "min": 11091296,
                "stdev": 0,
                "sum": 11091296,
                "unit": "microseconds",
                "value": 0
              }
            ]
          }
        ],
        "queryId": "28",
        "startTime": "03/01/2020 16:23:09"
      }
    }
  ]
}

Obtain query execution statistics. Minimal example with only mandatory request parameters.

Sample request

HTTP

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/databases/database_1/queries/42/statistics?api-version=2025-01-01&interval=PT1H

Sample response

Status code:: 200

{
  "value": [
    {
      "type": "Microsoft.Sql/managedInstances/databases/queries/statistics",
      "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/databases/database_1/queries/42/statistics/28",
      "properties": {
        "databaseName": "db1",
        "endTime": "03/11/2020 14:00:30",
        "intervals": [
          {
            "executionCount": 1,
            "intervalStartTime": "03/11/2020 11:00:00",
            "intervalType": "PT1H",
            "metrics": [
              {
                "name": "cpu",
                "avg": 0.03824320138888889,
                "displayName": "Cpu",
                "max": 0.03824320138888889,
                "min": 0.03824320138888889,
                "stdev": 0,
                "sum": 0.03824320138888889,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "io",
                "avg": 0.0001013888888888889,
                "displayName": "Physical Io Reads",
                "max": 0.0001013888888888889,
                "min": 0.0001013888888888889,
                "stdev": 0,
                "sum": 0.0001013888888888889,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "logIo",
                "avg": 0,
                "displayName": "Log Writes",
                "max": 0,
                "min": 0,
                "stdev": 0,
                "sum": 0,
                "unit": "percentage",
                "value": 0
              },
              {
                "name": "memory",
                "avg": 8336,
                "displayName": "Memory consumption",
                "max": 8336,
                "min": 8336,
                "stdev": 0,
                "sum": 8336,
                "unit": "KB",
                "value": 0
              },
              {
                "name": "duration",
                "avg": 11091296,
                "displayName": "Query duration",
                "max": 11091296,
                "min": 11091296,
                "stdev": 0,
                "sum": 11091296,
                "unit": "microseconds",
                "value": 0
              }
            ]
          }
        ],
        "queryId": "28",
        "startTime": "03/10/2020 14:00:30"
      }
    }
  ]
}

Definitions

Name	Description
createdByType	The type of identity that created the resource.
ErrorAdditionalInfo	The resource management error additional info.
ErrorDetail	The error detail.
ErrorResponse	Error response
ManagedInstanceQueryStatistics	Execution statistics for one particular query
QueryMetricInterval	Properties of a query metrics interval.
QueryMetricProperties	Properties of a topquery metric in one interval.
QueryMetricUnitType	The unit of the metric.
QueryStatistics
QueryTimeGrainType	Interval type (length).
systemData	Metadata pertaining to creation and last modification of the resource.

createdByType

Enumeration

The type of identity that created the resource.

Value	Description
User
Application
ManagedIdentity
Key

ErrorAdditionalInfo

Object

The resource management error additional info.

Name	Type	Description
info	object	The additional info.
type	string	The additional info type.

ErrorDetail

Object

The error detail.

Name	Type	Description
additionalInfo	ErrorAdditionalInfo[]	The error additional info.
code	string	The error code.
details	ErrorDetail[]	The error details.
message	string	The error message.
target	string	The error target.

ErrorResponse

Object

Error response

Name	Type	Description
error	ErrorDetail	The error object.

ManagedInstanceQueryStatistics

Object

Execution statistics for one particular query

Name	Type	Description
nextLink	string (uri)	The link to the next page of items
value	QueryStatistics[]	The QueryStatistics items on this page

QueryMetricInterval

Object

Properties of a query metrics interval.

Name	Type	Description
executionCount	integer (int64)	Execution count of a query in this interval.
intervalStartTime	string	The start time for the metric interval (ISO-8601 format).
intervalType	QueryTimeGrainType	Interval type (length).
metrics	QueryMetricProperties[]	List of metric objects for this interval

QueryMetricProperties

Object

Properties of a topquery metric in one interval.

Name	Type	Description
avg	number (double)	Metric value when avg() aggregate function is used over the interval.
displayName	string	The UI appropriate name for the metric.
max	number (double)	Metric value when max() aggregate function is used over the interval.
min	number (double)	Metric value when min() aggregate function is used over the interval.
name	string	The name information for the metric.
stdev	number (double)	Metric value when stdev aggregate function is used over the interval.
sum	number (double)	Metric value when sum() aggregate function is used over the interval.
unit	QueryMetricUnitType	The unit of the metric.
value	number (double)	The value of the metric.

QueryMetricUnitType

Enumeration

The unit of the metric.

Value	Description
percentage	percentage
KB	KB
microseconds	microseconds
count	count

QueryStatistics

Object

Name	Type	Description
id	string (arm-id)	Fully qualified resource ID for the resource. E.g. "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/{resourceProviderNamespace}/{resourceType}/{resourceName}"
name	string	The name of the resource
properties.databaseName	string	Database name of the database in which this query was executed.
properties.endTime	string	The end time for the metric (ISO-8601 format).
properties.intervals	QueryMetricInterval[]	List of intervals with appropriate metric data
properties.queryId	string	Unique query id (unique within one database).
properties.startTime	string	The start time for the metric (ISO-8601 format).
systemData	systemData	Azure Resource Manager metadata containing createdBy and modifiedBy information.
type	string	The type of the resource. E.g. "Microsoft.Compute/virtualMachines" or "Microsoft.Storage/storageAccounts"

QueryTimeGrainType

Enumeration

Interval type (length).

Value	Description
PT1H	PT1H
P1D	P1D

systemData

Object

Metadata pertaining to creation and last modification of the resource.

Name	Type	Description
createdAt	string (date-time)	The timestamp of resource creation (UTC).
createdBy	string	The identity that created the resource.
createdByType	createdByType	The type of identity that created the resource.
lastModifiedAt	string (date-time)	The timestamp of resource last modification (UTC)
lastModifiedBy	string	The identity that last modified the resource.
lastModifiedByType	createdByType	The type of identity that last modified the resource.

Share via

Managed Database Queries - List By Query

URI Parameters

Responses

Security

azure_auth

Scopes

Examples

Obtain query execution statistics

Sample request

Sample response

Obtain query execution statistics. Example with all request parameters.

Sample request

Sample response

Obtain query execution statistics. Minimal example with only mandatory request parameters.

Sample request

Sample response

Definitions

createdByType

ErrorAdditionalInfo

ErrorDetail

ErrorResponse

ManagedInstanceQueryStatistics

QueryMetricInterval

QueryMetricProperties

QueryMetricUnitType

QueryStatistics

QueryTimeGrainType

systemData