Enabling Azure SQL Database Auto-Tuning at Scale for Microsoft IT
This is a really exciting episode for people that are using Azure SQL Databases - or anyone that's looking at migrating on premise or SQL on IaaS to Azure SQL Database. Lyle Dodge talks with Rajesh Vasireddy, a Program Manager in Microsoft IT who has been working with the Azure SQL Database product group for the past few years on the Azure SQL Database Advisor engine. Rajesh has been enabling auto-tuning of database indexes across thousands of servers in Microsoft IT (and way more databases) to allow the Azure SQL Database engine itself to CREATE and DROP indexes across all of our SQL Azure database instances - regardless if they're running production, dev or test workloads.
The auto tuning feature allows you to let the platform implement the scientifically calculated, tailored recommendations that will significantly enhance the user's business experience and consume resources optimally. There are safeguards built into the auto tuning system to detect regression and revert implemented recommendations, and avoid going over resource utilization thresholds.
Rajesh will talk about an example during the video where he turned on auto tuning across Azure SQL Database for all of Finance IT's footprint in Azure. As of early June 2017, this meant:
- Auto Tuning feature for 829 databases spread across 348 logical servers
- 2219 new indexes created and 5721 unused indexes dropped. Approximately 4000+ queries performance improved as 243 billion logical reads reduced per day.
- ~250 CPU hours saved per day, Consumed additional 360GB space across 829 databases for additional indexes and saved 80GB space for dropped indexes.
- ~400 recommendations reverted automatically because of the regression detected post implementing respective recommendation
So far Rajesh's work across 5 of the Microsoft IT business units has resulted in ~15000+ recommendations implemented as part of tuning with plans to roll out automated recommendations for all of Microsoft IT's Azure SQL Database footprint.
To learn more about the Azure SQL Database Advisor – go here.