1. Data Monitoring Strategy
So, let’s take a look at how you can monitor your databases in various ways. When we were running SQL Server in 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 tools continue to function 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 in 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, 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. The reporting tools available in Azure are listed on the left. So, if we look under Intelligence Performance for this database, there’s a Performance Overviewtab with performance recommendations from Azure 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 are just select statements. It isn’t even using 5% of the GTUs that I have allocated. But if I had a lot of joins and 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, the sequel database advisor within the sequeladvisor tab will make database recommendations. And so I filtered this down only to the database settings, and I’m following other recommendations. However, Azure and SQL Database will make performance recommendations. You can set a number of automatic tuning elements.
And so, at the server level, there are 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 analyse the log files and we’ll take a look at events that are happening. Now maybe the query is being blocked. One query has been running for a long time and is currently blocking another.
What they call artificial intelligence detects and analyses these issues. If one query is preventing another from running, the diagnostics log will basically 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 are some artificially 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, timeout blocks, deadlock 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; I didn’t turn on the other ones.
Now, that database log goes into Log Analytics, and there’s a tool in the marketplace that’s in preview mode called Azure SQL Analytics, and it will take the log files from Logan Lynx and produce them as an element within Azure Monitor. So, if we switch to Azure Monitor, I’ve got SQL Analytics installed, and then I can go into the SQL Database and see. Now again, this is a very simple database with some sample data, but in a more complicated database, we can see query, we runtime statistics, query SQLInsights, database awaits statistics, et cetera. We can sort of get some better intelligence in terms of what’s going on in our database.
