SQL Server: Index Fragmentation Internals, Analysis, and Solutions

Course Overview
Hi everyone. My name is Paul Randal, and welcome to my course, SQL Server: Index Fragmentation Internals, Analysis, and Solutions. I'm a SQL Server consultant with SQLskills, as well as a Microsoft Data Platform MVP, and this topic is one that I've been presenting for many years, because I find that many DBAs, developers, and architects don't take index fragmentation into account when designing a database or maintaining performance of the deployed design. I also wrote many of the tools for managing index fragmentation when I worked on the SQL Server Team at Microsoft. It's really impossible to escape index fragmentation once you have indexes, and even clustered indexes suffer from index fragmentation problems when the cluster key leads to random inserts. In this course, we're going to cover everything there is to know about index fragmentation, including how it happens, the various methods of detecting it, how to mitigate it through design or the use of index fill factors, and the various ways you can remove or reduce it by rebuilding or reorganizing indexes. I'll also walk you through some of the SQL Server internals, including how online index operations work under the covers. By the end of this course, you'll have a really good understanding about why it's important to aware of index fragmentation, and what you can do about it. If you work with SQL Server in any capacity, whatever your level, this course is relevant to you, and you only need a basic understanding of SQL Server to be able to comfortably follow along. I really hope you'll join me on this journey to learn how to get your index performance back with the SQL Server: Index Fragmentation Internals, Analysis, and Solutions course here on Pluralsight.

Index Structure and Index Uses
Hello. This is Paul Randal from SQLskills. com, and I'm recoding this course for Pluralsight. This is the SQL Server Index Fragmentation Internals, Analysis, and Solutions course, and you're watching module 2, Index Structure and Index Uses. What This Module Covers. First of all we're going to talk about how an index is actually structured and the basic structure of pages and how they're linked together in an index B tree. Then we're going to talk about the different ways that the SQL Server storage engine makes use of that index structure. We're going to talk about single row lookups, index scans, allocation order scans, and a special twist on allocation order scans called merry-go-round scans. And then we're going to look at a mechanism called readahead, which is what helps to make index scans more efficient, and we'll talk about how readahead can actually be affected by index fragmentation. So this module really lays the groundwork for understanding why index fragmentation can be a performance problem and why it's important for you to be aware of it and to deal with it.

Types of Index Fragmentation
Hello. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server Index Fragmentation Internals, Analysis, and Solutions course, and you're watching module 3, Types of Index Fragmentation. In this module, we're first going to talk about logical fragmentation, which is the kind of fragmentation that most people think about when they think of index fragmentation in general. We'll then talk about extent fragmentation, which is a kind of special case of logical fragmentation that's very common as well. We'll talk about page density problems, specifically problems from having low page density that goes hand-in-hand with having logical fragmentation. And then we'll take a look at some buffer pool usage data that I got back from a survey and how you can use the same code that I use in the survey to check to see that you're not wasting a large proportion of your very precious buffer pool memory.

Causes of Index Fragmentation
Hello. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server Index Fragmentation Internals, Analysis, and Solutions course, and you're watching module 4, Causes of Index Fragmentation. Now that we've talked about the types of index fragmentation, we need to talk about what actually causes them, and the major cause of index fragmentation is an operation called a page split. So what we're really going to be talking about is what causes page splits. We're going to have a look at the page split mechanism and see how what it actually does under the covers leads to logical fragmentation and low page density. We're also going to talk about the cost of page splits, and I'm going to do a long demo that's going to show you just how expensive page splits can be in terms of extra transaction log generation. And then at the end of the module we'll talk about whether DML, Data Modification Language, can cause fragmentation, and you may be surprised that both inserts, updates, and deletes can all cause fragmentation under some circumstances.

Detecting Index Fragmentation
Hello. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server Index Fragmentation, Internals, Analysis, and Solutions course, and you're watching module 5, Detecting Index Fragmentation. In this module we're going to cover how you can track page splits occurring. That's actually pretty problematic because most of the methods of actually tracking page splits count what are called nasty page splits, the ones we've been talking about previously that cause fragmentation, and also good page splits that don't cause fragmentation. We're going to look at the different symptoms of index fragmentation, and we're also going to look at how you can analyze fragmentation using the sys. dm_db_index_physical_stats DMV. Page Split Types. Now in the introduction there I alluded to the fact that there are actually two different kinds of page splits. First of all there are the page splits that we've been talking about in the previous module called nasty page splits, and this is when free space must be created on a page for some reason, maybe a random insert, and there isn't enough space, and it just has to be on that page, so then a page split occurs, and that causes index fragmentation as we've seen. The other kind of page split is what I call good page splits, and this is where you've got some kind of append-only insert pattern. And conceptually at the right hand side of the index inserts are filling up a page, and then a new page must be added onto the right hand side of the index, the page gets filled up, a new page gets added. That's called a page split as well, but these don't cause any index fragmentation. But unfortunately SQL Server groups all these together as page splits and doesn't differentiate between nasty page splits that do cause fragmentation and good page splits that don't cause fragmentation, and that causes problems for us when we're trying to track page splits happening.

Avoiding Index Fragmentation
Hello. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server Index Fragmentation, Internals, Analysis, and Solutions course, and you're watching module 6, Avoiding Index Fragmentation. Previously we've talked about what fragmentation is and how you can detect fragmentation, so now let's talk about how to avoid index fragmentation, and that's going to be all of the things that can cause fragmentation and how you can work around them. If you can't work around some of those things, then what you can do is use an index fill factor, and we'll talk about what a fill factor does and how you can go about choosing and setting a fill factor in a variety of different ways. We'll also talk about removing dead weight, getting rid of any extra indexes that are lying around that could be having fragmentation in them and then having the fragmentation removed for absolutely no gain to the workload. And then finally we're going to talk about a startup option that you can use in some circumstances to increase the contiguity of your indexes and increase the efficiency of your range scans.

Removing Index Fragmentation
Hello. This is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This is the SQL Server Index Fragmentation Internals, Analysis, and Solutions course, and you're watching the final module, module 7, Removing Index Fragmentation. In this module we're going to talk about how you can remove index fragmentation using ALTER INDEX REBUILD or ALTER INDEX REORGANIZE. I'm going to compare and contrast them in a number of different ways. We're also going to look at useful index rebuild options that you can use such as setting the max degree of parallelism using data compression using online index operations. We'll talk about some of the methodologies that you can use for rebuilding indexes and removing fragmentation. And then I'll go into some details about how online index rebuild works, how index reorganize works under the covers, and then we'll look at some resources and we'll summarize everything we've talked about in this course.