Managed Instances - List By Managed Instance

Get top resource consuming queries of a managed instance.

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

URI Parameters

Name In Required Type Description
managedInstanceName
path True

string

The name of the managed instance.

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.

aggregationFunction
query

AggregationFunctionType

Aggregation function to be used, default value is 'sum'

databases
query

string

Comma separated list of databases to be included into search. All DB's are included if this parameter is not specified.

endTime
query

string

End time for observed period.

interval
query

QueryTimeGrainType

The time step to be used to summarize the metric values. Default value is PT1H

numberOfQueries
query

integer

int32

How many 'top queries' to return. Default is 5.

observationMetric
query

MetricType

Metric to be used for ranking top queries. Default is 'cpu'

startTime
query

string

Start time for observed period.

Responses

Name Type Description
200 OK

TopQueriesListResult

Successfully retrieved top queries for managed instance.

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 list of instance's top resource consuming queries.
Obtain list of instance's top resource consuming queries. Full-blown request and response.
Obtain list of instance's top resource consuming queries. Minimal request and response.

Obtain list of instance's top resource consuming queries.

Sample request

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/topqueries?interval=PT1H&observationMetric=duration&api-version=2021-11-01

Sample response

{
  "value": [
    {
      "numberOfQueries": 5,
      "aggregationFunction": "sum",
      "observationMetric": "cpu",
      "intervalType": "P1D",
      "startTime": "03/01/2020 00:00:00",
      "endTime": "03/05/2020 13:00:00",
      "queries": [
        {
          "databaseName": "db1",
          "queryId": "25",
          "intervals": [
            {
              "intervalStartTime": "03/03/2020 00:00:00",
              "executionCount": 1,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0015841714409722222
                },
                {
                  "name": "io",
                  "displayName": "Physical Io Reads",
                  "unit": "percentage",
                  "value": 4.340277777777778E-06
                },
                {
                  "name": "logIo",
                  "displayName": "Log Writes",
                  "unit": "percentage",
                  "value": 0
                },
                {
                  "name": "memory",
                  "displayName": "Memory consumption",
                  "unit": "KB",
                  "value": 8336
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 11306905
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db1",
          "queryId": "21",
          "intervals": [
            {
              "intervalStartTime": "03/03/2020 00:00:00",
              "executionCount": 1,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0009521432291666667
                },
                {
                  "name": "io",
                  "displayName": "Physical Io Reads",
                  "unit": "percentage",
                  "value": 2.3148148148148148E-07
                },
                {
                  "name": "logIo",
                  "displayName": "Log Writes",
                  "unit": "percentage",
                  "value": 0
                },
                {
                  "name": "memory",
                  "displayName": "Memory consumption",
                  "unit": "KB",
                  "value": 1024
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 6620020
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db3",
          "queryId": "3",
          "intervals": [
            {
              "intervalStartTime": "03/04/2020 00:00:00",
              "executionCount": 104,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0008006611689814815
                },
                {
                  "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": 5543088
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db2",
          "queryId": "3",
          "intervals": [
            {
              "intervalStartTime": "03/03/2020 00:00:00",
              "executionCount": 89,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0006882543402777778
                },
                {
                  "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": 4761877
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db3",
          "queryId": "22",
          "intervals": [
            {
              "intervalStartTime": "03/04/2020 00:00:00",
              "executionCount": 1,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0006220661168981482
                },
                {
                  "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": 1024
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 4454161
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Obtain list of instance's top resource consuming queries. Full-blown request and response.

Sample request

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/topqueries?databases=db1,db2&startTime=2020-03-10T12:00:00Z&endTime=2020-03-12T12:00:00Z&interval=P1D&observationMetric=cpu&api-version=2021-11-01

Sample response

{
  "value": [
    {
      "numberOfQueries": 5,
      "aggregationFunction": "sum",
      "observationMetric": "cpu",
      "intervalType": "P1D",
      "startTime": "03/10/2020 00:00:00",
      "endTime": "03/12/2020 13:00:00",
      "queries": [
        {
          "databaseName": "db1",
          "queryId": "28",
          "intervals": [
            {
              "intervalStartTime": "03/11/2020 00:00:00",
              "executionCount": 1,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0015934667245370371
                },
                {
                  "name": "io",
                  "displayName": "Physical Io Reads",
                  "unit": "percentage",
                  "value": 4.224537037037037E-06
                },
                {
                  "name": "logIo",
                  "displayName": "Log Writes",
                  "unit": "percentage",
                  "value": 0
                },
                {
                  "name": "memory",
                  "displayName": "Memory consumption",
                  "unit": "KB",
                  "value": 8336
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 11091296
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db1",
          "queryId": "24",
          "intervals": [
            {
              "intervalStartTime": "03/11/2020 00:00:00",
              "executionCount": 1,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0009522783564814815
                },
                {
                  "name": "io",
                  "displayName": "Physical Io Reads",
                  "unit": "percentage",
                  "value": 1.7361111111111112E-07
                },
                {
                  "name": "logIo",
                  "displayName": "Log Writes",
                  "unit": "percentage",
                  "value": 0
                },
                {
                  "name": "memory",
                  "displayName": "Memory consumption",
                  "unit": "KB",
                  "value": 1024
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 6625562
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db1",
          "queryId": "3",
          "intervals": [
            {
              "intervalStartTime": "03/11/2020 00:00:00",
              "executionCount": 82,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0007183139467592593
                },
                {
                  "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": 4970199
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db1",
          "queryId": "29",
          "intervals": [
            {
              "intervalStartTime": "03/11/2020 00:00:00",
              "executionCount": 1,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0006494454571759259
                },
                {
                  "name": "io",
                  "displayName": "Physical Io Reads",
                  "unit": "percentage",
                  "value": 5.034722222222222E-06
                },
                {
                  "name": "logIo",
                  "displayName": "Log Writes",
                  "unit": "percentage",
                  "value": 0
                },
                {
                  "name": "memory",
                  "displayName": "Memory consumption",
                  "unit": "KB",
                  "value": 1024
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 4530668
                }
              ]
            }
          ]
        },
        {
          "databaseName": "db2",
          "queryId": "25",
          "intervals": [
            {
              "intervalStartTime": "03/11/2020 00:00:00",
              "executionCount": 1,
              "metrics": [
                {
                  "name": "cpu",
                  "displayName": "Cpu",
                  "unit": "percentage",
                  "value": 0.0006275368923611112
                },
                {
                  "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": 1024
                },
                {
                  "name": "duration",
                  "displayName": "Query duration",
                  "unit": "microseconds",
                  "value": 4349943
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Obtain list of instance's top resource consuming queries. Minimal request and response.

Sample request

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-7398/providers/Microsoft.Sql/managedInstances/sqlcrudtest-4645/topqueries?api-version=2021-11-01

Sample response

{
  "value": [
    {
      "numberOfQueries": 5,
      "aggregationFunction": "sum",
      "observationMetric": "cpu",
      "intervalType": "PT1H",
      "startTime": "03/10/2020 12:00:00",
      "endTime": "03/11/2020 12:24:07",
      "queries": []
    }
  ]
}

Definitions

Name Description
AggregationFunctionType

Aggregation function to be used, default value is 'sum'

MetricType

Metric to be used for ranking top queries. Default is 'cpu'

QueryMetricInterval

Properties of a query metrics interval.

QueryMetricProperties

Properties of a topquery metric in one interval.

QueryMetricUnitType

The unit of the metric.

QueryStatisticsProperties

Properties of a query execution statistics.

QueryTimeGrainType

Interval type (length).

TopQueries
TopQueriesListResult

A list of top resource consuming queries on managed instance

AggregationFunctionType

Aggregation function to be used, default value is 'sum'

Name Type Description
avg

string

max

string

min

string

stdev

string

sum

string

MetricType

Metric to be used for ranking top queries. Default is 'cpu'

Name Type Description
cpu

string

dtu

string

duration

string

io

string

logIo

string

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

QueryStatisticsProperties

Properties of a query execution statistics.

Name Type Description
databaseName

string

Database name of the database in which this query was executed.

endTime

string

The end time for the metric (ISO-8601 format).

intervals

QueryMetricInterval[]

List of intervals with appropriate metric data

queryId

string

Unique query id (unique within one database).

startTime

string

The start time for the metric (ISO-8601 format).

QueryTimeGrainType

Interval type (length).

Name Type Description
P1D

string

PT1H

string

TopQueries

Name Type Description
aggregationFunction

string

Aggregation function used to calculate query metrics.

endTime

string

The end time for the metric (ISO-8601 format).

intervalType

QueryTimeGrainType

Interval type (length).

numberOfQueries

integer

Requested number of top queries.

observationMetric

string

Metric used to rank queries.

queries

QueryStatisticsProperties[]

List of top resource consuming queries with appropriate metric data

startTime

string

The start time for the metric (ISO-8601 format).

TopQueriesListResult

A list of top resource consuming queries on managed instance

Name Type Description
nextLink

string

Link to retrieve next page of results.

value

TopQueries[]

Array of results.