DP-203 Data Engineering on Microsoft Azure – Monitor and optimize data storage and data processing Part 2

4. Azure Monitor – Alerts

Hi and welcome back. Now in this chapter, I want to go through a special note when it comes to alerts that you can create in Azure Monitor. So if I go on to the monitoring service, so remember, Azure Monitor can be used to gather all of the metrics for the resources that, that are deployed as part of your Azure subscription. And also you have other features that is looking at your activity logs and having logs from various other resources collected in one location.

And then you can create alerts. So remember here, if you want to, let’s say, have an It administrator notified, if, let’s say, the data warehousing units on your dedicated SQL pool is reaching a particular threshold, then you can create an alert based on that. So if you want someone to be notified, you’ll actually create alerts.

Now here, let me just create a new alert rule. Let me select a resource, I’ll select my dedicate SQL pool. So here I’ll scroll down. So I’m going to choose my dedicated SQL pool. I’ll hit on done. Now let me add a condition. Now, there are various conditions here. I’m just going to choose for now the condition of connections blocked by firewall. So I’ll choose this and then I get a graph when it comes to if any connections were blocked by the firewall over the last 6 hours. Now I’ll scroll down here, let me add a threshold value of two here. In terms of the granularity, let me choose 15 minutes. And here in the frequency of evaluation, let me choose five minutes.

So now with this signal logic in place, so this logic is going to define if your alert gets triggered or not. So let’s understand what happens when you create this signal logic. Now, let’s say I created this dedicated SQL pool at 08:00 in the morning and it’s been running ever since then. Now, let’s say at 100 one, there was a connection that was blocked by the firewall.

So this is the first time that this happened. At 100 three, there was another connection that was blocked by the firewall. At 100 six, another connection was blocked by the firewall. Now, when it comes to this logic, this logic says that only ensure that an alert is triggered if the total connections that are blocked by the firewall is greater than two. So now this condition has been fulfilled because the number of block connections is greater than two. We have three over here. Now what is the impact when you have now the frequency of evaluation and the aggregation granularity also put into this picture? Will an alert be generated? What time will the alert be generated? Now here the frequency of evaluation is every five minutes. That means, let’s say that we define this alert rule at 10:00 itself. So our delegate SQL pool has been running since 08:00 in the morning and we define this rule at 10:00.

And assuming that this rule has taken effect. So the frequency of evaluation is at every five minutes. So now at ten, five will be the first evaluation. The evaluation will be done based on the metrics collected over the last 15 minutes. That means it will be from 950, in this case till 1005. Now, even though we have defined the alert at 10:00, remember, since our SQL dedicated SQL pool is running since 08:00 onwards, that means metrics are still in place. This is only an alert that we have created. So from 950 onwards the metrics are still there. So it will collect all of the block connections that have happened between 950 to 100 five. And at this point in time, we only have two in place. So the alert will not be generated at this point in time.

The next at 1010, that’s the next frequency of evaluation. If we look at the last 15 minutes now, this condition has been fulfilled. So now the alert will be generated. So this is an important concept when it comes to looking at all of these features when it comes to your metrics. Now, another point, at 1010 the alert was generated. Now, when an alert is generated, that means this is your alert, it will have a status, the status of the alert will be changed onto fire. That means the alert has been triggered. Now, let’s say that at 1011, 1012 and 1013 again there have been block connections. So now at 1015 again, an alert should be generated based on the past 15 minutes. But because your alert is already in the fired state, it will not trigger the alert. Again. This is put into place by Azure Monitor to reduce the amount of noise that gets generated. Instead of alerts being triggered over and over again, it will be triggered once. So only after the state of the alert has been changed.

So we have other states in place, there is a state of closed, there is a state of acknowledged. So only the once the state has been changed, then again after an evaluation and if there is a break in the threshold, then it will be fired again. So in this chapter, I just wanted go through some important points when it comes to the alerts that you create in the Azure Monitor service.

5. Azure Synapse – System Views

Now, in this chapter, I just want to inform you about views that are available when it comes to your dedicated SQL Pool. And these are all system based views that allow you to monitor various aspects about your workloads that are running in your dedicated SQL Pool. So for example, if you want to monitor the connections, you can just copy the statement and then run it in SQL Server Management Studio. If you want to look at the top ten queries, you can take this statement in SQL Server Management Studio as the SQL admin user, I can execute this.

So here you can see the summer time, the start time, and if you scroll on to the right, you will also see the commands that have been executed. So this is one of the statements that you can use to actually monitor the queries that are running against your dedicated SQL Pool and likewise there are many other such queries. So I will attach this link has a reference on to this chat. Just keep a note of each view name and what you can do with that particular view name because you can get a question in the exam on what is the purpose of this particular view when it comes to the dedicated SQL Pool.

6. Azure Synapse – Result set caching

In this chapter, I just want to go through the cache feature that is available in Azure Synapse so you can enable results at caching with the dedicated SQL Pool. Now this automatically caches the query results in the database. If there are queries that are executed, often the results can be be taken from the cache instead. So if you have queries that actually give you the same results inside the queries, actually fetching the data from the underlying disk that is attached onto your dedicated SQL pool, they can be taken from the cache. So the cache is faster memory, but it is limited in terms of the amount of memory. So if you have queries that actually fetch the same amount of data, those result sets can actually be stored in the cache so that the results can be taken from the cache and then given on to the user. This will be much faster.

So it improves the query performance and reduces the compute power required to process the queries. Now, when it comes to cache eviction, so you can’t have the result sets in the cache all of the time, it needs to be evicted, it needs to be removed based on some sort of policy. So in the dedicated SQL Pool, you have these policies in place, so it will be removed from the cache every 48 hours if it has not been used, or if it has been invalidated, or when the results that cache approaches the maximum size. So you have to remember that your cache has a limited size. Now, here I have the statements for implementing the cache feature. Now the first thing that we can do is to first understand if the cache feature is enabled for our databases.

So here in SQL Server Management studio, I’ll right click on my Dedicate SQL Pool. I’ll choose a new query. So let me execute this so I can see that for my dedicated SQL Pool, caching is turned off. Now next we need to enable something known as the Query Store when it comes to storing the queries and the result set. So let me first turn this on. Now next I can turn caching on, but this needs to be done from the context of the master database. So here in Object Explorer, in the Master database, I’ll right click and hit on a new query. And here I’ll execute the statement. So caching is turned on. Now I’ll go on to the tab wherein I am credited onto my new pool, dedicated SQL Pool. So here, let me now issue a statement. Select star from dBlog.

Let me execute this. So I’ve got some information and let me execute this again. So we’ve executed this twice. Now, the reason I’ve executed twice is because the first time when we run this query after enabling cache, this result set should now be stored in the cache after we execute the first query. And when we run this again, since it is giving the same result set. Now, this time, instead of actually going on to the underlying disk and getting the information, the second query will actually get the results directly from the cache. Now, let’s go on to Synapse Studio. So in Synapse Studio, I’ll go on to the monitoring section. Here, I’ll go on to SQL requests. Here. I’ll choose my dedicated SQL Pool. Let me just hide this. So here I can see my two statements from dBlog.

So this was the first statement and this was the second statement. Now, if I take this particular request ID. So here I can see there were two operations in place. So one was a partition move operation and the other was a return operation. So let me just copy this first. And here I have statements wherein you can check if a particular request is taking information from the cache or not. Here we are using a particular system view that’s DM underscore PDW underscore Exec underscore requests. Next, if I go on to my next SQL request, that’s again from dBlog, so that’s this one here, I can see that there is only one return operation in place. So there seems to be something that actually happened in the background.

So let me take this request and I’ll add it here. Now let me copy this both and let me go on to SQL Server Management Studio. Let me first execute this request. So there was no cache hit. So remember, this was the first request. So after the first request, the results were then stored in the cache. So if I now execute this statement, I can see I got a result cache hit of one. That means now this in information, this result set was actually taken from the cache. So if you want to get better results for your frequency access queries, you can actually make use of this result set caching feature.

• Category: Uncategorized