DP-300 Microsoft Azure Database – Evaluate performance improvements

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

In this video we’re going to have a look at identifying and implementing index changes for queries together with assessing index design 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 or 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, may just use a scan anyway. You 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 an Nvarchar 60 probably not that good. You should use columns which are in the where clauses and they need to be sagable. So in other words, we’ve had a look at when you’re using functions like year and left and other things, they just don’t work with indexes with sags, whereas if you used other things those are sagable. So less than greater than that sort of thing. So if you’re using a like then this is a perfectly well formed Sargable where clause. So wear hai is at the beginning. However, if I said where hai is anywhere, well, I can’t use an index for that because I would have to go through all of the items of 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.

So name of index so I usually put IX underscore and then the table and then underscore and then the individual columns that he was indexing because the clustered or nonclassed index name needs to be unique. So create clustered or nonclustered I’ll talk about the differences in just a moment. Index the name of the index on and then the name of the table and then in brackets, that’s where you put the columns. 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 automatically a clustered index. It resorts the table based on the index. So a heap is a table without a clustered index. Once you have a clustered index, it gets resorted. So you should use it for frequently used queries and range queries. So between x and y, clustered indexes, when created with primary keys, use 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 it’s only one particular raw for the combination of the values, it is possible to create a nonunique 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. So identity columns are automatically created data columns. So it starts off one, two, three. In other words, it’s a sequential numbering. You don’t actually specify, the computer does. And clustered indexes are frequently used. So, whereas clustered indexes resorts a table, and you can only have one per table, because you can’t sort a table two different ways at the same time.

You can have as many nonclassed indexes as you want. It creates a separate index. But beyond, if you insert a row, update a row, delete a row, 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 where, a frequently used query with a very specific where. So where the city is equal to offer. 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’s got references to all of these particular rows. And this happens on a particular page. And then there’s a new page which has everything else, and then another page which has everything else as well. So each of these are on separate but linked pages. And there’s also a hierarchy which 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 raw 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, maybe 16 goals on this page, 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 with fill factor equals, and in this case it would be five divided by eight or 62 or so. 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 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 by.

So the default is ascending. If you happen to be using a particular query, lots of queries why it’s descending, then you might want to create the index that way. So this is how we can create an index in TSQL. Now you can’t do this for the Azure SQL Database, but in other types of databases you could in SSMS go to the indexes part, right and click and go New Index and have a visual way of doing that. 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 this could be like an SQL Server on Azure Virtual Machine and go to a particular table and right and click on Indexes and go to New Index.

Here we can see we can create clustered indexes and non clustered indexes and so forth and actually have a dialog box to do that, but that is not available in Azure SQL Database. Just a quick note about Column store. Now we won’t be talking too much about Column Store, but just to let you know, the traditional way of having data stored. So like this is called Raw Store. So we have each raw contained within a set unit. In SQL Server 2012, they introduced Column Store and it wasn’t that good in terms of number of situations when you can 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 it stores each column separately and then you have to get it together at the end.

So for instance, just like you get ranges of rows together, well, it’s the same way of being able to get columns together. So the advantage of this, if you’ve got a huge amount of data, your data warehouse we’re talking about, wouldn’t it be a lot easier to be able to just say, okay, give me all of the roles with this particular city and what a Column Store table does, and therefore a Column Store index does. Is it concentrates on a particular city or anything else in a page and it 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 and two and so forth are 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. However, Column Store indexes are generally available in more status for Azure Database. So creating a table use create clustered or nonclustered or unique clustered. You could have unique nonclustered if you want index, name of the index on name of table and then in brackets the columns. You could have a filtered index if you use the where clause. And you can have spaces in the index.

So it takes more pages, but there’s less on each page if you use the with fill factor and that is 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 need an index, you can always drop it. If I write and click on an index and go to script index as drop, you can see it’s a very simple drop index. Name of index on the name of the table. So it’s the first part, but just not using the words unique, non, clustered or clustered.

• Category: Uncategorized