When we are talking about Performance tuning and Monitoring on Azure SQL Server, we should consider some services provided on azure supporting us in this task
- Query performance insight.
- Azure SQL Performance Recommendation Services
- Azure SQL Analytics
- Azure SQL Automatic Tuning Services
- Diagnostics settings
- Learning Resources
Query performance insight
This feature available in Azure SQL Database that helps you understand the impact of queries running against your database. Query Performance Insight depends on Query Store to be able to manage the data performance, and by default Query store feature is enabled on Azure SQL Database. With using Query performance insight you will have visibility on some charts and reports like Top CPU query, TOP query by duration, top query by execution count. And you can deep dive into a single query
Azure SQL Performance Recommendation Services
Azure Feature enable by default on Azure SQL DB and it is a feature based on SQL Database Adviser and it is available on Azure SQL single DB or elastic pooled database Using this feature, we can take recommendation by Creating missing index automatically by azure and removing unused index automatically by azure, also recommendation advisor it will give you parametrize queries recommendations. For dropping unused indexes is not available for Premium and Business Critical service tiers.
Azure SQL Analytics
monitoring Solution that allows you to collect important data related to the performance of your Azure SQL Database in a single place. And It is based on Log Analytics services. Azure SQL analytics allows you to create custom rules and alerts with specific metrics. And as proactive action, you can identify issues in your database. Azure SQL Analytics depends on Diagnostics settings so you should enable the Diagnostics settings first on your Azure SQL database. Storing the data in Log Analytics while creating managing actions in Diagnostics settings. Azure SQL Analytics will provide you an amazing graphical user interface that can be used while troubleshooting any performance issue.
Azure SQL Automatic Tuning Services
This fully monitoring services from azure it is doing monitoring for SQL Server continuously to be analyzed by Built-in intelligence and generate performance recommendations. One of the most amazing features in these services that after applying the recommendation for the tuning services it will be monitored by the services and if the services find this recommendation not adding value for Azure DB performance it will be rollback automatically. all of these actions will be recorded in the logs to be able to track it. This feature is provided with full support in Azure SQL Single Database and Azure SQL Pooled Database. This feature also available on SQL Server 2017 version and above but there is a difference here in SQL Server 2017 or above Automatic tuning used to force query plan and it is depending also on query store. This means you should enable query store first on your database and keep it running for some hours to take the benefits from SQL Automatic tuning features.
Enabling this feature on SQL Server for collecting Some logs based on some matrices and sending it to (Log Analytics, Saving it on Azure Storage account, Stream it on Event Hub) then we can build our alerts on it with creating managing actions groups that we can use to send an alert by email, SMS, alert to the logic app like doing tweet on twitter, Executing Azure Runbook ) many of options we can use it on managing actions.
Finally, all of this services you can manage it using Azure portal, PowerShell, and Azure CLI. And to check the previous azure posts and articles check this link (Latest Microsoft Azure Articles and Posts)
- Query Performance Insight for Azure SQL Database
- Database Advisor performance recommendations for single and pooled databases
- Monitor Azure SQL Database using Azure SQL Analytics
- Enable automatic tuning to monitor queries and improve workload performance
- Create diagnostic setting to collect platform logs and metrics in Azure
- Overview of alerts in Microsoft Azure
- Create and manage action groups in the Azure portal