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

  • Visual Studio Code Dev Team Cleans Up

    The Visual Studio Code development team focused on some housekeeping in the October update, closing more than 4,000 issues on GitHub, where the cross-platform, open-source editor lives.

  • ML.NET Model Builder Update Boosts Image Classification

    Microsoft announced an update to the Model Builder component of its ML.NET machine learning framework, boosting image classification and adding "try your model" functionality for predictions with sample input.

  • How to Do Naive Bayes with Numeric Data Using C#

    Dr. James McCaffrey of Microsoft Research uses a full code sample and screenshots to demonstrate how to create a naive Bayes classification system when the predictor values are numeric, using the C# language without any special code libraries.

  • Vortex

    Open Source 'Infrastructure-as-Code' SDK Adds .NET Core Support for Working with Azure

    Pulumi, known for its "Infrastructure-as-Code" cloud development tooling, has added support for .NET Core, letting .NET-centric developers use C#, F# and VB.NET to create, deploy, and manage Azure infrastructure.

  • .NET Framework Not Forgotten: Repair Tool Updated

    Even though Microsoft's development focus has shifted to the open-source, cross-platform .NET Core initiative -- with the aging, traditional, Windows-only .NET Framework relegated primarily to fixes and maintenance such as quality and reliability improvements -- the latter is still getting some other attention, as exemplified in a repair tool update.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events