SQL Server: Index Fragmentation Internals, Analysis, and Solutions

By Paul Randal
Index fragmentation is an inescapable part of any SQL Server environment, no matter which version of SQL Server you're using or what hardware or I/O subsystem you have, and this comprehensive course will give you all the information you need to properly understand and deal with it, applicable to anyone responsible for SQL Server, from SQL Server 2005 onward.
Play course overview
Course info
Rating
(273)
Level
Intermediate
Updated
Jun 5, 2015
Duration
2h 39m
Table of contents
Course Overview
Course Overview 2m
Introduction
Introduction and Why Index Fragmentation Is Important 3m Common Myths Around Index Fragmentation 3m Course Structure and Module Summary 2m
Index Structure and Index Uses
Introduction 1m Index Structure 3m Demo: Index Structure 4m Singleton Lookup 2m Scan 3m Allocation Order Scan 2m Side Note: Merry-go-round Scans 2m Readahead 4m Module Summary 1m
Types of Index Fragmentation
Introduction 1m Fragmentation in Action 2m Demo: Fragmentation Example 3m Logical Fragmentation Details 2m Extent Fragmentation 4m Low Page Density 3m Increased Buffer Pool Usage 2m Demo: Examining Buffer Pool Usage 6m Module Summary 1m
Causes of Index Fragmentation
Introduction 1m Page Splits 2m Page Split Causes 4m Demo: Page Split Causes 5m Page Split Mechanism 4m Demo: Extra Logging From PageSplits 3m Can DML Cause Fragmentation? 2m Module Summary 1m
Detecting Index Fragmentation
Introduction and Page Split Types 2m Tracking Page Splits 2m Demo: Tracking Page Splits 5m Symptoms of Index Fragmentation 2m sys.dm_db_index_physical_stats DMV 2m sys.dm_db_index_physical_stats Modes 2m Interpreting DMV Results 2m Demo: Using sys.dm_db_index_physical_stats 7m Module Summary 1m
Avoiding Index Fragmentation
Introduction 1m Avoiding Index Fragmentation 2m Using Index Fill Factors 2m Setting and Choosing a Fill Factor 3m Demo: Viewing and Setting Index Fill Factors 4m Removing Dead Weight 2m Using the -E Startup Option 2m Module Summary 2m
Removing Index Fragmentation
Introduction 1m Comparing REBUILD and REORGANIZE 5m When to Use REBUILD vs. REORGANIZE 1m Demo: REBUILD and REORGANIZE 3m Useful Index Rebuild Options 3m Online Index Operation Restrictions 2m Demo: Low Priority Lock Waits for Online Operations 4m Removing Index Fragmentation 3m Methodologies 4m Inside Online Index REBUILD 3m Inside REORGANIZE 3m Module Summary 1m Resources and Course Summary 3m
Description
Course info
Rating
(273)
Level
Intermediate
Updated
Jun 5, 2015
Duration
2h 39m
Description

You cannot escape index fragmentation in any SQL Server environment, no matter which version of SQL Server you're using or what hardware or I/O subsystem you have, and it can be detrimental to performance in many ways. In this comprehensive course, you will learn everything you need to know about index fragmentation, including why it's a problem, how it happens, how to detect it, how to avoid it, and how to remove it. The course is applicable to anyone responsible for SQL Server performance, including developers, DBAs, and architects. All course demos are shown on SQL Server 2014 with references to behaviors (where different) on all versions. Strategies for index fragmentation prevention, mitigation, and removal that you will learn in the course apply to SQL Server 2005 onward.

About the author
Paul Randal
About the author

Paul is the CEO of SQLskills.com, a Microsoft Regional Director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Among other things, he wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development.

More from the author
SQL Server: Understanding and Performing Backups
Intermediate
2h 27m
Nov 7, 2017
SQL Server: Advanced Corruption Recovery Techniques
Advanced
3h 57m
Nov 22, 2013
More courses by Paul Randal
Section Introduction Transcripts
Section Introduction Transcripts

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.

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT