Database automatic tuning in Microsoft Azure SQL database has become a crucial aspect for database administrators (DBAs). With an increasing amount of data and increasing complexity of systems, many DBAs or database professionals often find it challenging to manually analyze, optimize, and tune databases. Luckily, Microsoft Azure has a functionality that makes all of these tasks much simpler and easier: Database Automatic Tuning.
Configuring Database Automatic Tuning
Azure SQL Database Automatic Tuning provides an equal balance between performance stability and query-performance efficiency. When it’s enabled, Azure SQL Database identifies the SQL plan choice regressions. If it’s a short regression, it lets it naturally self-correct, but if the regression lasts long, it forces the last known-good plan.
You can enable automatic tuning at the server or a database level by executing a simple command:
For enabling automatic tuning at the server level, run the following command:
SQL
ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO
And for disabling, you may use:
SQL
ALTER DATABASE current SET AUTOMATIC_TUNING = MANUAL
Same can be achieved at the individual database level as well:
SQL
ALTER DATABASE
Automatic tuning options include:
- CREATE_INDEX: Identifies when the database engine can benefit from a new index and creates that index.
- DROP_INDEX: Identifies when an index is not being used and is taking up disk space, and deletes it.
- FORCE_LAST_GOOD_PLAN: Identifies when query performance will degrade and fixes it by forcing the last known-good plan.
Applications of Azure SQL Database Automatic Tuning
Database automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and learnings built over millions of databases. It can be leveraged to automatically manage performance in two ways:
- Automated performance tuning notifications: Automatic tuning provides tuning recommendations via notifications in the Azure portal. Each recommendation comes with an associated script. When you apply a recommendation, Azure runs this script on your behalf to improve the performance of your database.
- Fully automated performance tuning: Automatic tuning can fully manage performance for you. If you choose to enable automatic tuning on your database, Azure will continuously monitor your database usage, identify the actions to improve performance, and take action to tune your database.
Monitoring Automatic Tuning Actions
Azure SQL Database Automatic Tuning provides transparency into its operations. As a DBA, you can monitor tuning actions and validate performance gains directly through the Azure portal. Beneath the Automatic Tuning page on the Azure portal, you’ll find tuning action history and performance chart displaying workload performance before and after tuning actions.
Therefore, configuring database automatic tuning properly can lead to significant performance improvements and stability in an Azure SQL Database. Understanding the mechanics behind Azure SQL automatic tuning options is a crucial ability that every DBA needs to have when administering Microsoft Azure SQL solutions, which may also come handy when preparing for exams like DP-300.
Practice Test
True or False: Azure SQL Database Automatic tuning is an option that can be configured at the server and individual database level.
- True
- False
Answer: True.
Explanation: The Azure SQL Database Automatic tuning can be set both at the server level and at the individual database level.
True or False: Setting the automatic tuning on a particular database will override the server-level settings.
- True
- False
Answer: True.
Explanation: If you configure automatic tuning at the database level, that configuration will override the server-level settings.
Which of the following can be automatically managed by Azure SQL database automatic tuning?
- a) Index Drop
- b) Create Index
- c) Force Plan
- d) All of the above
Answer: d) All of the above
Explanation: Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries, and it can automatically manage indexes and plan forcing.
The automatic tuning option “FORCE_LAST_GOOD_PLAN” is used to:
- a) Force a good query plan for a particular SQL statement
- b) Force Azure SQL Database to use the last good execution plan it has
- c) Force all queries to use a single execution plan
Answer: b) Force Azure SQL Database to use the last good execution plan.
Explanation: The FORCE_LAST_GOOD_PLAN option forces a query to use the last known good execution plan, if the current execution plan is less efficient.
In the Azure portal, where can the Automated tuning settings be found?
- a) Under SQL Server settings
- b) Under the SQL Database’s Performance tab
- c) Under SQL Server’s Performance tab
- d) Under SQL Database’s settings
Answer: b) Under the SQL Database’s Performance tab
Explanation: The Automated tuning settings can be found in the Performance section of the Azure SQL Database’s menu in the Azure portal.
When automatic tuning is enabled, who is responsible for monitoring and tuning the database performance?
- a) The database administrator alone
- b) The automatic tuning feature alone
- c) Both the database administrator and the automatic tuning feature
Answer: c) Both the database administrator and the automatic tuning feature
Explanation: Even with automatic tuning enabled, the performance of the database should still be periodically reviewed by the database administrator. Automatic tuning reduces the load but does not eliminate the need for monitoring.
True or False: Automatic tuning configuration changes require a database restart.
- True
- False
Answer: False.
Explanation: In Azure SQL, automatic tuning configuration changes don’t require a database restart – they are applied automatically as the workload runs.
True or False: The automatic tuning recommendations can be applied manually.
- True
- False
Answer: True.
Explanation: Users can choose to apply the recommendations manually. Under ‘Recommendation history’, they can review the automatic tuning recommendations and choose to apply them manually.
Which of the following is NOT a recommendation type made by Azure SQL Database Automatic tuning?
- a) Create Index
- b) Drop Index
- c) Modify Data
- d) Force Plan
Answer: c) Modify Data
Explanation: Modify Data is not a recommendation type. The available recommendations are ‘Create Index’, ‘Drop Index’, and ‘Force Plan’.
True or False: Indexes dropped by auto-tuning in Azure SQL Database can be reinstated.
- True
- False
Answer: True.
Explanation: Dropped indexes can be reinstated if it turns out they were beneficial to the database’s performance. Azure SQL Database retains the scripts to recreate any dropped indexes.
Interview Questions
What is Azure SQL Database Automatic Tuning?
Azure SQL Database Automatic Tuning is a feature that is designed to automate the process of performance tuning for databases. This feature enables the system to automatically manage indexes and applies tuning recommendations by analyzing the usage patterns and workload of a database.
How can the Automatic Tuning mode be configured in Azure SQL Database?
The Automatic Tuning mode can be configured at different levels such as at the server level or at an individual database level, using the Azure portal, Transact-SQL (T-SQL), or Azure PowerShell.
What are the three options available for configuring automatic tuning?
The three options are “Inherit from parent”, “Auto”, and “Off”. Inherit from parent applies the tuning options selected at a higher level. Auto applies the Azure SQL automatic tuning features. Off disables automatic tuning.
What are the benefits of enabling Force Last Good Plan option in Automatic tuning?
When enabled, the Force Last Good Plan option ensures that Azure SQL Database reverts to the last known good execution plan if performance drops significantly due to plan regression.
What is Query Performance Insight in the context of Azure SQL Database?
Query Performance Insight is a feature in Azure SQL Database that provides insights into performance on a per-query basis. This allows administrators to identify costly queries, analyze their execution plans, and apply the necessary optimizations.
What does Automatic Index Management do?
Automatic Index Management identifies indexes that should be added or dropped based on your workload and automatically applies these changes during maintenance windows.
What permissions are required to manage automatic tuning options?
To manage automatic tuning, a user must have the SQL Database contributor role or equivalent permissions.
Can you oversee the actions executed by automatic tuning in Azure SQL?
Yes, Azure SQL provides detailed logs and reports of automatic tuning actions which you can review using Query Store reports or an Extended Events session.
How does Automatic Tuning handle plan regressions?
Automatic Tuning detects plan regressions by monitoring the performance of individual queries over time. If a new execution plan performs worse than a previous one, the feature reverts to the last known good execution plan.
How long does automatic tuning keep the tuning history?
Automatic tuning keeps the tuning history for at least 30 days.
Can the Force Plan feature be disabled in Azure SQL Database?
Yes, the Force Plan feature can be disabled at either the database level or the server level.
What’s the impact of tuning options on databases that do not belong to an elastic pool in Azure SQL Database?
For databases not in an elastic pool, tuning recommendations are computed and applied individually for each database.
How does Automatic Index Management deal with dropped indexes?
Azure SQL Automatic Index Management first marks an unused index as INVISIBLE for a period of time and monitors its usage. If the index continues to be unused, it is then dropped.
Can you manually accept or reject the recommendations made by Automatic Tuning?
Yes, recommendations made by Automatic Tuning can be manually accepted or rejected depending on performance monitoring and user discretion.
What is the command to enable automatic tuning on a database through T-SQL?
The command to enable automatic tuning on a database through T-SQL is:
ALTER DATABASE [YourDatabaseName] SET AUTOMATIC_TUNING = AUTO.
extutorials.com