|   Trend Micro Cloud One™
Open menu

Enable Automatic Tuning for SQL Database Servers

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
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 Cloud 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

Unlock the Remediation Steps

Gain free unlimited access to our full Knowledge Base


Over 600 rules & best practices for and

Get started for FREE

A verification email will be sent to this address
We keep your information private. Learn more.

Thank you!

Please click the link in the confirmation email sent to

You are auditing:

Enable Automatic Tuning for SQL Database Servers

Risk level: Medium