Today just a quick project I’ve been working on to do connectivity and performance monitoring of a SQL Azure database. The original goal was to record client errors for a SQL DB to help with some troubleshooting and to prove if there were actual connectivity errors or just false alarms. That lead to an overall performance monitoring tool to do basic data capture to help performance optimization of a database.
If you just want to look at the project (and my less than well documented code) check it out over at GitHub. If you want some more history of why I dug into this, keep on reading.
A few weeks back I had a customer seeing outage alerts for their SQL Azure database, but not seeing any actual reported issues. The question came down to, did they have an actual issue? If they did, what was the issue? The only way to really know from a SQL DB standpoint what the issue was from a connectivity side is to get the reported SQL client error. Things like 40613 errors (reconfigure errors when the database is moved for maintenance/scaling), or semaphore timeouts (network issues or idle timeouts) are easy to explain. You can only explain them if you know they happened though.
That lead to this quick project that every minute attempts to connect to the database and records the exception if the connection fails. Simple enough, a few lines of code later and we have an Azure Function that connects and dumps the exception to blob storage.
Now though, we have a connection hitting the database every minute and some time to burn. What else would benefit from a quick query dump on a regular schedule? If you came from the world of SQL performance, you just heard the beginnings of Profiler Traces and PSSDiag.
Initially I just added output of the DMV sys.dm_db_resource_stats. This gives you a nice history of performance data beyond the 30 days held in Azure Metrics. Plus, has the added benefit of 15 second granularity at any time frame.
From that I added on optional performance data from the PerfStats script that originally was created for PSSDiag and SQL Azure database monitoring 10+ years ago. It gets us a bunch of interesting data about currently running queries, locks, and blockers. I also added a dump of SQL Wait Stats so you can get a historical view of waits across your database.
These all translate to easier performance troubleshooting and hopefully quicker resolution of issues with your database!
My favorite part about the notebook setup is they directly render in GitHub!
All of that data sounds really exciting! At least if you are super excited about SQL performance… But getting to the data and visualizing it can become a challenge. Using Azure Data Studio though you can make Jupyter-like notebooks for SQL queries! This lets you explain your queries and store result sets in an easy to consume format. You can check out the example queries I made in the SQL Performance Serverless Queries Notebook I made to show querying the files directly from Synapse Serverless. My favorite part about the notebook setup is they directly render in GitHub!
Really this whole project just became an excuse to learn Azure Data Studio and what use cases it has compared to SQL Server Management Studio.
If you want to, feel free to follow the README on the repository and give the monitor a try. If you have any suggestions, please add them in the issues and I will get right on it!
In the future I want to add custom monitoring queries and Azure Monitor integration, but that’s a future step for a different day.