How do I query Azure SQL elastic database utilization?

1
votes

In the Azure Portal, under SQL Elastic Pools, I am able to obtain a list of databases in the pool together with the Peak EDTU, Avg EDTU, Size (GB) and Status.

How do I obtain this list via T-SQL, REST, Powershell, etc?

We have a large number of Azure SQL servers and elastic pools and I want a consolidated list of every database in the subscription together with it's EDTU utilization.

2
azure-sql-database

2 Answers

2
votes

There is no single query to get all the information that you are looking for. But you can get this information by combining few queries.

  • You can use this REST API to list all servers in a subscription
  • You can query this REST API to get a list of all databases in a server/elastic pool.
  • you can query sys.resource_stats DMV to get the list of all databases in that server along with their DTU utilization. Or use this REST API to get DTU usage for an individual database.

-- Srini

0
votes

You can use Azure Cli to get the details. The below example will get you the Standard elastic pool with 100 dtu's in West US:

az sql elastic-pool list-editions -l westus --edition Standard --dtu 100 --show-details db-min-dtu db-max-dtu db-max-size

Let me know if this helped.