1 00:00:00,090 --> 00:00:02,930 How do we measure query performance? 2 00:00:02,930 --> 00:00:05,510 Well, in this lesson, we're going to find out. 3 00:00:05,510 --> 00:00:08,410 So what we're going to be discussing is we're going to take 4 00:00:08,410 --> 00:00:10,480 a walk through the Synapse Query Store. 5 00:00:10,480 --> 00:00:13,120 And if you don't know what that is, don't worry, 6 00:00:13,120 --> 00:00:15,110 we will be discussing that. 7 00:00:15,110 --> 00:00:17,420 We're also going to talk about some common scenarios 8 00:00:17,420 --> 00:00:20,990 for the Query Store and honestly, just some common scenarios 9 00:00:20,990 --> 00:00:23,390 for why you would measure queries. 10 00:00:23,390 --> 00:00:25,390 And then, that's not all, 11 00:00:25,390 --> 00:00:27,160 we're also going to talk a little bit about 12 00:00:27,160 --> 00:00:28,500 dynamic management views, 13 00:00:28,500 --> 00:00:31,660 log analytics, and Azure Data Explorer. 14 00:00:31,660 --> 00:00:34,013 So with all of that, let's get started. 15 00:00:35,370 --> 00:00:39,310 So first, let's take a walk through the Synapse Query Store. 16 00:00:39,310 --> 00:00:41,550 What is the Query Store? 17 00:00:41,550 --> 00:00:43,720 Well, it's something that provides insight 18 00:00:43,720 --> 00:00:46,670 on your query plan choice and your performance. 19 00:00:46,670 --> 00:00:49,610 And before we go on, let's step back just a second. 20 00:00:49,610 --> 00:00:53,120 When we talk about queries for the DP-203, 21 00:00:53,120 --> 00:00:55,650 we are talking about Synapse Analytics. 22 00:00:55,650 --> 00:00:58,320 So make sure you keep that service in mind. 23 00:00:58,320 --> 00:01:00,460 And the query tore is just something that helps us 24 00:01:00,460 --> 00:01:03,270 to get insight on the queries that we're running. 25 00:01:03,270 --> 00:01:05,750 So the Query Store captures your queries 26 00:01:05,750 --> 00:01:08,310 and your plans and your run time statistics. 27 00:01:08,310 --> 00:01:10,230 And then from that, you can take a look 28 00:01:10,230 --> 00:01:14,600 at time sections or windows, and you can get information 29 00:01:14,600 --> 00:01:17,860 on the queries or the plans or the run time statistics. 30 00:01:17,860 --> 00:01:20,010 So it's a very helpful tool set. 31 00:01:20,010 --> 00:01:23,150 However, you need to make sure that you turn it on. 32 00:01:23,150 --> 00:01:25,120 And you do that with that simple snippet there, 33 00:01:25,120 --> 00:01:26,530 you're just going to alter database, 34 00:01:26,530 --> 00:01:30,690 put your database in, and then set Query Store to on. 35 00:01:30,690 --> 00:01:32,220 So make sure that you turn it on, 36 00:01:32,220 --> 00:01:35,190 because it is not on by default. 37 00:01:35,190 --> 00:01:36,550 And there's 3 different types 38 00:01:36,550 --> 00:01:39,530 of stores, or kind of mini stores. 39 00:01:39,530 --> 00:01:41,670 So think about a store within a store, 40 00:01:41,670 --> 00:01:42,840 because that's kind of what is. 41 00:01:42,840 --> 00:01:46,650 So within the Query Store, there's 3 little subsets. 42 00:01:46,650 --> 00:01:48,400 There is the plan store, 43 00:01:48,400 --> 00:01:52,320 the runtime stats store, and the wait stats store. 44 00:01:52,320 --> 00:01:57,320 So the plan store is just for execution plan information. 45 00:01:57,950 --> 00:01:59,060 The runtime stats 46 00:01:59,060 --> 00:02:02,090 is going to be execution statistics information. 47 00:02:02,090 --> 00:02:04,730 And then the wait stats is just going to give you 48 00:02:04,730 --> 00:02:06,770 information about your wait times. 49 00:02:06,770 --> 00:02:07,710 That's pretty much it. 50 00:02:07,710 --> 00:02:10,110 So those are the 3 individual components 51 00:02:10,110 --> 00:02:13,240 that make up the larger Query Store. 52 00:02:13,240 --> 00:02:16,140 So now let's talk about some common scenarios 53 00:02:16,140 --> 00:02:17,820 for using the Query Store, 54 00:02:17,820 --> 00:02:20,460 or honestly for monitoring your queries. 55 00:02:20,460 --> 00:02:22,950 The first is an audit history of queries. 56 00:02:22,950 --> 00:02:25,140 So you might have regulatory requirements 57 00:02:25,140 --> 00:02:26,950 or other things that you need to use. 58 00:02:26,950 --> 00:02:29,640 You need to be able to audit your history. 59 00:02:29,640 --> 00:02:32,530 Next, you want to identify top performing queries, 60 00:02:32,530 --> 00:02:34,880 or poorly performing queries. 61 00:02:34,880 --> 00:02:36,300 This is a great way to do that. 62 00:02:36,300 --> 00:02:38,260 So you can kind of take a look at what's going on 63 00:02:38,260 --> 00:02:41,480 with your queries and figure out for your databases 64 00:02:41,480 --> 00:02:45,520 and your data in general, how accessible is that data? 65 00:02:45,520 --> 00:02:49,270 So do we need to look at optimizing our queries, 66 00:02:49,270 --> 00:02:51,250 building different partitions? 67 00:02:51,250 --> 00:02:52,083 Do we need to look 68 00:02:52,083 --> 00:02:55,070 at changing our database structure around? 69 00:02:55,070 --> 00:02:58,640 This can give us insights into some of that. 70 00:02:58,640 --> 00:03:01,600 Also we can determine how often a query executes. 71 00:03:01,600 --> 00:03:03,330 So in addition to understanding 72 00:03:03,330 --> 00:03:05,860 the top performing and poorly performing queries, 73 00:03:05,860 --> 00:03:08,220 we can take a look for how often it's run. 74 00:03:08,220 --> 00:03:10,910 So for instance, if we have a very poor performing query 75 00:03:10,910 --> 00:03:13,170 but we run it once a quarter, eh, 76 00:03:13,170 --> 00:03:14,930 maybe we don't care all that much. 77 00:03:14,930 --> 00:03:16,240 If we run it every day, 78 00:03:16,240 --> 00:03:18,190 then we probably care quite a bit more. 79 00:03:19,120 --> 00:03:22,210 We can also analyze resource usage patterns. 80 00:03:22,210 --> 00:03:26,130 So this is going to help us both in a cost savings 81 00:03:26,130 --> 00:03:28,280 perspective. And it's also going to help us to understand 82 00:03:28,280 --> 00:03:29,633 where there's bottlenecks. 83 00:03:31,190 --> 00:03:34,100 And identify queries waiting on resources. 84 00:03:34,100 --> 00:03:37,070 So there may be queries that are waiting on resources 85 00:03:37,070 --> 00:03:39,040 that we need to bump up those resources 86 00:03:39,040 --> 00:03:42,120 or we need to allocate when queries are being run 87 00:03:42,120 --> 00:03:44,670 to more efficiently utilize the compute 88 00:03:44,670 --> 00:03:47,903 and the the resources that are available to us. 89 00:03:49,450 --> 00:03:53,070 Now, Query Store is not our only solution. 90 00:03:53,070 --> 00:03:54,500 That's 1 of our solutions. 91 00:03:54,500 --> 00:03:56,580 And the good thing about the Query Store, 92 00:03:56,580 --> 00:03:58,190 at least from Microsoft's perspective, 93 00:03:58,190 --> 00:04:00,920 is they say that it stores 30 days of data. 94 00:04:00,920 --> 00:04:03,950 My question is, but is that good? 95 00:04:03,950 --> 00:04:05,710 It really depends upon you. 96 00:04:05,710 --> 00:04:08,030 And as we look at the 4 solutions, 97 00:04:08,030 --> 00:04:10,740 you need to keep in mind that all of this 98 00:04:10,740 --> 00:04:14,950 is going to be very dependent upon your specific scenario 99 00:04:14,950 --> 00:04:16,860 and which one's the best for you. 100 00:04:16,860 --> 00:04:19,150 So 30 days of data might be fantastic 101 00:04:19,150 --> 00:04:21,503 or it might not be nearly enough. 102 00:04:22,360 --> 00:04:27,360 The bad for that is it is limited analysis compared to DMVs. 103 00:04:28,060 --> 00:04:30,000 And we're going to talk about that next, 104 00:04:30,000 --> 00:04:32,430 but you can't get the depth that you can get 105 00:04:32,430 --> 00:04:36,670 off of dynamic management views or DMVs. 106 00:04:36,670 --> 00:04:38,080 So with DMVs, 107 00:04:38,080 --> 00:04:41,840 you have an extremely extensive option for analysis. 108 00:04:41,840 --> 00:04:44,407 And before I jump any further into the good 109 00:04:44,407 --> 00:04:47,460 and the bad of dynamic management views, 110 00:04:47,460 --> 00:04:50,900 you might not be familiar with DMVs. 111 00:04:50,900 --> 00:04:54,810 Again, this is something that is run in SQL servers, 112 00:04:54,810 --> 00:04:57,360 or Power BI, or there's several different places 113 00:04:57,360 --> 00:04:59,820 that you can use dynamic management views, 114 00:04:59,820 --> 00:05:03,160 but basically it's a query that gives you information 115 00:05:03,160 --> 00:05:08,070 about model objects, or server operations, or server health. 116 00:05:08,070 --> 00:05:10,760 So you can run queries to give you information 117 00:05:10,760 --> 00:05:13,200 about what's going on in your environment, 118 00:05:13,200 --> 00:05:15,930 and that is a dynamic management view. 119 00:05:15,930 --> 00:05:18,650 So there are a ton of different queries that you can run 120 00:05:18,650 --> 00:05:21,910 to get information about just about anything that you want. 121 00:05:21,910 --> 00:05:23,940 The bad for this is you are limited 122 00:05:23,940 --> 00:05:26,700 to the last 10,000 queries. 123 00:05:26,700 --> 00:05:28,370 Now this may seem like a ton, 124 00:05:28,370 --> 00:05:32,260 but it really depends again on your specific scenario. 125 00:05:32,260 --> 00:05:35,910 And dynamic management views are not going to be as easy 126 00:05:35,910 --> 00:05:37,980 for large scale operations. 127 00:05:37,980 --> 00:05:40,430 If you're using large scale operations, 128 00:05:40,430 --> 00:05:43,410 you're going to have an easier time using Log Analytics 129 00:05:43,410 --> 00:05:46,160 or Azure Data Explorer. 130 00:05:46,160 --> 00:05:50,050 Another alternative of course is going to be Log Analytics. 131 00:05:50,050 --> 00:05:53,000 This is fantastic for log retention. 132 00:05:53,000 --> 00:05:55,450 Hey, you want to look at 7 years of data? 133 00:05:55,450 --> 00:05:56,283 You can do that, 134 00:05:56,283 --> 00:05:59,440 because you can pull that information into Log Analytics, 135 00:05:59,440 --> 00:06:02,980 store it in a blob storage and pull insights 136 00:06:02,980 --> 00:06:06,430 or run queries against that for as long as you want. 137 00:06:06,430 --> 00:06:08,170 It is a little bit more costly 138 00:06:08,170 --> 00:06:11,120 and it uses the Kusto query language. 139 00:06:11,120 --> 00:06:12,920 Again, is that bad? 140 00:06:12,920 --> 00:06:15,930 It really depends on you and your team. 141 00:06:15,930 --> 00:06:18,960 If you have developers or engineers 142 00:06:18,960 --> 00:06:21,810 that understand Kusto query language, hey, 143 00:06:21,810 --> 00:06:23,430 it's not really all that bad, 144 00:06:23,430 --> 00:06:27,240 but just something to keep in mind for Log Analytics. 145 00:06:27,240 --> 00:06:30,160 And then finally we have Azure Data Explorer. 146 00:06:30,160 --> 00:06:32,770 This is one of the newer entries on the list, 147 00:06:32,770 --> 00:06:37,000 and it provides an extremely extensive option for analysis. 148 00:06:37,000 --> 00:06:39,300 So Azure Data Explorer is used 149 00:06:39,300 --> 00:06:42,120 when you want your own analytics solution 150 00:06:42,120 --> 00:06:45,350 or you want full control of your data sources. 151 00:06:45,350 --> 00:06:48,180 So it's going to give you a much deeper insight 152 00:06:49,060 --> 00:06:51,270 than some of these other solutions. 153 00:06:51,270 --> 00:06:54,500 However, it uses KQL again, the Kusto query language, 154 00:06:54,500 --> 00:06:58,180 and it is going to be a much more expensive solution 155 00:06:58,180 --> 00:07:00,190 than any of these other options. 156 00:07:00,190 --> 00:07:01,400 It really just comes down 157 00:07:01,400 --> 00:07:04,540 to what you're trying to do with your data. 158 00:07:04,540 --> 00:07:07,150 And it also depends on the rest of your environment. 159 00:07:07,150 --> 00:07:09,700 So if Azure Data Explorer is something that you need 160 00:07:09,700 --> 00:07:11,210 for quite a few different areas 161 00:07:11,210 --> 00:07:13,620 then you might want to use it here as well. 162 00:07:13,620 --> 00:07:16,830 So anyways, these are the 4 main choices that you have 163 00:07:16,830 --> 00:07:21,830 when you look at analyzing queries in Synapse or in Azure. 164 00:07:22,270 --> 00:07:25,940 So which one you use is going to be kind of dependent 165 00:07:25,940 --> 00:07:28,370 upon your individual situation. 166 00:07:28,370 --> 00:07:30,080 Couple of key points to remember. 167 00:07:30,080 --> 00:07:31,800 1. Have a plan. 168 00:07:31,800 --> 00:07:34,840 You do need to monitor queries in almost all cases 169 00:07:34,840 --> 00:07:38,000 and you want to have a plan for how you're going to do that 170 00:07:38,000 --> 00:07:39,590 and how long you need the data. 171 00:07:39,590 --> 00:07:41,430 So make sure you have the basics down 172 00:07:41,430 --> 00:07:45,420 from a business perspective as to what you need to be doing. 173 00:07:45,420 --> 00:07:47,490 If you have the Query Store, hey, 174 00:07:47,490 --> 00:07:49,120 don't forget to turn it on. 175 00:07:49,120 --> 00:07:51,580 That's also going to be true for these other things. 176 00:07:51,580 --> 00:07:53,820 Have you stood up Azure Data Explorer? 177 00:07:53,820 --> 00:07:55,970 Have you turned on Log Analytics? 178 00:07:55,970 --> 00:07:58,210 Those are the things that you need to look at 179 00:07:58,210 --> 00:08:00,660 if you are going to monitor queries. 180 00:08:00,660 --> 00:08:03,160 Finally, use the best tool for the job. 181 00:08:03,160 --> 00:08:05,610 This is a very small dive 182 00:08:05,610 --> 00:08:08,703 into some of the different solutions that you could use. 183 00:08:08,703 --> 00:08:09,740 For the DP-203, 184 00:08:09,740 --> 00:08:12,870 you need to be aware that those 4 options exist 185 00:08:12,870 --> 00:08:17,380 and there is a path to monitoring queries in Azure. 186 00:08:17,380 --> 00:08:18,720 Hope this has been helpful. 187 00:08:18,720 --> 00:08:20,320 I'll see you in the next lesson.