Azure Services for Monitoring and tuning Azure SQL Database

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

**Azure SQL Database Monitoring Services**

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.

Diagnostics settings

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)

Learning Resources

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/ View all posts by Mustafa EL-Masry