DP-300 Microsoft Azure Database – Evaluate performance improvements Part 2

2. 61. DMVs which gather query performance information

In this video we’re going to have a look at how we can use DMVs dynamic management views to gather queer performance information and performance issues generally. So you can see on the screen a variety of DMVs. So what are DMVs? Well, dynamic management views are system views. That’s it. They start off with Sys dot, DM underscore. So Dynamic Management underscore.

Then we have a word which is the functional area like exec and DB and tran and then an underscore and what the actual view is. Now, if you wish to do the DP 300 certification, you will need to memorize 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 code. Don’t worry about it, it’s just code that you can pull in on the internet. But the important thing is what is in green 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 has 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 got things like the plan handle and other information.

Now this plan handle is used in two separate DMVs, DM Exec SQL text and DM Exec query plan stats. So if I use this using a cross apply, you don’t need to worry why it’s a cross apply. 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 can see that we’ve got a similar sort of execution plan that we have seen many a time. So these three work hand in hand together with each other. So we have the actual plans and then we extract the SQL text and also the query plan stats. This next one is about having a look at the top end.

So the top five queries in this case ranked by average CPU computer time. So you can see these are our biggest users of memory. And surprise, 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 join.

Now it’s very similar to the one we’re using here, except this is even more extreme and no doubt 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 which use the most cumulative CPU? So this particular statement may take the highest 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 run this one and you can see the various plan handles and here are the text over here and you can see the total workload time in Plummeted. 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 we’ve seen that DM Exec SQL text earlier when we were looking at the cached plans, so it can be used quite frequently. So this uses the most cumulative CPU and then finally the longest running queries that consume CPU that are still running.

So you notice that this one is still executing, still running. So if I run this then you will not be too surprised to hear that the one with the most CPU which is still running is this one over here. But let’s have a look at it. We have the statement text so 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’s updated the session. ID. Now you notice the Session ID is in brackets here and 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’re going to have a look at how I provide you information of all of these DMVs and sample output and how hopefully you can use it in your studying.

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 Resource 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 contain a categorized list of DMVs that you should sort of memorize 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 recognize a particular DMV. So DM Exec Cached plans returns a role for each query plan that is cached by SQL Server. Why does it do that? It’s because you need cache plans for faster query execution.

If I run a query once and then run it a second time, then I don’t want to have to work out again how to use it. Now, this plan handle allows me to use the DM Exec SQL 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 plan statistics. As you can see, they are in XML format. I have abbreviated what’s there so you can see the type of information which is found.

DM Exec 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 plan handle. So again you can use it with conjunction with DM exact SQL text and query plan stats 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 rows it returns, and the Total Last Min and max for various categories.

I should point out that when a plan is removed from the cache, the corresponding rows are removed. From this view, DM Exec procedure stance does exactly the same thing, but for cached stored procedures. So you notice again we’ve got the total Last Min and Max of certain items. So for both of these views, they contain one row per query statement within the cached plan, or one row for each cache stored procedure plan and the lifetime of the rows are tied to the plan itself or the stored procedure remaining cached.

Now, the Sysdm Exec requests 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 select Star from Sysdm exec requests. Underscore requests, we will see that we have session 90, and it is still going. So information about that. So total elapsed time, for instance, 936 seconds, isolation level, lots of details which you might just go into and go, oh, that shouldn’t 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 select at speed. So my current connection is 70, which is this item up there. Now, the next DMV is sys DM underscore execunderscoreconnections.

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 server wide connection info, and for SQL Database, it contains the current database connection information.

So you can see I’m connecting by a named pipe with a net packet size of 800. So interesting information if you need to know it. So these are current active sessions right now. Now we turn to data and log input output I O usage. And we’ve got sys DM underscore DB, underscore resource, underscore stats. So this returns CPU, I O 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 things like average CPU percent, data, I O percent, memory usage percent, maximum worker percent, maximum session percent. So session means number of people connected, worker means number of requests, and it’s a percentage of the limit of the databases serviced 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 Stats. This returns CPU usage and storage data for an Azure SQL database. Now, this data is collected and aggregated within five minute intervals. So in other words, we have all this information, say, from 02:00 P. m. To two, or 05:00 P. m. To zero 05:00. P. m. , and then aggregated some averaged max together. So for each user database, there is one raw for every five minute reporting window. The data returned includes CPU usage, storage size change, and database SKU modification. 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 idle database and no changes, you may not have any roles of every five minutes, and historical data is retained for approximately every 14 days or so total. Now, just one thing about it. If I was to go in my Azure SQL Database and try and get this information and run execute, it says, no idea what you’re talking about. You must in the Azure SQL Database be in the Master database to be able to run this. Now, there are similar DMVs for the managed instance.

This is Sys Server Resource, and there’s one for all elastic pools in an SQL Database server. We haven’t retouched on elastic pools, but you know that we have a single database resource stats. We have an elastic pool. Elastic pool resource stats. And then finally we’ve got DM tran active transactions. So the Tran is sort of transactions, and this returns information about transactions for this instance of SQL Server. So these are some of the DMVs that you need to get familiar with, that you need to know.

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, this particular scenario. What’s the answer? And this is why one of the practice tests is dedicated to DMVs. But the good news is, when you do this practice test, you can also have my resources open, and you can actually use it as a learning tool to go afford it. This is actually this. So these are DMVs to gather performance issues and query performance information.

• Category: Uncategorized