SQL Server: Indexing for Performance
This course will teach you how to correctly choose indexes for your environment. You'll also learn how to understand index internals, how indexes are used, and much more.
What you'll learn
The most important feature in SQL Server when it comes to getting the required workload performance is probably indexing. But the indexing possibilities you have can be bewildering, and it's easy to make the wrong choice, leading to wasted resources and maybe even worse performance! In this course, SQL Server: Indexing for Performance, you'll learn how to correctly choose indexes for your environment. First, you'll discover how clustered and nonclustered indexes work and how they are used by SQL Server, both of which are crucial when choosing the correct indexes to create. Next you'll explore the tipping point - when an index can be used or not, the benefits of covering queries, and how to use the INCLUDE option to more effectively cover queries. Finally, you'll learn how to use filtered indexes and get an introduction to index consolidation for effective server tuning. When you've finished this course, you'll have the skills and knowledge to start adding and changing indexes in your environment for better performance!
Table of contents
- Introduction and Table Structures 2m
- Row-based and Column-based Structures 4m
- Choice Depends on Workload 5m
- Strategies by Version 2m
- RDW/Columnstore Indexes by Version 5m
- Row-based vs. Column-based Indexes 2m
- Row-based vs. Column-based Indexes: Problems 2m
- Indexed Views vs. Columnstore Indexes 6m
- Module Summary 2m
- Introduction 1m
- Are Bookmark Lookups Optimal? 3m
- The Tipping Point: Concept 4m
- The Tipping Point: Employee Scenario 3m
- Demo: The Tipping Point 13m
- The Tipping Point Varies 3m
- Tipping Point Variation Examples 4m
- Generalizing the Tipping Point 3m
- Demo: The Tipping Point with Very Large Tables 17m
- Summarizing the Tipping Point 4m
- Introduction 1m
- Nonclustered Index Scenario and Seeks 4m
- Demo: Nonclustered Covering Seek 5m
- Nonclustered Index Scans 3m
- Demo: Nonclustered Covering Scan 3m
- Data Selectivity: The Best Index Varies 4m
- Demo: Data Selectivity - The Best Index Varies 6m
- What Is Covering and How Is Covering Possible? 3m
- Improving Critical Queries with Low Selectivity 5m
- Covering Correctly, Methods for Covering, and Module Summary 4m
- Introduction and What Is a Filtered Index? 5m
- Filter Indexes Overview 2m
- Filtered Index Requirements 5m
- Example Use Case 3m
- Demo: Filtered Indexes 16m
- They're Small so Cover More! 3m
- Filtering Sets 5m
- Demo: Interval Subsumption 8m
- Plan Caching 3m
- Demo: Plan Caching 12m
- What Filter? 2m
- Demo: Examing the Filter Predicate 7m
- Maintenance 4m
- Module Summary 2m
About the author
Kimberly L. Tripp is a SQL Server MVP and a Microsoft Regional Director and has worked with computers since 1985. Since 1995, Kimberly has worked as a Speaker, Writer, Trainer and Consultant for her own company SQLskills.com which she now runs with her husband – Paul Randal. Working together Kimberly and Paul enjoy the ability to work on different types of projects – from Immersion Events to consulting to speaking at conferences to writing articles and books. Kimberly has presented lectures and ... more