Automate performance tuning and patching
In this we will learn about how to automate performance tuning and patching.
Automatic tuning in Azure SQL Database and Azure SQL Managed Instance
Azure SQL Database and Azure SQL Managed Instance automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning. However, Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database. Further, this is achieved through dynamically adapting database to the changing workloads and applying tuning recommendations. Automatic tuning learns horizontally from all databases on Azure through AI and it dynamically improves its tuning actions.
What can automatic tuning do for you
- Firstly, automate performance tuning of databases
- Secondly, automate verification of performance gains
- Thirdly, automate rollback and self-correction
- Then, tuning history
- Tuning action Transact-SQL (T-SQL) scripts for manual deployments
- After that, proactive workload performance monitoring
- Lastly, to scale out capability on hundreds of thousands of databases
Safe, Reliable, and Proven
Tuning operations applied to databases in Azure SQL Database are fully safe for the performance of your most intense workloads. The system has been designed with care not to interfere with the user workloads. Automated tuning recommendations are applied only at the times of a low utilization. Further, the system can also temporarily disable automatic tuning operations to protect the workload performance. In such case, “Disabled by the system” message will be shown in Azure portal. Automatic tuning regards workloads with the highest resource priority.
However, Automatic tuning mechanisms are mature and have been perfected on several million databases running on Azure. Automated tuning operations applied are verified automatically to ensure there is a positive improvement to the workload performance. Regressed performance recommendations are dynamically detected and promptly reverted. Through the tuning history recorded, there exists a clear trace of tuning improvements made to each database in Azure SQL Database and Azure SQL Managed Instance.
Automatic tuning for SQL Database
Automatic tuning for Azure SQL Database uses the CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN database advisor recommendations to optimize your database performance. Here, you can either manually apply tuning recommendations using the Azure portal or you can let automatic tuning autonomously apply tuning recommendations for you. The benefits of letting the system autonomously apply tuning recommendations for you is that it automatically validates there exists a positive gain to the workload performance, and if there is no significant performance improvement detected, it will automatically revert the tuning recommendation.
However, in case you are applying tuning recommendations through T-SQL, the automatic performance validation, and reversal mechanisms are not available. Recommendations applied in such way will remain active and shown in the list of tuning recommendations for 24-48 hrs. before the system automatically withdraws them. If you would like to remove a recommendation sooner, you can discard it from Azure portal.
Automated Patching for SQL Server on Azure virtual machines (Resource Manager)
Automated Patching establishes a maintenance window for an Azure virtual machine running SQL Server. However, automated Updates can only be installed during this maintenance window. For SQL Server, this restriction ensures that system updates and any associated restarts occur at the best possible time for the database.
Configure in the Azure portal
You can use the Azure portal to configure Automated Patching during provisioning or for existing VMs.
New VMs
- Firstly, use the Azure portal to configure Automated Patching when you create a new SQL Server virtual machine in the Resource Manager deployment model.
- Secondly, in the SQL Server settings tab, select Change configuration under Automated patching.
Existing VMs
For existing SQL Server virtual machines,
- Firstly, open your SQL virtual machines resource and select Patching under Settings.
- And, when you’re finished, click the OK button on the bottom of the SQL Server configuration blade to save your changes.
However, if you are enabling Automated Patching for the first time, Azure configures the SQL Server IaaS Agent in the background. During this time, the Azure portal might not show that Automated Patching is configured. Wait several minutes for the agent to be installed and configured. After that the Azure portal reflects the new settings.
Reference: Microsoft Documentation, Documentation 2