Deciphering Performance levels and DTUs for a single Azure SQL DB
I was asked by a higher education customer in the southwest to explain the various Azure SQL Database tiers and levels, what DTUs are, how long does it take to move performance levels, and what type of outage occurs when using a single database Azure SQL DB?
They had a single 210GB on premises SQL database they wanted to replicate to Azure SQL DB with high initial load volume.
Here were some of the questions they asked:
Can you explain the different Azure SQL DB service tiers and performance levels for a single database?
Azure SQL single database service tiers and performance levels
For a single Azure SQL database, there are currently three types of service tiers, Basic, Standard, and Premium and 11 total performance levels amongst them with P15 being the newest tier. You can also select from a single database to an elastic pool with up to hundreds of databases in a shared set of resources. The nice part is you can maintain a combination of both single database and elastic database pools so you are not locked into a initial direction.
Here is a useful table explaining each performance level in detail for a single database:
Azure SQL single database service tiers and performance level matrix
For more on the Azure SQL DB service tiers you can read about them here and more on elastic database pools not covered in this post see here and here.
Can you help me understand the DTUs and how can we better calculate our DTUs needed?
DTU or Database Transaction Unit (formerly Throughput) is the unit of measure in Azure SQL Database that represents the relative power of databases based on a real-world measure: DB transaction.
You can use this very useful 3rd party DTU calculator to get an estimate of DTUs you might need for your database. To use the tool, first you run SQL-perfmon against your on premises SQL Server to capture utilization and you can upload the outputted CSV file to get a baseline service tier, performance level, and DTU estimation. This will get you closer to proper sizing but you still will need to monitor your single database performance thresholds to fully determine tier, performance level and DTUs (see under ‘Monitoring resource use’ section).
Here are some high level Azure Service DB (ASDB) benchmarks for determining best service and performance level fit based on TPS:
How long does it take to move performance levels of my Azure SQL DB? Is there any downtime or data loss?
The whole advantage of using Platform or DB as a service is to allow for dynamic scale up or scale down of database resources on demand. The three questions which follow are: data loss, downtime and how long does it take to scale up?
The good news is there is no data loss during a service tier or performance level move since we take a replica of the existing database and then move the connections over to the new tier or performance level.
For downtime, there is an average of 4 seconds of blocked connections to the old level database during a move to the new level with 99% of the level moves occurring in less than 30 seconds. In some outlier cases, the move may take longer than 30 seconds.
For how long does the database take to be up and running on the new performance levels, the answer is it truly depends on the size of the current tier/level, database, transactions, etc. As an example, a 250GB Azure SQL database takes on average 6 hours to upgrade the performance level for the Standard tiers and 3 hours for Premium tiers.
Using my customer’s example of a 210GB Azure SQL DB, we could imply that it should not incur more than 30 seconds of downtime during a performance level up, and be running at the new performance level in less than 3 hours since they were running on a Premium tier.
For more on this topic see here.
Please see our other useful posts on Azure SQL Database on our blog here and here.