OK so thanks very much everybody is great see so many people. Now I'd like you all to leave no so this is all the other sessions that happening right now, it might be there's a better session for you. Then this one or rather have 2 really enthusia stick people than 100 unhappy people so how many of you guys a DBA.
Quite a few how many developers.
Most people by people.
Couple people OK so I've color coded this based on those kind of things, so if you buy the green stuff.
If you're predominately dev the kind of weird, yellow, Brown mustard kind of color for a DBA the blue stuff OK. So I've color coded. All this for you to make it easier for you to walk out right now. This is your chance.
Almost embarrassing right OK right so let's crack on this is what we're going to be covering in this particular section.
Still, nobody's worked out OK. Alright let's move on So what we're actually going to talk about them. So we're going to talk about indexing today and the whole idea. Is this is going to be an introduction because we do only have 50 minutes.
So why did I create this session well this whole session came from?
5 or 6 words, maybe 7, I can't count so basically this was come on.
A death manager came up to me and said can't you just put an index on it, so this whole session is why you shouldn't just put an index on something so we're going to be talking about the internals of SQL server? Why you want to do certain things were going to be talking about guidelines and how you implement and manage these things so.
This this was my reaction going need a better clicker than this there, we go.
So I will say that I did create this before brexit so the bit. You're going to see next has nothing to do with my political view OK but let's pretend or not. Let's not pretend because this is actually true going to stand here.
We are on an island.
So the example, I was going to give all I'm still going to give to be honest is one of our economics. If we had an economical problem? What would we do well. One of the choices. We've got is to devalue the pound and that means that our imports. We become more expensive, but our exports would become cheaper.
Lift our economy based on.
Being able to export more information, but more information more good small services. Those kinds of things. But you have to take a wider view of what's going on so really we also need to think about our relationship with Europe. Still, not talking about Brexit. We need to be thinking about how our economy is going to be work.
Peter call me where we've got certain trade deals, etc, etc. Still, not talking about brexit OK, but then is not just Europe that we've got these relationships where it's going to be the wider world OK, so you can't just think about.
An individual query gave Britain is a query in this example. We can't just think we can just put indexes on that particular query because that's going to have knock on effects with Europe, which is our database.
With the rest of the world, which could be our instance or it could be a set of virtual machines on top of a hypervisor. So we've got to think about the impact of what we're doing if we take this one step. Further, which almost makes it think like God makes you think I've actually thought about this is that when we look into export.
This is our data set so our index makes our data set retrieval. A lot faster, but at the expense of imports. So are inserts are updates, Onard Elites.
So it's great point all these indexes on, but it's going to cause problems elsewhere does that make sense OK store not about Brexit.
So.
I can help you have a number of things. But what I'd like to talk about is DDDWID stands for data. W stands for workload and I stand for internals. I know nothing about your data. I know nothing about your workload, but I can help you with the internals, which means you can make.
About when and where to create these indices because indices is the proper plug for indexes. Thank you Microsoft. Where's the camera there, we go?
So this is me my name's Richard Douglas. I'm a principle solutions engineer at century one. Here's all my details up on the screen. Probably the most important thing is that better the bottom. A copy of this is already on slideshare.net slash SQL, rich along with a bunch of my other sessions.
So if you get nothing away from this you've got some content that you can download and have a look at.
And I will pour some photos.
Comes down, yes.
I can't but they can somewhere.
Can somebody make me louder, though they can't hear me say that?
Am I allowed to now that sounds a bit louder, OK perfect.
So this is what we're going to be talking about the prerequisites is going to be all about the internals, then we're going to be talking about the infrastructure how the architecture works going to be talking a bit about the dynamic management objects that you can use to query. The indexes and the state of them to see how they're being used things like levels of Frag.
Well then going to be moving on to some golden rules on how to design these indices and then we're going to be talking about the maintenance of these because he can't just leave them there. Things need to happen with them. Otherwise, you will see bad performance overtime.
So I'm going to stop using this because it's not working properly so in terms of prerequisites. We need to know about a data page. So you might think that SQL server tables kind of look like Excel tables. Anybody think that.
No OK people into other sessions before me, then so a data page is the lowest level of data that you're going to see inside of Microsoft. SQL server, and update pages 8 K so it can store 8192 bytes worth of information part of that is broken into a section called the page header and that's going to store.
Nation about things like the type of page. It is where the next page is where the previous page. Was and a bunch of other things, OK that's 96 bytes, leaving us with 8096.
Another section that we want to look at is this section down here and let's see if I can use the pointer at least, there, we go that area. There is called the slot array and this helps the logical order to be kept in place. Now a lot of people have the misconception. That's data pages are actually kept in a fifth.
They're not they're kept in a logical order this keeps optimizing allows it to optimize it better, so if we go through. I'm actually a Big Bang Theory fan and so I've created a couple of records. Here first ones Cooper sequins. Hofstadter, the third one is koothrappali and then for some reason we've missed 4.
Into 5.
Now, what would happen if I suddenly decided I wanted to insert value for does anybody know.
OK, So what would happen is it will try and stick it at the end.
But this lottery will keep the correct logical order so if you missed that bit of PowerPoint magic there. We go 5 look at that so I was to do that. You wouldn't guess rubbish a PowerPoint so that's how your data pages are actually kept together as a kind of very, very brief overview.
Will need to understand page is a bit more later on.
This brings us onto the index architecture, so there are a couple of different kinds of indexes and I won't have time to go through absolutely all of them because I've not even listed things like XML for example.
First, one, you going to come across is a heap and heap is exactly as it sounds. It's just a kind of mess OK. It is not sorted in anyway. If you did, have assorted table that would be a clustered index. And this has something called AB tree, which allows it to keep in the correct or.
Does mean the insert performance could be better on a heap than a clustered index sometimes OK so generally people will say have a clustered index is better for performance. It depends on what your workload is remember the whole DWI bit going to depend what this table is actually being used for?
So those are heaps dance are clustered indexes so if you think that the standard example is a phone book OK. I'm sure you've all heard this before if you think of a phone book. It's all going to be sorted in a particular way so that you can find that information.
Now the nonclustered index is a bit like the glossary at an end of a book. What will happen is. It's another materialized copy of this data, So what you'll see is a word or something like that, and then appointed back to where that data actually is now a nonclustered index can be.
On either a heap or it can be on a clustered index, OK in which case they will have different kinds of pointers.
If you have a nonclustered index on a clustered table. It will contain the clustered key so it knows where that record is if it's on a heap. It will store something called a row ID and point to that particular row on a table looks like in a page.
We also have something called columnstore indexes OK and these come in various guises. There's a clustered version and there's a nonclustered version. But what will happen is that these will work in a slightly different way different architecture. And I've got you got some screenshots of how this would work.
So if we move on to heap.
Heat can be identified in sister indexes by having an idea of zero. It will always have a zero. So when you're looking through that you will be able to find that so if anybody wants to waive Andy taking some pictures in the background. No call you will really boring OK fair enough.
So index ID zero is always going to be your hips.
Then, what's going to happen is it's going to know where the first. I am pages. I am is a special system page, which is an index allocation map and that's going to point to a number of different records inside of this heap.
Each I am will actually have pointers to other items. So you can think of this kind of scaling out way past this page here.
Or PowerPoint I should say.
This does mean it's very hard to find an individual record so if you're looking for sink. What we call Singleton Vlookups. It's going to be very hard to do it in that kind of structure, so it might not be good for select performance.
Obviously is going to depend on the number of record what you got on the page and how many pages you've got.
If we come to a clustered index.
The architecture becomes a little bit more complicated, so this is where we can start using a pointer.
This part at the bottom down here is got a couple of different names. One it can be called a leaf node OK or date pages.
So this is going to be your actual data OK. This is the table itself. But it's going to be stored in a particular order OK based on whatever your cluster key is above that you're going to have what's called a tree structure specifically in mathematics is called AB tree, although Tech.
And the idea is for each of these records. There's something called a low level, which says this is the first record on this page and then that is put in as a ID on here was a record in here, so it can then find the next page down in the tree so depending.
Data you have don't point over there. That's not gonna work. You could find that you have multiple different tiers and again, you can find these in some of the demos so this is always going to be tier. Zero no matter how many you have then it's going to tier. One Tier 2 to 3, etc, etc, etc. Based on the number of levels in the tree.
Free is always called the route.
OK.
So let's break this out into an example of something that actually means something strong use this again.
So for this case, we're going to be doing is we're going to have a table. We're going to keep it. Simple going to hold it DBO customer and this is going to have a clustered primary and it's going to be an identity attributes OK. So here's our first page. It's on file. One has page 100, and we're going to do.
White pages in just to make it easier to understand what's going on.
Now I mentioned before this low level, so that's going to be part of the first well it's going to point to the first record so here's my lowest key here my lowest key is number one the one we're starting with.
So that's going to be added up to the next tier in terms of my B tree now that's not the only thing that's going to be added in.
So if we see over here, we've got 3 different numbers. The first one, is my ID ID won the second number is my file number so we're from fire one. The last number. There is actually my page number. So 'cause we're page 100, so that makes up Mikey.
Then the next one is going to be the first one or the lowest one on that page that gives a record, etc, etc. Etc OK and then what will happen is if we have enough of these we will see another tree on top. Obviously, these all points of the various pages so at some point if I actually man.
We're going to see our route tree, which will point down to the next level ad nauseam. Until you get bored depending on the amount of data you have now we could go into the math of all this, but this is the last session of a 4 day conference, who wants to do that. I don't so we'll skip on because we only have to 50 minutes.
To the nonclustered structure now, I kinda had an argument about this in an interview once which is great 'cause. I want so I didn't take the job because I thought if you interview questions of its trains. I don't want this. The argument was what's the difference between a clustered index in a nonclustered. I survived the architecture is the same? It's
Changing really.
Becaus we've still got this be tree or his B plus tree but this last bottom level, which is the leaf node contains a pointer back.
So the actual data pages as I said, before if it's on a clustered index that will be pointing back to the cluster table if it's on a heap that will point back to the heap using a row ID, so technically it's still the same architecture. It's just pointing to something else.
So that's something that we need to think about that. If we do have a nonclustered index is going to have to contain the clustered key to point back to that table, which means if we have large clustered keys that increases the size of our non clustered key which makes it bigger, which means potentially we will fit less rose on a wreck.
Bored that means we need to have more pages to deal with all this, then you get into memory problems storage problems. CPU problems, so we want to try and keep these smaller if possible.
Now I did mention columnstore column store work slightly differently. When I explain pages. Before that was in a row store format. A case there's 2 different ways of storing the data on data pages.
Column store is slightly different so if we have our roast or version over here, so you can think of rose going into vertical fashion and a column store. You technically pivoting this and so what's going to happen is. I'm going to have a page for the first attribute another page for second attribute another page for the third another page default.
But the good thing here is that because it's all going to be the same data type. Microsoft can put a ton of different levels of compression on it. I think it's 2 or 3, which means I can get more data on a page which means that's great for scanning performance so you'll find this predominantly in things like data warehouses so.
Tastic for that kind of thing and I can't for the life of me. Remember what version they came in now because those bright lights on me.
But the later versions are much, much better if you Google with Bing because we're at Microsoft conferences. Nico and column store. He's got a huge amount of information on column store indices. More than you could probably read in a lifetime and that's not strictly true unless you get there by bus tomorrow.
Let's move on. These are some of the index options that you will want to think about when creating your indexes. Because these will change the behavior of the index or indices that you want to create so the first one, is to fill factor so as well as being a great Twitter handle it also.
But this is the amount of space that we want to use up on a data page. When we're populating. This data page. So I could say I don't want to fill it all up. I want to give it something like a value of 80. That means it's going to use 80% now. I love Microsoft bit. They help me pay my mortgage every month, so that's fantastic, but
100 mean the same thing that's really confusing OK, if you ever see zero. It means no space left if you see 100. That means I want to use 100% of the space confusing isn't it but anything else means. I'm going to be leaving some space and sometimes you will want to do that. Other times, you won't.
Pad index is a very, very similar thing, but instead of just being on the data pages. It's do. I want to leave space inside the Bee tree itself, so that's the part that comes down, so you can traverse through to find the data.
Now, Unfortunately this isn't a zero to 100. This is a yes or no thing. So you don't have as much granularity as you do with the data pages. It will default to giving you the same fill factor as the data pages to again. There are reasons why you might want to do.
You mentioned that a bit later on.
The next thing is, you can create something called a filtered index, and filtered indexes are quite cold. In some ways because what you can do is essentially put a where statement in this so I could say creates index on town blah blah blah blah where talent is not.
So any address that hasn't got a town in it. I don't care about because normally what will happen. When I create an index is it will create a row. For every record even if the attributes inside, that index return null values. I would just have noels and pointer back to the data page and the record itself.
So this can help me if I've got horrible set based things and I'm looping through. But I don't care about most of the data. I've shaved huge amounts of time of order picking systems where these picking stations were empty. They couldn't change anything else but I could put a filtered index in which meant I was scanning 10.
Set of a million rows, which when you in a horrible loop that you can't change makes a big difference. Now there are potentially other problems with filtered indexes, but Hey, Welcome to the world.
The next thing is included attributes so and included attributes is where you will have data on the lowest level of the index generally well it will always be nonclustered index that isn't inside the key so the benefit of this is if I'm returning the results.
But not all of those attributes are being searched up on so they're not in my where clause or my join clauses. I could have them as included attributes and then I don't need to go back to my base table to get the rest of that information. It can all be satisfied by that one index, we would then call it a covered.
Where we don't have all of the attributes inside an index that would be called called and non covering query OK, so ideally. We want just one hit against an index rather than having to go to the base table.
Now in some situations what will happen is the optimized will actually choose to index is from the same table, creating an intersecting join OK.
One of the other things that you can do and it's quite nice. In 2016 SP one. I think it's EU 3 and above.
They introduce data compression and that is now in the standard edition rather than just enterprise.
One got you here if I compress a table. It doesn't automatically compress the nonclustered indices on that table so if I want those compressed as well. I need to alter that and compress it. But it does mean I can potentially fit more records on the page which could mean that I'm going to scan through less record.
Which means less memory grants Lessio all of those good things?
Always, a see saw the problem with this is if it's compressed. I need to rehydrate this at some point and so that will burn some extra CPU cycles.
The next thing which is an enterprise only feature and will probably stay enterprise. Only is online. So this means that I can rebuild my indices online will be talking about fragmentation later on, so you will want to reorganize or rebuild your indices because it's become.
In the end position, you can do this online in standard. It's an offline feature, which means that it's going to lock. The table, you won't be able to access it properly.
Any questions, so far.
Doesn't voices guy talking to me? Yes.
I can't hear you at all.
So the question if I heard it correctly was is this the same basically from premises as you'll yes unless you looking at Azure. SQL data warehouse, but also the demos and things will be talking about will be exactly the same perfect so next thing I want to talk about is hidden.
So.
If I create a null attribute that means. I need to have an old bitmap inside of my page. And that can take up extra space potentially so if all my data is populated and I've made it null well nullable that means extra space is being taken up so I.
We enforce that it's not null. Ralph the null again this comes back to the data workload internals parts.
Another thing we need to think about is var char anybody know anything special about vouchers in terms of what they take up.
Yes.
I really can't hear anything? There's so much noise back it.
Yes, so the size itself what happens is it adds 2 extra bytes on this so it can be variable. So it knows where the start and end is of each of these so if we were to look more deeply into the page. It needs to understand where it starts and where it ends because it's not a set blocksize anybody.
With having one well of Archer, one.
Anybody ever declared a virtual one.
No, that's actually going to take up the one byte plus the 2 by overhead. I've seen this in production. I shouted at somebody quite loudly. This is a very bad thing because the one value. They had in there was T or F for true or false? Which is a billion, which could be a bit value.
Yeah, why take up 3 bites when I could use one bits. Thank you.
Sony 50,000,000 records in that table. Thank you very much yes.
So that can be a problem for us. Another problem is something called unique fires so if my index isn't unique and I have duplicate values. SQL server will need to add in a unique a fire so it knows which record is which.
So let's say we had an index on gender OK, you're going to have mail records are going to have female records and potentially some other records based Darden.
Other things identities people want to be associated with OK, so generally we're only going to have a few of these so it means we're going to have lots and lots. And lots of duplicates. So it would be a really bad suggestion for an index because there's not much selectivity is lots of groups also means because of these ships were going to have to insert.
Into them a unique a fire is just an int internally. So it's going to be an extra 4 bytes. Problem is at least late earlier versions of SQL server. It didn't store what the last unique and fire was so if I want to insert it off, then go all the way through find out what the last value was not one to it.
That kind of sucks a bit that's going to be a big performance drag for you.
Then the last part that we need to think about is versioning.
Anybody using 2005 and above is likely to see versioning happening inside of their SQL server. That's when it first happened anybody use DML triggers ends up.
Really, nobody.
One and a half people OK so that users versioning behind the scenes that stores urine certain out deleted values inside temped to do that, it needs to put the records inversion store, which is in Tempdb. When it puts it in version store. It updates the initial record to say. Here's my points are back to.
Stored in temp DB while we're doing this.
OK, it's the same for optimistic concurrency. It's the same for online index rebuilds. Lots of different things. Use this in the background. Things like always on availability groups use it in the background as well problem with this versioning uses a grid anybody know how big goods are.
Big horrible things links 14 or 16 bytes office on my head so that means I potentially need to leave enough space to add this in for every record that's going to be on that date page. If I'm using anything that has versioning if not, that could cause a page split if I don't have enough.
And that would be bad, so if you go and see my transaction log thing, which I think I did it pass summit 2 years ago, you will be able to see the impacts of page splits on the transaction log.
So here are a couple of demos or dynamic management objects that you will be able to use to see information about the indices that you have inside of your environment.
So the first one, is more of an administration 160 MDB Index physical stats.
What this one is going to do is help you to identify the levels of fragmentation within an index?
That's going to be the most important part of that particular one. Now I've got a demo rather than the DMV because.
That gives you an overarching naming convention for dynamic management views and dynamic management functions. This first one, is a function. You can pass through a number of different parameters based on database and a ton of other stuff. The last parameter is going to be one of 3 values.
Sample or detailed detailed is very, very detailed. It also takes a hell of a long time to run.
So you may not want to do that on very, very large indexes. But it does give you a lot more information, OK, so do you have a look at books online or whatever it's called these days and it will help you to see the differences between these 3 OK because you can see all kinds of cool stuff about ghost records and stuff as well.
Other things, which are marked for deletion, but haven't been deleted and and things like that.
Other ones that you're going to want to use in this usage sexing section are 60. MDB index usage stats. Anybody wants have a guest but what that ones like and what that does.
You really are quite today.
OK, so it's going to tell us how the indexes. We used so how many Sikhs. How many scans. How many updates. Those kind of things that is fantastic information OK. The reason for that is. We talked about imports and exports at the beginning. What happens if I'm just importing data all of the time.
Do I really need that on there or if it's helping me for updates. And things on might want to, if it's making my updates faster, but if I'm not performing any selects that need that. That could just be a massive overhead for me, so it might be. I want to disable that unless it's for a report for a CEO and then maybe I just want to have that enabled for month end or something.
I could rebuild it at the points. I'm interested in and then disable it afterwards.
The Next One is index operational stats, so this is going to give us a lot more information about my mind, going blank.
I think coach pages and the like help us understand more about what's happening. There no no no. It's Come Back to me, so this allows us to look for things like weight stats, but various different values. Sorry various different elements in the tree so I can see how many locks replaced and things like that, so that could help us to understand if we're seeing lots of blocking and.
Inside of the index trees and how they actually being used.
The last section is all about missing indexes and this is probably why all the Devs are in the room. SQL server from 2005 and above will help you to identify missing indexes inside of SQL server, yeah, happy not happy still asleep yes.
OK, maybe we should give away free coffee inside of sessions, so the idea here is this will help you to identify.
Index is that the SQL server.
Optimization engine thinks would be a better candidate for you, then the indices that are in place. Currently, all aren't in place now as you may think there are some exceptions to this.
First, one, and possibly one of the biggest is that it only stores up to 500.
Anybody got more than 100 databases on SQL server.
One person anybody got more than 200 I'm going to pick on you got.
Quite a lot OK fair enough, I will go with that I spoke to one guy this week who had 1500.
1500 databases. I have space for 500 index is how many do you think he's going to see across all of his databases?
500 does it really does depend which ones come online first and they used as to what he's going to see?
Doesn't necessarily mean it's the panacea across? Absolutely everything but it will help those ones that's great?
Also means there are a number of other things, you will see lots of versions of indices, which are very, very similar so you'll have? What's called an equality operator and inequality operator and then it will suggest included attributes we put in there and that's fantastic. I absolutely love that apart from it might give me some that I've got last name and first.
It might give me another one that's exactly the same but says last name. First name initials that I might have last name initials. Basically, the same index. But it's repeated multiple times with different kinds of operators in so I need to take this information. I need to consolidate it and then I need to act upon that I can't just take each one of these.
I mean, 'cause we go back to our import export thing, we're going to just see lots of extra overhead.
Is also something called trivial plans?
It won't suggest anything for those either OK, so that could be a big problem for us, so if I just have a single table that says selects I was going to say stop that be a bad example. But let's go with select star never do it. I just said it never do it, so let's start from address work.
Country course United Kingdom, it will never suggest that I should have an index on country.
Great thank you.
But the more complex queries it will suggest these things.
So let's move on to some of the golden rules. This actually becomes a bit of a reading test OK, you've been very, very quiet so far. I'm hoping some of you can read this would be handy for me, so this is an example of a bad index. It's a clustered index. It's got the key of surname first name and middle initial.
Based on what I've got on the screen underneath that why might this be a bad example anybody know.
Perfect you just need to put the word not in front of these things right. It's not narrow. We said before that. You want to have quite a narrow clustered key because your clustered key will be inside. All of your over the nonclustered indexes. That's going to make them wide. It's going to take up more space. That's going to be a problem.
It's not static.
From a lady I could get married. I could change my surname. In fact, if I'm a gentleman, we could get married and change my surname as well.
We've had people go into witness protection and how to change everything we've had people who have changed sex change the name because of that all kinds of things. This is a key that's going to change if it changes. I then also have to change all the nonclustered indexes because it contains is contained in my cluster key.
Problem for me, I have lots of locks when I'm doing this update is going to be bad for performance.
By Progressive I mean that this isn't in a particular format. That means I'm not going to see fragmentation is not in a kind of a linear format. So it's not sequence. So I could see lots of fragmentation because I could have Smith then Douglas then let's do a proxy joke for our.
OK does it really matter they're not going to be in the same kind of order. We're going to see fragmentation ask this data is inserted.
It's not unique. There could be lots of John Smiths and indeed. There are lots of John Smiths, so that might mean I'm going to have a unique a fire on there as well.
Fixed width versus null is going to be a day to think so. I fixed width versus var. Char null versus not null. These are going to be data. Things I can't say whether it's the best practice or not 'cause I don't know your data. But if we go through these potentially we could have a huge amount of overhead based on what we're seeing here.
What unique fire to bite variable offset plus what's happened going to happen to have through that I can speak now. Plus, the offset in the length for these plus potential notability in here that could mean that overall. I have an extra 13 bytes per row. Why haven't if I've got a billion records in here? How much X.
Quite a lot.
If I change that to an identity.
Which is using an integer that's 4 bytes bearing in mind. I'm going to have the extra 13 bytes in every nonclustered index on that table.
I did something similar fixing a problem similar to this change the gui it to A.
And integers more clustered key created the clustered key, as a an integer with an extra attribute on that table managed to save 4 gigabytes on that table.
Just quite some time ago, when storage cost a lot more than it does today also made replication have a lot faster.
So golden rules for nonclustered indexes.
Banged on about this a lot. We need to have an optimal clustered key keep it. Small make sure the nonclustered indexes smaller because of that now. Unlike the clustered index. We don't want this to be particularly narrow. We want to try and cover as we said before we want to be able to cover the queries that were trying to do so we're going to want to have.
Attributes in this not just one because why would I have an index on day created great. I can get today created fast, but surely. I need some other attributes alongside that which means I need to get data from somewhere else to do that. So I need to understand the workload of what's happening so I can create the proper indices.
We talked about the fixed width and null quite a bit and we talked about the consolidation thing when we need to create our missing indexes. It's the same with the indices. Do we have in place as well. We need to see are they really similar should we be consolidating these because a lot of these.
We used a lot and if we roll them together is only one update every time we're updating are oh OK. I don't need to insert records into 3 nonclustered indexes every time I do an insert those kinds of things.
Another thing you might want to consider is indexing the foreign key when you're joining to another table. That helps the optimizer because stats will be created on here and then more likely to be up to date.
Which means to join is going to be faster SQL server optimization engine is going to have a better understanding of what it's doing so, it's more likely to choose the correct kind of joint type.
Moving on to the importance of maintenance. So here's some more power not to say power shell is all PowerPoint wizardry for you'll so we've got our 2 data pages here. We've got date page. Let's call him one and 2.
Because it's simpler we've got our 123 and 5 scenario that we had before you'll see with the page point. The page header. There's actually point is going to and from each data page. This is called a doubly linked list help. SQL server to know where the next page is so it can scan across the bottom layer.
Now, if I was to insert value for value for didn't fit. It would need to put that somewhere else now because this lottery needs to be in a logical order. It means anything that was in there below 4 would also need to be moved so typically on a page split is going to try and move 50% of the data.
To make sure it can insert these new record so it's going to insert a new page now because I kind of lied about the PowerPoint wizardry. I'm really rubbish. He doesn't really put them in between. This isn't a physical thing. This will be a logical thing it could be on a data file somewhere else on a different drive for all I know.
It's not going to do is put it in the middle and shuffle. Everything else along because that would be completely wasteful OK. But what is going to happen is the page header on both sides is going to have to be updated so that we've got this doubly linked list that points at this new data page so not only.
Am I updating this row, but I've now got to lock this page and update this page header as well.
This means I could be slowing down other people trying to read row 8 or something like that, so it's going to be really bad for performance.
We've then got to insert our record and copy data over from there as well. So we're logging a lot of information at this point.
And we can see here that are PG arrays source lot arrays are actually being updated here.
So there are 2 main types of fragmentation and these work on both shown in the last example. Here we've got logical and external so that both called the same thing or the both the same thing basically so it's when the next logical page is not the next physical page, so that's what we saw before 'cause we inserted and.
Logically, I had to connect to it, but physically it wasn't going to be contiguous so that's 1 kind of fragmentation and that's going to cause the problems that we're seeing there in terms of if we were on older disks. We know we have to have this Cape Movement. All those kinds of things.
That's also physical or internal fragmentation and this is where we've got lots of extra space on the data page. So if we think about reading that 8 K into memory that only might contain 2 K of data but I've took up 8 K of memory, so at 6 K of wasted space.
That makes sense.
Again, what this is going to mean is a lot from the IO perspective because we're going to read more data.
More memories being used larger backups from using always on availability groups or something like that. I'm shipping more across more back in if it's a synchronous 2 phase commit going to cause me a lot of problems as a DBA.
So how do we go through fixing this couple different ways. There are some tools out there that will help you to rebuild or reorganize the indices. Now these ones here are just guidelines.
What will happen is between North and 5% Norton 10% people will generally say I couldn't care less well. I do care. But I don't necessarily need to update it all the time and so they will take no action between 5 and 30 or 10 and 30%. This is when they will typically say it's in a state will I will reorganize?
30% and above it will go through and do the rebuild in standard edition. This is going to be an offline operation.
Reorganize isn't online operation OK and I do apologize that reorganizes spelt wrong for this audience.
So reorganize won't do as good a job as a rebuild, because it uses less space to work. It all out OK. But it does mean that the entire table will be online at only certain parts. A lot to a time that well. There are 2 times when it locks entire table, but it's very, very brief.
So there are some options for this you can use maintenance plans, which are better than they used to be, but not my preferred choice. There are custom community scripts and I will shout out older having good at this point. He's wrote. Some great scripts to do index rebuilds and there are third party tools that will do that thing for you OK.
Going to vendor who does a tool that does that or may not for the camera benefit.
So maintenance best practices.
If I ever see anybody doing auto close all auto shrink. I will suggest that you update LinkedIn and I will also suggest that you don't have anything to do with data in your title. It's up very, very bad thing to do also close basically takes everything out of the cash for that database.
So very bad things, so as long as when the last user connection for that. Database is finished. Blatz all gone very bad thing auto shrinks fantastic you know what it does.
Takes all the data from the front tries to put it to the back basically it's a great big fragmentation creation machine.
How many people have got vendor documentation that says shrink your so rebuild your indexes and then shrink your data files I've had it yeah.
So I had a big argument with somebody and then sent bird document from Paul Randal, saying This is the worst thing you can do in the world ever other shoot your mother.
And then maybe depending on human life, even that. But that's going to be a really, really bad thing OK, so we don't want to do that. That's going to cause fragmentation. The only time you going to want to shrink things is manually if you're running out of space and it's the last resort to stop things from breaking. You don't want this turned on by default. We also want to update.
As part of our maintenance plans because this is going to help the optimizer to understand how much data is in there to create better plans.
When we're looking at rebuilding and reorganizing our indices. If you rebuild. It also update statistics for you, OK, so the one in the same now. If I'm rebuilding my index. It's gone through the entire table and knows exactly what's there, if I then update my statistics with sample. I've made the whole thing worse again.
So once I rebuild I don't want to update with sample 'cause. It's going to make the statistics worse and that used to be the order in which things happened for a lot of people in their maintenance plans so that was a bit of a problem. You also want to consider the kind of recovery models that you're using because this can have a big impact So what I like to do when.
Maybe I is I would put my recovery model in bulk logged mode before I rebuilt my indices and then turned it back again because it's a different way of logging that information for the environment. I had it meant I logged less data that may or may not work for you. You need to look that up, you need to test that.
So we are coming up on time so these are the things that we have covered today. We've talked about the architecture. We talked about the different options that you have for SQL server on the indexing indexing side. We talked about some of the demos. You can use to query? What's happening inside of your indices.
Look at the design in terms of creating clustered and nonclustered indices. We've talked around the fragmentation and then also spoken briefly about maintenance and what you need to be doing so, Unfortunately that is my time with you today. We are open for questions will probably shut the camera off at this point to make it a lot.
Questions there are cards up here if you want to email me separately shaking a bit of paper at me which either means Germany invaded Poland or it means that he wants you to feed out fill out some feedback forms OK. So my name's Richard Douglas. If you like this session if you didn't. It was Kevin Kline so thank you.