Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Enable Automatic Tuning for SQL Database Servers

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: Sql-012

Enable automatic tuning for Microsoft Azure SQL servers in order to monitor database queries and improve database workload performance. Automatic Tuning is a built-in intelligence feature that automatically tunes your Azure SQL databases to optimize their performance. The feature options are:

FORCE PLAN – this option identifies SQL queries that are using an execution plan that is slower than the previous optimal plan, and queries that are using the last known optimal plan instead of the regressed plan.

CREATE INDEX – this recommendation identifies database indexes that may improve performance of your workload, creates indexes, and automatically verifies if the performance of SQL queries has been improved.

DROP INDEX – this option identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time, such as more than 90 days. Note that this option is not compatible with applications using partition switching and index hints. Dropping unused indexes is not currently supported for Premium and Business Critical service tiers.

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Performance
efficiency

Automatic Tuning feature learns horizontally from all the SQL databases provisioned within the Azure cloud through AI and Machine Learning (ML), and it dynamically improves its tuning actions. The longer an Azure SQL database server runs with automatic tuning on, the better it performs.

Audit

To determine if automatic tuning is enabled for your Azure SQL database servers, perform the following actions:

Note: Getting Automatic Tuning feature configuration status using Microsoft Azure CLI or Azure PowerShell is not currently supported.

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

04 Click on the name of the SQL database server that you want to examine.

05 On the resource navigation panel, select Overview to view the configuration details available for the selected database server.

06 On the Overview page, select the Features tab and check the Automatic tuning configuration status. If the configuration status is set to NOT CONFIGURED, the Automatic Tuning feature is not enabled for the selected Microsoft Azure SQL database server.

07 Repeat steps no. 4 – 6 for each SQL database server available within the current Azure subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

Remediation / Resolution

o enable and configure automatic tuning for your Microsoft Azure SQL database servers, perform the following actions:

Note: Enabling and configuring Automatic Tuning for SQL database servers using Microsoft Azure CLI or Azure PowerShell is not currently supported, the feature can be configured only through Azure Management Console (Azure Portal).

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

04 Click on the name of the SQL database server that you want to reconfigure (see Audit section part I to identify the right SQL server).

05 On the resource navigation panel, under Intelligent Performance, select Automatic tuning to access the configuration settings for the Automatic Tuning feature.

06 On the Automatic tuning configuration page, select Revert to defaults to let the database server to inherit the automatic tuning configuration from Azure defaults. Under Desired State, you can configure each automatic tuning option individually based on your workload needs. If you choose INHERIT for an option, that option will inherit the state from Azure or the parent SQL server. Explicitly selecting ON or OFF will override the inherited value to the selected value. It is recommended to let the system set the ideal automatic tuning configuration by allowing all available options (i.e. FORCE PLAN, CREATE INDEX and DROP INDEX) to inherit the state. Click Apply to save your configuration changes and enable Automatic Tuning for the selected Microsoft Azure SQL database server. This configuration will be applied to all the SQL databases that inherit automatic tuning configuration from this SQL database server (parent server). If required, this configuration can be overridden and specified for each SQL database individually.

07 Repeat steps no. 4 – 6 for each SQL database server provisioned in the selected subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Oct 26, 2019

Related Sql rules