If you have been using Azure SQL Elastic pools or databases (Platform As A Service – PASS), I strongly recommend to check your workload and validate Max storage choices!
Traditionally Each Azure Database and elastic pool had fixed storage in each tier and capacity. Example, If you have to host 300 GB database with very minimum CPU and IO requirements you have to choose a Premium tier which is ~145% higher cost to Standard Tier for the same DTU.
New Feature, Add-On Storage is added which allowed additional storage for databases and elastic pools with same capacity of CPU, IO, and Memory. Add-On storage is costs ~8% of cost for standard elastic pools. This feature is very stable and converted as (Max storage choices).
How to save cost:
If Database/Elastic pool is not effectively using its DTU (CPU/IO/Worker Threads) bring down the database/elastic pools to lower capacity using Max storage choices.
Example: 400 eDTU standard pool with 400 GB storage and using maximum of 50% DTU; Bring down to 200eDTU with 400 GB as max storage, this saves cost by 46%!
How to determine if you can gain cost saving with this feature?
Depends on workload and business requirements; I will recommend a few metrics to think about:
- Average eDTU: Average is not a good baseline to define if this can be downgraded but this can be used as comparing factor.
- Max eDTU: good measure for mission critical business scenarios. Decisions made of Max eDTU are more reliable.
- Percentile: This is my favorite measure. Use case: I have a business case which has 30 minute maintenance window every week, I run something which uses 100% CPU for 5 minutes. Lowering the capacity increases my maintenance time to 10 minutes within maintenance window. I prefer to ignore 6 minutes a week during my analysis; 99.94 Percentile value ignores peak 6 minute metric and picks max value from different window;
- Weekly Average (Max eDTU of each Day): I have no business case but I used it at times;
Few Examples (for reference only):
- 2 Standard 400 eDTU elastic pools with maximum eDTU consumption of 42%, I will merge both of them into single elastic pool and delete second pool.
- 3 elastic pools with 99.98 Percentile value below 52, I will balance the load from 3rd pool to 2 other pools and remove third elastic pool.
- 12 pools with uneven load distribution: Move all databases from one of the pools to remaining pools based on load and repeat the process until better load balancing found. At least I recommend to move few databases at a time and eliminate one of the pool.
There is always unknown factor with few metrics not being exposed, Test your application thoroughly before changing it in production. Example, DataIO Throughput is 20 times more on premium pool compare to standard pool. Lowering from Premium to Standard could impact your database.
- Frequently used Azure specific tSQL Commands: https://vijredblog.wordpress.com/2016/10/27/sql-commands-in-azure/
- Compare Database resources in different tiers: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu#compare-the-dtu-based-service-tiers (On Standard Tier IO Throughput is 2.5 IOPS per DTU but on Premium tier Throughput is 48 IOPS per DTU)
- Standalone DTU database limits: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-single-databases
- SQL Elastic pool Limits: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-elastic-pools
- Azure SQL Pricing: https://azure.microsoft.com/en-us/pricing/details/sql-database/elastic/
- Note: Storage Cost on standard tier: 0.17$ / GB / Month