Alerting for Deadlocks in SQL Azure and Monitoring using Azure SQL Analytics

Hi Mates,

I always remember my childhood where my parents used to tell me it is only up to 17 years we need to study and thereafter we can enjoy. However after stepping into the IT industry I realized this is not true at all .

After the cloud expanded her wings at a rapid pace we just can't refrain ourselves. 

Coming back to the topic although this is not too technical I just want to share how we can generate alerts for common scenarios like Deadlocks for Azure SQL Database.

I don't have a subscription to carry out testing even though I am very eager; however we can do a few things with a free subscription.

We just need to select alerts section under Azure database 

Click on Alerts and select new alert  we nearly have 33 signals and out of which I selected Deadlocks as shown below.

 

upon selecting we need to specify the condition just like performance counter alert in perfmon. We do have static and Dynamic alerting mechanism. 

A Dynamic threshold uses advanced machine learning to automatically determine the threshold value using the metric's historical values and the sensitivity has been classified in to High, Medium and Low.

For a counter like Deadlocks we can go with Static and set the threshold value accordingly.

Post that under Action group we need to select notification type where I opted for Email and passed my Gmail details(For Testing)

After completing the above information finally under Alert rule details select the severity based on your requirement. This is how it looks finally

Now we can generate a deadlock scenario and can verify if we are getting Alerts.

In my previous Article I explained how can we achieve Monitoring using Free tools.

https://www.chiranjeevivamsydba.com/2021/01/make-use-of-free-monitoring-tools-for.html 

This time I wanted to test native method of Azure SQL Analytics and it has some cool features where we just need to create 

1) Log Analytics workspace

2)Diagnostic setting

3) Azure SQL Analytics

I could have explained  this however it has already been clearly projected in the below links

https://eitanblumin.com/2020/03/17/azure-sql-analytics-monitoring/

https://www.sqlshack.com/getting-started-with-azure-sql-analytics/

For testing this time I created a Blocking scenario and this is how it looks for my workspace.

 

I believe if we make use of Azure SQL Analytics and Free monitoring tools together then we can have everything in place.