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.

Comments

Ishita Jack said…
Excellent article and with lots of information. I really learned a lot here. Do share more like this.
Salesforce Training in Chennai
Salesforce Training Online
Salesforce Institute in Bangalore
Ishita Jack said…
This comment has been removed by the author.
EZaccounting said…

Thanks for sharing a good article with us. It is very helpful to us, if you want to know more about accounting software our website is helpful to you...
Accounting Software Singapore
PSG Grant Accounting Software
E invoicing Singapore
Allena Potter said…
Microsoft has leveraged its constantly-expanding worldwide network of data centers to create Azure, a cloud platform for building, deploying, and managing services and applications, anywhere. Azure lets you add cloud capabilities to your existing network through its platform as a service (PaaS) model, or entrust Microsoft with all of your computing and network needs with Infrastructure as a Service (IaaS).

Either for fixing deadlocks or Microsoft Azure Cloud Monitoring, I only trust SafeAeon Cloud Monitoring Services
Shiva Shakthi said…
Great blog! This is really helpful for my reference. Do share more such posts and keep us updated. Looking forward to more informative blogs from you.
Cloud Computing Training in Chennai
Cloud Computing Online Course
Cloud Computing Training in Coimbatore