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.

Saving CPU Hours with Automatic Tuning
[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.

comments powered by Disqus

Featured

  • Xamarin.Forms 5 Preview Ships Ahead of .NET 6 Transition to MAUI

    Microsoft shipped a pre-release version of Xamarin.Forms 5 ahead of a planned transition to MAUI, which will take over beginning with the release of .NET 6 in November 2021.

  • ML.NET Improves Object Detection

    Microsoft improved the object detection capabilities of its ML.NET machine learning framework for .NET developers, adding the ability to train custom models with Model Builder in Visual Studio.

  • More Improvements for VS Code's New Python Language Server

    Microsoft announced more improvements for the new Python language server for Visual Studio Code, Pylance, specializing in rich type information.

  • Death of the Dev Machine?

    Here's a takeaway from this week's Ignite 2020 event: An advanced Azure cloud portends the death of the traditional, high-powered dev machine packed with computing, memory and storage components.

  • COVID-19 Is Ignite 2020's Elephant in the Room: 'Frankly, It Sucks'

    As in all things of our new reality, there was no escaping the drastic changes in routine caused by the COVID-19 pandemic during Microsoft's big Ignite 2020 developer/IT pro conference, this week shifted to an online-only event after drawing tens of thousands of in-person attendees in years past.

Upcoming Events