AZ-304 Microsoft Azure Architect Design – Design a Monitoring Strategy for the Data Platform

1. Data Monitoring Strategy

So let’s look at the ways that you can do various monitoring within your databases. When we were running SQL Server on our local environments within our data centers, we certainly knew that there were places to look, little hidden corners of SQL Server to find out the performance aspects of our databases. Some of those management scripts and things still work within SQL Database. But Azure gives us the ability to monitor the performance using the Azure Portal in a way that we weren’t able to in a traditional SQL Server environment. So this is a database that I created, a SQL Database.

I just created it a little while ago and I started running some queries against it. And we can see as we go down into the metrics tab under Monitoring, we can see the CPU usage on the DTU. Percentage is also an option within that tab, and we can see how close we are to exceeding or hitting our limits on our database plan. So there’s an overview of the performance in the form of being able to create these charts right within the Azure Portal.

Azure provides a number of reporting tools on the left here. So if we look under Intelligent Performance for this database, there’s a Performance Overview tab, there are performance recommendations that Azure will make in order to improve performance. And there’s this section called Query Performance Insight, and this gets down to the query level and will tell me which queries are consuming the most of my resources. Now in this particular case, the queries I’m running is just a select statement. It isn’t even using 5% of the DTUs that I have allocated.

But if I had a lot of joins and I had a lot of expensive queries, table scans and things like that, I might find that there are a small handful of two or three queries that are using up most of my performance. And so we go into Query Insight here to see which queries are the individual ones causing the problems. We can also see long running queries as well. Now SQL Database Advisor within the SQL Advisor tab that will make database recommendations. And so I filtered this down only to the database settings, and I happen to be following all the recommendations. But Azure and SQL Database will make recommendations for improving performance.

You can set a number of automatic tuning elements. And so at the server level, there’s the defaults. And in each individual database you can override the defaults, whether you want to force a performance plan onto a query, whether you want to create new indexes to improve the select performance, whether you want to drop indexes that are not useful in order to improve the insert performance and things like that. We also saw there’s this thing called Intelligent Insights, and the interesting thing about that is that Azure will analyze the log files and will take a look at events that are happening. Now, maybe the query is being blocked.

One query is long running and it happens to be blocking another. What they call artificial intelligence detects and analyzes these issues. If one query is blocking another and will basically in the diagnostics log will make a recommendation to say, hey, this particular query is using a lot of locks and it’s preventing a lot of other queries. So there’s probably a problem here. So there’s some artificial intelligent ways of looking at our database to see what’s causing performance issues. Speaking of diagnostic logs, not only the intelligent insights can go into a diagnostic log, we can go into the Azure database, into the diagnostic settings, and turn on the collection of diagnostics among a number of elements. You can turn on errors, timeouts blocks, deadlocks wait times, query store weights, database weights, et cetera, depending on how deep you want to get into it. In this case, I turned on SQL Insights,

I turned on Tuning, and I want to collect the basic metrics, and I didn’t turn on the other ones. Now, that database log goes into Log Analytics, and so there’s a tool in the marketplace that’s in preview mode called Azure SQL Analytics, and it will take the log files from Log Analytics and produce that as an element within Azure Monitor. So if we switch over to Azure Monitor I’ve got the SQL Analytics installed in Azure Monitor. Then I can go into the SQL database and I can see now, again, this is a very simple database with some sample data. But in a more complicated database we can see query, runtime statistics, query SQL insights, database await statistics, et cetera. We can sort of get some better intelligence in terms of what’s going on at our database.

• Category: Uncategorized