Managed Database Queries - List By Query

Reference

Service:: SQL Database

API Version:: 2021-11-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=2021-11-01

With optional parameters:

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

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	The name of the resource group that contains the resource. You can obtain this value from the Azure Resource Manager API or the portal.
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.
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	Successfully retrieved query text.
Other Status Codes		* Error Responses: * 404 SubscriptionDoesNotHaveServer - The requested server was not found 404 ServerNotInSubscriptionResourceGroup - Specified server does not exist in the specified resource group and subscription. 404 ResourceNotFound - The requested resource was not found.

Name

Type

Description

200 OK

ManagedInstanceQueryStatistics

Successfully retrieved query text.

Other Status Codes

*** Error Responses: ***

404 SubscriptionDoesNotHaveServer - The requested server was not found
404 ServerNotInSubscriptionResourceGroup - Specified server does not exist in the specified resource group and subscription.
404 ResourceNotFound - The requested resource was not found.

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

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=2021-11-01


/**
 * Samples for ManagedDatabaseQueries ListByQuery.
 */
public final class Main {
    /*
     * x-ms-original-file:
     * specification/sql/resource-manager/Microsoft.Sql/stable/2021-11-01/examples/ManagedInstanceQueryStatisticsList.
     * json
     */
    /**
     * Sample code: Obtain query execution statistics.
     * 
     * @param azure The entry point for accessing resource management APIs in Azure.
     */
    public static void obtainQueryExecutionStatistics(com.azure.resourcemanager.AzureResourceManager azure) {
        azure.sqlServers().manager().serviceClient().getManagedDatabaseQueries().listByQuery("sqlcrudtest-7398",
            "sqlcrudtest-4645", "database_1", "42", null, null, null, com.azure.core.util.Context.NONE);
    }
}

To use the Azure SDK library in your project, see this documentation. To provide feedback on this code sample, open a GitHub issue

Sample response

Status code:: 200

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

Obtain query execution statistics. Example with all request parameters.

Sample request

HTTP
Java

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?startTime=03/01/2020 16:23:09&endTime=03/11/2020 14:00:00&interval=P1D&api-version=2021-11-01


import com.azure.resourcemanager.sql.models.QueryTimeGrainType;

/**
 * Samples for ManagedDatabaseQueries ListByQuery.
 */
public final class Main {
    /*
     * x-ms-original-file:
     * specification/sql/resource-manager/Microsoft.Sql/stable/2021-11-01/examples/ManagedInstanceQueryStatisticsListMax
     * .json
     */
    /**
     * Sample code: Obtain query execution statistics. Example with all request parameters.
     * 
     * @param azure The entry point for accessing resource management APIs in Azure.
     */
    public static void obtainQueryExecutionStatisticsExampleWithAllRequestParameters(
        com.azure.resourcemanager.AzureResourceManager azure) {
        azure.sqlServers().manager().serviceClient().getManagedDatabaseQueries().listByQuery("sqlcrudtest-7398",
            "sqlcrudtest-4645", "database_1", "42", "03/01/2020 16:23:09", "03/11/2020 14:00:00",
            QueryTimeGrainType.P1D, com.azure.core.util.Context.NONE);
    }
}

To use the Azure SDK library in your project, see this documentation. To provide feedback on this code sample, open a GitHub issue

Sample response

Status code:: 200

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

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

Sample request

HTTP
Java

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?interval=PT1H&api-version=2021-11-01


import com.azure.resourcemanager.sql.models.QueryTimeGrainType;

/**
 * Samples for ManagedDatabaseQueries ListByQuery.
 */
public final class Main {
    /*
     * x-ms-original-file:
     * specification/sql/resource-manager/Microsoft.Sql/stable/2021-11-01/examples/ManagedInstanceQueryStatisticsListMin
     * .json
     */
    /**
     * Sample code: Obtain query execution statistics. Minimal example with only mandatory request parameters.
     * 
     * @param azure The entry point for accessing resource management APIs in Azure.
     */
    public static void obtainQueryExecutionStatisticsMinimalExampleWithOnlyMandatoryRequestParameters(
        com.azure.resourcemanager.AzureResourceManager azure) {
        azure.sqlServers().manager().serviceClient().getManagedDatabaseQueries().listByQuery("sqlcrudtest-7398",
            "sqlcrudtest-4645", "database_1", "42", null, null, QueryTimeGrainType.PT1H,
            com.azure.core.util.Context.NONE);
    }
}

To use the Azure SDK library in your project, see this documentation. To provide feedback on this code sample, open a GitHub issue

Sample response

Status code:: 200

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

Definitions

Name	Description
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).

ManagedInstanceQueryStatistics

Execution statistics for one particular query

Name	Type	Description
nextLink	string	Link to retrieve next page of results.
value	QueryStatistics[]	Array of results.

QueryMetricInterval

Properties of a query metrics interval.

Name	Type	Description
executionCount	integer	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

Properties of a topquery metric in one interval.

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

QueryMetricUnitType

The unit of the metric.

Name	Type	Description
KB	string
count	string
microseconds	string
percentage	string

QueryStatistics

Name	Type	Description
id	string	Resource ID.
name	string	Resource name.
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).
type	string	Resource type.

QueryTimeGrainType

Interval type (length).

Name	Type	Description
P1D	string
PT1H	string

Share via

Managed Database Queries - List By Query

URI Parameters

Responses

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

ManagedInstanceQueryStatistics

QueryMetricInterval

QueryMetricProperties

QueryMetricUnitType

QueryStatistics

QueryTimeGrainType

Additional resources