Pass Your Microsoft Azure Database DP-300 Exam Easy!

Microsoft DP-300 Administering Microsoft Azure SQL Solutions exam dumps vce, practice test questions, study guide & video training course to study and pass quickly and easily. Microsoft DP-300 Administering Microsoft Azure SQL Solutions exam dumps & practice test questions and answers. You need avanset vce exam simulator in order to study the Microsoft Azure Database DP-300 certification exam dumps & Microsoft Azure Database DP-300 practice test questions in vce format.

Optimize Query Performance

7. 59. identify problem areas in execution plans

In this video, we're going to identify problem areas in execution plans. So we're going to start off with this. And we saw that there were problems initially if you didn't have an index. But there's another problem. Now I'm using Select Star. Is that really necessary? Do you really need all of the columns? Can you narrow it down? If so, then you probably have a better chance of using indexes. So here we're using an index scan, but it'd be much better if you could use an index sink, for instance. So if you get a scan when you're using a wire, then you may require an index. If you don't have any indexes and are using a heap, you might need a cluster index. Maybe it's the problem with thewhere could it be sagible. So SARG basically means, can you use an index? For instance, let's have a look at this select star. We have here a modified date. Suppose my wire was where the year of modifieddate is going through a function now equals 2006. I can't use a function more easily than an index. In terms of seeking, it's having to scan. Now, to be honest, Select Star will probably be using a clustered index (Scannis as a clustered index) because you're having to retrieve all of the fields. What if we change it to "select city"? Well, you can see that we are still using an index, and in this case, it's still a clustered index. Okay, so what happens if we create a nonclustered index using only the modified date and city? So in other words, it's exactly the index that we want. So, name of index. So I'll call it IX address, modified date on sales address, and in the brackets say modified date and city. So this gives us exactly the index that would be useful if we were looking at Modified Date and City. Hopefully, it will now perform a seek. It will seek out those particular roles. When we look at it, we see that it is still an index scan because the year function is not sagable. Now, what I can do instead is modify this. So it says where to modify the date between 2006 101 and 2000 and 612 31 and all the rest. So one second before 2007. If I look at that and go to the execution plan, then you can see it is now an index seat using this non-clustered index. And the Sikh, because it only goes to a specific set of rows, is much faster than a scan, which has to go through the entire input or table. Now, let's have another example. Maybe I was looking at the beginning of line one. So let's have a look at selecting this for address line one. The first character is equal to eight. So if I did that again, I'd be looking at a scam. Instead of saying where address line one is like 8 percent, so any number of characters after eight, I can now use an index seek; similarly, I could say is null or two words instead of using the function is no one word. Now, have we got a key lock up?If so, could we use "include" with the index the word include?So I've created an index, but I could have later included and a specific set of columns that I didn't want to be included in the index but written in a separate part of the key away from the index, so I don't have to go into the table; it's much faster, but it doesn't slow down the index as much. Are the field types too wide? So we previously looked and discovered that we had an envel chart of 60. So, do we actually require an invalchar 60? Let's see what the length of address one from the sales list address is, and I'm going to order by this field, and you can see we've got 39 as the maximum, so do we really need 60? Maybe we can narrow it down to 50. If field types are too wide, then it will increase the raw size, so it's taking more time to retrieve the data. Do you have a sort? So let's say I wanted to order by this modified date. Well, sorting can be quite expensive; do you really need it? If so, do you have an index that is already sorted on this column? Do you have a store procedure in place while using parameters? Assume this was a stolen procedure with parameters or the correct procedure. The procedure had a name and certain parameters, so yeah. For instance, if you get this and its performance is not that good, Then maybe you can use the words "recompile," so if I did this, then it would force the procedure to look at the parameter and not use a cached version of the execution plan each time it would recompile.Now, I can do this for individual queries by selecting "recompile" at the end, but you should only do so if your statistics are so dissimilar. For instance, maybe you have a million rows where the modified date is 2006 but you've only got 100 for 2007. We've had a look at loops. Are you using a hash join when, with some changes, maybe an index, you could be using a merged join or nested loop, and are you using a cursor? So in other words, there are certain operations where you go through and take one route through time, then the next, and the next. If so, you might want to look at SET-based operations instead I rarely use cursors; sometimes when the computer gets overwhelmed, that's when I use them, but quite often, if I've got the choice, I will use a set-based operation instead. So, these are some problem areas in executionplans select stars that have scans when they seek to be better with index sort parameters. You could use the hint recompile are using hash joinswhere you can improve that and are using cursors. So those are some problem areas for execution plans.

Evaluate performance improvements

1. 63, 68. identify and implement index changes for queries

In this video, we're going to take a look at identifying and implementing index changes for queries, along with assessing the design of the index for performance. So we've had a look at what indexes are in previous videos, so they stop us from having to go through the entirety of a table and allow us to seek a particular point. So what are the requirements for indexes? Well, first of all, a big table If you've got a small table, you probably don't need an index. Small tables, even if you provide an index of major shoes anyway, should have a small column size. So the best are things like numerics, but if you've got others like smaller text sizes, then that could be good as well. So having an index on envarchar 60 is probably not that good. You should use columns that are in the where clauses, and they need to be sagable. In other words, when you use functions like year and left and other things, they simply do not work with indexes with sags, whereas when you use other things, they are saga. So less than greater than, and so on. So, if you're using alike, this is a perfectly well-formed stagable where clause, right where he is at the start. However, if I said where he is anywhere, well, I can't use an index for that because I would have to go through all of the items in the index anyway. So how do you create an index? Well, in TSQL, you would say create, and then you would either have a non-clustered or a clustered index named index. So I usually put IX underscore and then the table, then underscore, and then the individual columns that he was indexing because the clustered or nonclustered index name needs to be unique. So create clustered or non-classed I'll talk about the differences in just a moment. Index the name of the index on, followed by the name of the table, and then the columns in brackets. Now what is a clustered index as opposed to a non-clustered index? Well, you're only allowed one clustered index per table. It's frequently used with primary keys. When you create a primary key, you also create an automatically clustered index. It reorders the table based on the index. So a heap is a table without a clustered index. Once you have a clustered index, it gets sorted. So you should use it for frequently used queries and range queries between x and y clustered indexes when creating primary keys using the unique clustered index. So that means that there can only be one particular raw with each value. If you're using multiple columns and there's only one particular raw for the combination of the values, it's possible to create a non-unique clustered index. Most of the time, it will be unique. So these are things that should be accessed sequentially in ranges. It's quite good for identity columns. As a result, identity columns are data columns that are generated automatically. So it starts off "one," "two," "three." In other words, it's sequential numbering. You don't actually specify what the computer does. and clustered indexes are frequently used. Because you can't sort a table in two different ways at the same time, you can only have one clustered index per table. You can have as many nonplus indexes as you want. It creates a separate index. But be warned, if you insert a row, update a row, delete a row, or merge data sets together, then all of the indexes will need to be adjusted. So if you've got too many indexes, that could be slowing down your machine. Now, you don't have to index the entire table. Suppose you have a frequently used query with a very specific place where the city is equal to awful. Well, you could have an index that looks at just that particular where clause. That's called a filtered index. Now let's imagine a hypothetical index. So I'll create this index, and it'll have references to all of these particular rules. And this happens on a particular page. And then there's a new page that has everything else, and then another page that has everything else as well. So each of these is on a separate but linked page. And there's also a hierarchy that says, "Go to this page if you want one to eight; this for nine to 17; this for 18 to 25." And this is all that we can contain on a particular page in this example. Now, what happens if I insert row number 16? Well, I need to put it in here because an index needs to be in the right order, but I haven't got any room. So what I need to do is break the page and create a separate page. There are maybe 16 goals on this page and 17 goals on the next page. It doesn't then redo everything. It just creates a new page. Now, suppose you didn't want your index to use up all of the available space because you knew that you were going to be adding additional information. So at the time of creating the index, you wanted it to say, "Just allow five for each particular page." So it could have eight in terms of capacity. But you're just saying, "Actually, all I want is five." Well, you can do that with something called the fill factor. So I can say wave fill factor equals and in this caseit would be five divided by eight or 62 or so. Now, if I have this index and raw 15 comes along, then good news! Here's my page. It's only got five items on it. I can just insert it; no need for the page to be split, which obviously will take some time. And finally, we can say that it is going to be sorted, ascending or descending, just like with an order in which the default is ascending. If you happen to be using a particular query—lots of queries where it's descending—then you might want to create the index that way. So this is how we can create an index in TSQL. You can't do this with the Azure SQL Database, but in other types of databases, you could go to the indexes section of SSMS, right-click, and select New Index to see a visual representation. As you can see, it just gives you an index template for the Azure SQL Database. So if I connect to a different database, I'm going to connect to my local database. So, for example, on an Azure Virtual Machine, go to a specific table, right-click on Indexes, and select New Index. Here we can see we can create clustered indexes and non-clustered indexes and so forth and actually have a dialogue box to do that, but that is not available in Azure SQL Database. Just a quick note about the Column Store. Now, we won't go into too much detail about Column Store, but it is the traditional way of storing data. So, like, this is called the raw store. So we have each row contained within a set unit. In SQL Server 2012, they introduced Column Store, and it wasn't that good in terms of the number of situations when you could use it in 2012, but it got a lot better in 2014 and then got even better in 2016. So what Column Store does is store each column separately, and then you have to get it together at the end. So for instance, just like you can get ranges of rows together, it's the same way to be able to get columns together. So the advantage of this is that if you've got a huge amount of data, your data warehouse that we're talking about, wouldn't it be a lot easier to just say, "Okay, give me all of the roles with this particular city and what a Column Store table does, and therefore what a column star index does?" It concentrates on a particular city or anything else on a page and compresses it down. So it says this is City 112-3456, and it has a list of all of the meanings of what city one, two, and so forth mean in the page header. We'll be looking at Column Store later when we talk about the use of compression for tables and indexes. But if you see what Column Store is, it's not the standard type of index; it's an index on a different type of table. Column store indexes, on the other hand, are generally available in most Azure Database tiers. Use createclustered, nonclustered, or unique clustered when creating a table. You could have unique nonclustered if you want index name ofthe index on name of table and then in brackets thecolumns, you could have a filtered index if user where clothesand you can have spaces in the index. So it takes more pages, but there's less on each page if you use the fill factor, and that's expressed as a percentage, except you don't use a percentage sign, so it goes from one to 10. And finally, if you no longer needan index, you can always drop it. If I write and click on an index, then go to script index as drop, you can see that it's a very simpledrop index, a native index on the table's name. So it's the first part, but just not using the words unique, nonclustered, or clustered.

2. 61. DMVs which gather query performance information

In this video, we're going to have a look at how we can use DMV's dynamic management views to gather query performance information and identify performance issues generally. So you can see on screen a variety of DMVs. So what are DMVs? Well, dynamic management views are system views. That's it. They begin with Sys Dmundagement. Then there's a word that represents the functional area, such as Exec, DB, or tran, followed by an underscore and the actual view. Now, if you wish to do the DP 300 certification, you will need to memorise a fair number of these in terms of what they can be used for. You don't necessarily need to know the exact columns or the exact output, but you need to know roughly what they are used for. So in this video, we're going to take a few of these and have a look at what you can do with them. So here we've got some rather scary-looking looking card.Don't worry about it, it's just code that you can pull in on the internet. But the important thing is what's in green at the particular DMV. Now, before I do anything with this, I'm going to run this query in a separate window. All it is is this table, which is 450 rows multiplied by 450 rows multiplied by 450 rows. So you can see, it's going to take a long time. So I'm just going to leave that running. So let's have a look at the first of our DMVs. And we've got DM-exec cached plans. So this can retrieve the last execution plans, which are in the cache. And so you can see, we have things like the plan handle and other information. Now, this plan handle is used in two separate DMVs, dmxec SQL text and dmxec query plan stats. So if I use this with a cross application, you don't need to worry about why it's a cross application. The reason for that is because we have a different plan handle for each one of these rows. So it's not a left join or right join or inner join; it's an apply. What we have is the text from the query and also the plan in XML format. Now you'll see that it's underlined. So, if I click on any of these plans, you'll see that we have a similar type of execution plan that we've seen before. So these three work hand in hand with each other. So we have the actual plans, and then we extract the SQL text and also the query plan statistics. This next one is about having a look at the top end. So the top five queries in this case are ranked by average CPU computer time. So as you can see, these are our biggest users of memory. And surprise, if I just copy and paste that, you can see that our biggest use of memory is one that we've used from a previous video, which is a cross drawing. Now it's very similar to the one we're using here, except this is even more extreme, and that will be number one there. So you can see which queries are running the longest, and from that you'll be able to go, "Well, is there a reason for it?" Could the query be rewritten? Could I add some indexes in addition that use the most cumulative CPU? So this particular statement may take the most CPU for each individual query. But maybe I ran that once, and I ran this 100 times. Well, that will take longer in total. So let's give it a shot. and you can see the various plan handles. And here is the text over here. You can see the total workout time in Plumber. So I might have run one particular thing several times, or it could be that the server did so as well. So that uses DM Exec query stats and DM Exec SQL text. So, when we were looking at the cache plans, we saw that DM Execute SQL text. so it can be used quite frequently. So this consumes the most total CPU, followed by the longest running queries that consume CPU that are still running. So you notice that this one is still executing and still running. So if I run this, then you will not be too surprised to hear that the one with the most CPU that is still running is this one over here. But let's have a look at it. We have the statement text. I could copy that and retrieve the text. We have the CPU time in milliseconds. It's taken a lot longer now, but this is probably the latest time. It updated the session. ID. Now you notice the Session ID is in brackets here, as is the start time. So if your computer is slowing down right now, then have a look at the DM Exec requests. And again, this ties in with the DM Exec SQL text. So in this video, we've had a look at how you can use some of the DMVs to gather query performance information. In the next video, we'll look at how I provide you with information about all of these DMVs and sample output, and how you can hopefully use it in your studies.

3. 61, 62. determine the appropriate DMVs to gather performance information

In the previous video, we had to look at how we can use some of these DMVs to gather query performance information. In this video, we're going to look at these DMVs in turn, and you can see that there are two different resources in the Reason All section near the beginning of this course that may help you. The first is this document, which I provided to you as a PDF in the Resources section, which contains a categorised list of DMVs that you should sort of memorise and know what they are for. And secondly, I have provided you with sample output of each of these DMVs as well. So what we're going to do in this video is have a look at this sample output and just talk generally about these DMVs. So for the exam, you will not be required to be able to do something as complicated as this. You just need to be able to recognise a particular DMV. So DmxE returns a role for each query plan cached by SQL Server. Why does it do that? It's because you need the cache plans for faster query execution. If I run a query once and then again, I don't want to have to figure out how to use it again. Now, this plan handle allows me to use the DM ExecSQL text, which returns the text of the SQL batch, and it also allows me to use the DM Exec query plan stats, which allow me to get planned statistics. As you can see, they are in XML format. I have abbreviated what's there so you can see the type of information that was found. DM Execute Query Stats is one of those DMs while you've got queries with CPU times, so it returns aggregate performance statistics for cached query plans in SQL Server. So you'll notice that there is a pull handle. So, again, you can use it in conjunction with Dmxec SQL Text Inquiry planStats wherever you see that plan handle. So you can see things like the last execution time of a particular cached query plan, how many times it was executed, how many roles it returns, and the TotalLast Min and Max for various categories. I should point out that when a plan is removed from the cache, the corresponding roles are removed. From this view, Dmxec Procedure Stats does exactly the same thing, but for cashed-stored procedures. So you can see that we have the total last minimum and maximum of certain items. So for both of these views, they contain one raw per query statement within the cache plan, or one raw for each cache stored procedure plan, and the lifetime of the roles is tied to the plan itself or the stored procedure remaining cached. Now, the SysdmExec request returns information about each request that is executing right now in SQL Server. So we still have that really long query happening. So, if I go to Sys DM, exact requests, and score requests, we can see that we have session 90 and it is still running. so much information about that. So total elapsed time, for instance, was 936 seconds. Isolation level: lots of details, which you might just go into and go, "Oh, that should be that for that particular query." So, as I said previously, the session ID is this number up at the top. If you want the current session ID, then you can do that by selecting at speed. So my current connection is 70, which is this item up there. Now. The next DMV is SysDMV: Understand Connections. So that returns information about the connections established in this instance of SQL Server and the details of each connection. So for SQL Server, it returns serverwide connections, and for SQL Database, it contains the current database connection information. So you can see I'm connected by a named pipe with a net packet size of 800. This is interesting information if you need to know it. So these are the current active sessions right now. Now we turn to data and log input output I O usage. Under statistics, we found SIS DM underscoreDB, underscore resource. So this returns CPU, IO, and memory consumption for an Azure SQL database. have a database or a managed instance. Now, one raw exists for every 15 seconds, even if there's been no activity, and historical data is maintained for approximately an hour. So you can see average CPU percentages, data percentages, IR percentages, memory usage percentages, maximum worker percentages, and maximum session percentages. So session means number of people connected, worker means number of requests, and it's a percentage of the limit of the database service tier. You can only handle a maximum. I've got the basic database running, so the maximum is not going to be that high. Now, resource statistics This returns CPU usage and storage data for an Azure SQL database. Now, this data is collected and analysed within five minute intervals. So in other words, we have all this information, say from 02:00 p.m. to 2:00 p.m. or 05:00 p.m. to 05:00 p.m., and have then aggregated some average maximum together. So for each user database, there is one row for every five-minute reporting window. The information returned includes CPU usage, storage size changes, and database SKU changes. SKU SKU. In other words, what size are you giving the database in terms of performance and that sort of thing? So if you've got an ideal database and no changes, you may not have any roles for every five minutes, and historical data is retained for approximately every 14 days or so total. Now, just one thing about it: If I were to go into my Azure SQL Database and try and get this information and run execute, it says, "I have no idea what you're talking about." You must in the Azure SQL Database be inthe Master database to be able to run this. Similar DMVs exist for the managed instance; this is Sys Server Resource, and there is one for all elastic pools in an SQL Database server. We touched on elastic pools, but you know that we have single database resource stats. We have an elastic pool. Elastic pool, resource statistics Finally, there are Dmtran active transactions. So the Tran is showing transactions, and this returns information about transactions for this instance of SQL Server. So these are some of the DMVs that you should become acquainted with and be aware of. Oh, yes, I know roughly what all of this is talking about. So you don't need to know more than that. You just need to know. Okay, I'm giving you this particular scenario. What's the answer? And this is why one of the practise tests is dedicated to the DMV. But the good news is, when you do this practise test, you can also have my resources open, and you can actually use it as a learning tool that goes up to you. This is actually this. So these are and V's for gathering performance issues and querying performance data.

4. 64. recommend query construct modifications based on resource usage

In a previous video, we ran this query. So we created the sales order detailcopy and sales order header copy tables. But the important thing about these is that they've got no indexes whatsoever. I created this query and ran it, and you can see it uses table scans because there's an old where clause. So you'll be using a scan, and it uses a hash match to create the results. Okay, so far, so good. Now the computer is not complaining about the lack of indexes. and there's a reason for this. If you have a look at how many rows we've got, we've only got 32 rows, so really, it doesn't make that much difference. So when would it use the indexes? It is used when obtaining the join here between the sales order IDs. Now what if this wasn't 32 rows? What if this had a lot more rows? So let's double the number of rows. So I'm inserting into this table the table,so that double the number of rows. So now there are 64 rows. So if I execute this, we're still fine, but let's execute this a few more times. 128, 256, 512, so now up to 1024 rows. So let's execute this. Look at the execution plan. We're still fine. Now let's do it a few more times. So 20, 48, 40, 96, 81, and 92, taking a bit longer, and 16,384. So now let's execute our original query. And if we now have a look at the execution plan, you can see that there is a missing index and a suggestion of what we create. Create a non-clustered index, add a name, and it gives us what we should be having. So the sales order ID and the customer ID are an include.So the include columns would be in a separate part. So in other words, you would have the main indexand then when you get to individual rows, there wouldthen be a link to this separate part. So having the ability to include columns is far more efficient than having only one column and then having to get the information elsewhere. So if I click on the dot, you can see this is the query text, and if I write and click, we can see missing index details, and there is my code that I would need. So let's run that code and have a look at this query again. And now we can see it uses an index scan, non-clustered over this sales order header copyright lawn.Let's just drop that index or get rid of the words. Create an unclustered list temporarily. So just drop this index. I didn't actually give it a proper name, I'm just using the default name. So now it's dropped. I'll undo that. So it's still dropped. I just did the typing. So I'll run this again, and we'll have that problem again. So this is one way of detecting missing indexes. But is there a better way to do it for an entire database? And the solution is to use a DMV, which is sys DM underscore DB underscore missingindexdetails plural. So if we have a look at this because we're missing it in this current index, it comes up. So it's in database ID five, with object ID 133-010-3779. And you can see that we have a sales order ID equality column, no inequality columns, and then some included columns. What are equality and inequality? Well, if we have a look at this index, it doesn't actually give us any clues whatsoever. So we go back to the query, and you can see that the query has this equality. So there you have it: equality. So, if we have not equal to, greater than, or anything other than equality somewhere, such as in the where clause, that is in the inequality. When you are creating an index, you put equality first and then the inequality columns; both of these should be in the key, and then you've got the included columns in the include section of the index. Now you can go through like this and work out what you need to do and why you need to do it, and so forth. So you can see that in this particular table, it says that "Equality" and "Equality Not Included" are columns that are available on the internet and in the PDF that's attached as a resource much earlier on in this course. Examples of the full things that you can do with these sorts of things So this is a much wider query and it uses three different DMVs, but it gives basically the same results. It's just that you now have the correct index statement there, so you just need to copy and paste it. So there's my entire index statement, including a good name for the index. Going further on, we've got things like average user impact, so that's the percentage-average benefit that user queries could experience if this missing index group was implemented. You've got average total user costs, so that's the average cost that could be reduced by the index. So you have to go OK, this might be a big saving—98%—but if we're saving not that much, is it really worth it? But this is when a query like this could help because it's actually ordered by those indexes that are most beneficial. So a quick look at what all of these different DMVs are So they all start with a missing index in Dmdb. So details returns details groups that return information about missing indexes from a specific index group. And then we have group stats. So this is summary information about groups of missing index, and you've also got the Dmdb missing index group statistics query, which returns information about queries that are missing in index. So this is probably the biggest query construction modification I can think of that actually creates indexes when they are needed. Of course, don't create too many because as soon as you insert additional information, update, delete, or merge, the indexes need to be updated, so that could grind your system to a halt in terms of actual changes that could be needed to the query. While we've already gone through everything that we need to be sagable, we need to ensure, for instance, that we're not using functions when we can avoid them. So don't use the year function when you can use between two sets of dates, don't use left when you can use like, and don't use the is null function. So, if a certain field is null, give it to me unless you have a specific need for it; alternatively, you could say if my field is no or equal to whatever. Yes, it adds a bit more to the words, but it makes it actually solgo, which means that you can use any particular index that is available. So you don't necessarily need to know all of the specifics of how to change a query to make an asaga or query; you just need to know, for instance, that you shouldn't use functions when you can avoid it if there is a better alternative that can use an index. So the greatest thing you can do to speed up your queries is to have appropriate indexes, and you can do that by using Sysdm, DB, Missing Index Details, and similar DMVs.

Go to testing centre with ease on our mind when you use Microsoft Azure Database DP-300 vce exam dumps, practice test questions and answers. Microsoft DP-300 Administering Microsoft Azure SQL Solutions certification practice test questions and answers, study guide, exam dumps and video training course in vce format to help you study with ease. Prepare with confidence and study using Microsoft Azure Database DP-300 exam dumps & practice test questions and answers vce from ExamCollection.