1

I'm migrating away from Azure SQL and my new cloud hosting service wants stats like "estimated inserts per hour on average", traffic/bandwidth usage per month, or "average read/writes on table x per day".

How can I get statistics on prior DB usage per table or database with Azure?

1 Answer 1

2

Depending on how long the system has been up on the current underlying server, you may be able to get some of the info you're looking for from these DMVs (though many are limited to a maximum of 14 or 30 days of data retention).

  • sys.database_connection_stats - while it doesn't provide any of the specific info you're after, it can give you some clue about how busy your server has been, cycle fluctuations, etc.

  • sys.dm_db_index_usage_stats - again, depending on how far back this data goes, this can show you number of read/write operations against tables over the entire period (no breakdown by hour or day, you'll have to average). Also note that an update that affects one row and another that affects 2 billion rows are each treated equivalently as a single write operation.

  • sys.resource_stats - shows CPU usage, storage, and I/O metrics in 5-minute intervals over the past 14 days (or less). Not sure how valuable this info is for your needs - not sure how you will translate avg_data_io_percent to something meaningful for your new hosting provider. You can get more granular data from sys.dm_db_resource_stats (every 15 seconds), but this only goes back an hour (and requires elevated privileges).

  • sys.bandwidth_usage - on a v11 server you may be able to see some details about in/out bandwidth. On v12, however, this view has been deprecated and returns an empty result. I don't know of a direct replacement; you should be able to get some info on this in "Resource Costs" in the Azure portal.

You can get a rough idea about how far back your DMV data goes, you can use this as a ballpark:

SELECT MIN(start_time) FROM sys.event_log WITH (NOLOCK);

That DMV may have some useful info too, but there are warnings all over the place about the impact it can have on performance - you're migrating away, but I'm putting this here for future readers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.