News
AI-Based Automatic Tuning Is New Default for Azure SQL Database
Automatic tuning will be turned on by default for the SQL Database service running in the Microsoft Azure cloud, starting early next year.
Though it's been available since early last year, automatic tuning wasn't enabled by default for Azure SQL Database. The feature improves database performance by monitoring internal operations and adjusting them as needed to meet varying conditions.
Some of the things it does automatically include creating database indexes that have been identified as potentially improving query performance, or the reverse: dropping indexes deemed to be redundant or duplicates. Another tuning option, Plan Regression Correction, can identify SQL queries that are using an execution plan found to be slower than a previous plan and use the last known "good" plan instead.
Veljko Vasic, a Microsoft program manager, explained how the feature incorporates cutting-edge AI technology to boost performance in a July blog post.
"Automatic tuning uses artificial intelligence to continuously monitor database workload patterns and recognize opportunities to improve the database performance," Vasic said. "Once confidence is built that a certain tuning action would improve the performance of a database, the Azure SQL Database service automatically does the tuning action in a safe and managed fashion. The service monitors each tuning action and the benefits to performance are reported to the database owners. In the infrequent case of a performance regression, the service quickly reverts the tuning action."
Internal IT groups at Microsoft began using automatic tuning early on, and the Microsoft IT Finance group enjoyed a reduction in CPU hours needed for database queries, as shown in the figure below.
Vasic today wrote an update explaining the new default that enables automatic tuning out of the box. He noted the team has been improving the feature since it was announced in early 2016. Using the experience of early adopters, the team removed barriers to adoption, improved the feature's recommendation models and made other enhancements to increase query speed and make it safer to apply tuning actions.
[Click on image for larger view.] Saving CPU Hours with Automatic Tuning (source: Microsoft).
Speaking of which, Vasic emphasized that the automatic tuning options are fully configurable on both the server and in an individual database. Along with resource-level configuration, each tuning option can be separately enabled or disabled.
The gradual rollout of automatic tuning will begin Jan. 15, 2018, with a warning scheduled to be sent to subscribers some two weeks in advance.
"In case you would like to start using automatic tuning earlier, create a custom automatic tuning configuration, or disable automatic tuning on your resources, please configure the automatic tuning on your resources before January 15th, 2018," Vasic concluded.
After that, "All servers that do not have automatic tuning explicitly configured will inherit Azure defaults, making automatic tuning enabled. Similarly, all databases that do not have automatic tuning explicitly configured will inherit the configuration from the parent server. All newly created databases by default will inherit the configuration from the parent server."
About the Author
David Ramel is an editor and writer for Converge360.