DP-300 Microsoft Azure Database – Implement performance-related maintenance tasks

1. 43, 50. implement index maintenance tasks

Hello. And in this section, we’re going to be implementing performance related maintenance tasks. I’m going to start with indexes. So we’re going to be looking at index maintenance tasks and assessing the growth and fragmentation of a particular index. So first of all, we’ve had a look before about how to find missing indexes. We’ve had a look at the Sys Dmdb missing index details. So that tells me that there is a particular table which could do wave these indexes with these fields being included in an index. So we’ve had to look at that in an earlier video. Now we’ve also had a look at how indexes can be split. So let’s say we have this as a page of an index.

So we have 12346, and now I want to insert five in that. But this is as many items as you can get on a particular page. So what might happen to happen is I then move six onto another page, maybe I’ll move four and five onto there as well, and then I have this page. So now I’ve got two pages in this index. Now let’s suppose instead of starting with 12345, I start off with eleven, 2233-4455. And now I’m going to be inserting some additional items. Let’s insert 1234. Well, maybe I’ll put them on a later page, and then I’ll be linking this page backwards to that page. So this is the first page, this is the second page. This is how fragmentation can work, where you have an overall item which says, okay, go to page two first and then to page one. This is the order.

So fragmentation, it happens when you’re splitting pages, but then you’re getting pages in the wrong order in terms of how it can be read. So what is the fragmentation of particular database indexes? Well, we can use this to show us. So why should I care about whether there is fragmentation? Well, it can degrade query performance because there’s more input output requests with smaller numbers of data in each request, and each page can be fragmented up to 100%. So I have got this Sys Dmdb index, physical stats that can give us that information. Now, if I have a look at that in the other way around, you can see it gives things like average fragmentation in percentage, the object ID. So you can look that up using object underscore ID, the index ID, but you can also see which object it relates to. So this can let you know what the fragmentation is like.

Now, you can also use the DBCC command show contig. However, this has been deprecated and we don’t recommend that you use it anymore. Incidentally, if you’ve got column store indexes, then you should be using Sys Dmdb column Store Raw group physical stats. So they both end with physical stats. And the main thing that you should note, I haven’t got any column store indexes is the number of deleted rows and the total rows because when you’ve got that information, you can divide one by the other to say, okay, this is how fragmented it is.

When you update a row in a column store, what happens is it deletes or marks the original raw for deletion and then creates a new row. So that’s why using deleted rows and total rows is quite useful. So we found that there’s an index you need to update. What should you use? Well, you should use the Altar index. Now we have got two main options, reorganize and rebuild.

And it used to be that rebuild was great if you could spare time with that index not working. However, nowadays you have a we’ve a bit that says online equals on, so you can do it online. When should you reorganize and when should you rebuild? There’s no hard and fast rules. If you look on the internet, you’ll probably see that reorganized should be done if you’ve got fragmentation of between ten and 30% and reboad above 30%.

And if you’ve got column store indexes, then you should reorganize if you’re above 20% and it removes the roles marked as deleted. However, there’s no hard and faster rule, those are just guidelines. So here is the reorganized section. So alter index all, or you can name a particular index on and then the name of the table reorganize. So you can see it didn’t take that long, but then it wasn’t a particularly big index. Now we can use rebuild and we’ve got a few options with rebuild. So we can say with online equals on or online equals off fill factor. What does that mean? Well, let’s say we have got a page which allows us to have five roles in it. In reality it’s 8192 characters, but let’s say it’s five rolls. The fill factor is a percentage of how much it’s going to be filled up.

So if I had this to start with, and I only had a 60% fill factor, then if I want to insert two and four, I can do that within the existing page. So fool factor allows us to have a bit of expansion, but then it does create a bigger index because we are having blank spades max duration. Well, this is how long you can do the index for the rebuilding. It may be you can only do 30 minutes. So that’s how you can do it. Now, this can be used with SQL Server 2017 or later, and with Azure SQL Database, and then after those 30 minutes, well, it pauses. Now you can say resumable equals on, we’ll make it resumable. So you can say auto index, name of index, and then add where that index is and you can say pause or bought or resume. So I won’t be needing to do this for this particular rebuild, as you can see, very quick, but that’s what you can use.

So indexes, we’ve previously gone through missing indexes, so well worth revisiting that section. If this is new to you, you can also look at the fragmentation of an index by using Dmdb index physical stats. So you could say where average fragmentation in percentage is of a certain amount, or even you can order by it descending. So that gives you the biggest at the top. So it’s given me an ambiguous column. It’s because I’ve got this star at the end, so that should work now.

So here you can see the biggest average fragmentation, but there are some where the page count is so small. If it’s a small index, does it really matter about high fragmentation? After all, it’s not going to slow down the machine that much. So I would say multiplied by the page count to have the page count included in the calculation. So here I’ll be looking. Okay, here is one particular index that could well do with being either rebuilt or being reorganized. And as a rule of thumb, you may wish to reorganize when you are between ten and 30% fragmented and rebuild if you are more than 30% fragmented. And if it’s resumable, you can pause or bore the index if it’s starting to interfere with other users being able to use your database.

2. 44. implement statistics maintenance tasks

In this video we’re going to talk about statistics maintenance tasks. What are statistics? Well, they’re used to create query plans to improve the speed of queries. The statistics contain information about the distribution of values and tables or indexed views columns. So maybe it says if this value is one, then there are a million rows. If this value is two, then there are 100 rows. It uses it to estimate the cardinality, let’s say, the number of roles in a result which enables query optimizer to create better plans. So in other words, it could use seek rather than scan if an index would be of use. Now, usually the query optimizer determines when statistics might be out of date and then it updates them.

However, you might want to manually update them if query execution times are slower, you are inserting on ascending or descending key columns such as Identity or Timestamp columns, and after maintenance operations such as a bulk insert that’s to say you’re inserting a million or 2 million or a billion rows at once. You don’t need to do it after rebuilding or reorganizing an index, as you’re not actually changing what roles are there, so the statistics don’t change. So there are two things to know. First of all, we’ve got exec SP underscore Update Stats. So this storage procedure updates statistics for all user defined and internal tables, all of them.

But if you just want to update one particular index, then you can use update stats and the name of table, name of the index. So I’m updating this particular one. So there are a few options you can use. First of all, you can use with full scan, so this scans all of the roles, or you can just get the computer to scan a sample. So you could have say we sample 10% or we sample 20 roles. You can also say with resample, which is its most recent sample rate.

Now with these first two, you can also have a comma and then persist underscore sample underscore percentage goes on. So whatever options you have chosen will be the default for future statistics updates. However, I’ve not actually been able to get that to work with Azure SQL database, so I suspect it’s not available in this particular database. But it’s useful to know that it is an option. So this is how you can implement statistics maintenance tasks. You can either have the still procedure SP underscore Update Stats to do them all, or you can have one particular index updated with Update Statistics, name a table, name of index, and then with full scan with sample 10% or we’ve resampled.

3. 45, 82. configure database auto-tuning and automate performance tuning

Now in this video I want to talk about database Auto tuning. Auto tuning is a process which learns about your workload and identifies potential issues and improvement using the philosophy Learn, Adapt, Verify and Repeat. So configuring data auto tuning is very easy. We just go down to the intelligent performance and automatic tuning. Now you will see that there are three options force Plan, Create Index and Drop Index. These are the three which are there in Azure SQL database. However, in SQL managed instance, there is only Force plan. The other two do not currently exist. So let’s talk about Force plan. So this is also called force. Underscore last underscore. Good. Underscore plan. Force last. Good planner. Now it is enabled by default.

So you can see the current state is on inherited from the server. So if we were to go into the server for this Azure SQL database and go down to the automatic tuning, you can see we have got On, off or inherit. So it’s currently inherited from the Azure defaults which are on for this. So what does force last good plan mean? It means that if something has gone wrong with a plan, if there has been some plan change regression found. So in other words, where something is no longer as good, then the Last Good plan should be used, should be forced. So that’s force plan.

So that’s roughly when the estimated gain is above 10 seconds, or the number of errors in the new plan is above the recommended plan. So that’s Force plan. Also known as Force last good plan. Now for your SQL database only we can automate Index maintenance as well. So we can change, create index and drop index we can have those away from inherit. So it inherited from the server. The server inherits it from Azure and change them to on. So you can see the default in Azure is that they are off for both of these. So if I do this in the database, this is overriding the settings from the server. So note that the Drop Index option is not currently compatible with partition, switching and index hints. So if you use those, then you can’t use Drop Index. So indexes will be auto created.

If the CPU data I O and Log I O is lower than 80% and it thinks it’s a good idea, the performance of queries using the autocrated indexes will be reviewed and if it doesn’t improve performance, then they’ll be automatically dropped. So let’s have a look at a few TSQL commands. First of all, Sys Indexes shows all of the indexes in a particular database. But over near the right hand side we have got Autocreated. There it is. So where Autocrated is one which is not the case for any of these, then these will be Indexes which were created using Autocreation.

So needless to say, if I run this now, there won’t be any. So you can tell which indexes are created from Sys indexes. Now, if I wanted to do what I’ve just done in the portal, I could do it like this. So auto database name of database name. So I’ll put DP 300 set automatic tuning equals auto. I could also say inherit or I could have a custom. So if I say auto, there we go. If I want a custom, then I can have in brackets false last good plan equals create index equals drop index equals but again, you won’t be able to use these last two. In SQL managed instance they aren’t there. So now I’ve done that. If I go back into the portal and refresh, you can see that it now shows the desired state.

And then finally, what are the tuning recommendations, if any? You can find those in Dmdb tuning recommendations if you want to go underneath the hood and see what is actually happening. So configuring database auto tuning. So you can do this quite easily in the Azure Portal by going to automatic tuning and you can also do it in TSQL by using Alter database database name set automatic underscore tuning and you can also see which indexes are auto created by going assist indexes and looking at the Altor underscore created column.

• Category: Uncategorized