SQL Server: Index Fragmentation Internals, Analysis, and Solutions
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.
What you'll learn
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.
Table of contents
- 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
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. Since leaving Microsoft, Paul has been a full-time consultant and trainer on SQL Server around the world, including authoring and teaching the prestigious Microsoft Certified Mast... more