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!
Kimberly is a SQL Server MVP, Microsoft Regional Director and President/Founder of SQLskills.com, which she now runs with her husband, Paul Randal. Kimberly’s areas of expertise focus on performance tuning through effective database design and architecture.
Course Overview Hello, my name is Kimberly Tripp and welcome to my course, SQL Server, Indexing for Performance. I'm a SQL Server consultant with SQL skills as well as a Microsoft data platform MVP and in over 25 years working with SQL Server, the feature that I enjoy the most is indexing but indexing's a huge topic and there are many aspects to it, internals, data access methods, features, rules, restrictions, and of course, performance tuning for your queries. To do a good job tuning SQL Server I feel that you need to understand all of these facets of indexing because there's both a science to indexing as well as an art. One side of indexing for performance is query tuning. This is always the first step for improving performance of a query. You analyze what the query is trying to do and you come up with the best index for that query but what's best for the query might not be best for the server, so after query tuning comes server tuning and even server tuning has multiple facets to it. The focus of this course is to lay the foundation for truly understanding the internals of indexing and the features and options available to you so that you know the rules, requirements and best uses for all of the index options available. Specifically we'll look at the index options for row-based indexes and column store indexes as well as the restrictions on the features based on the version that you're using. Then, we'll dive into the internal structures of row-based indexes, both clustered and non-clustered and then expand our knowledge to include end filtered indexes. Throughout the course we'll use a case study to view these structures and focus on query tuning. Finally, we'll wrap up with consolidation techniques and server tuning strategies in order to reduce the impact of your indexing changes so that you'll no longer have to cross your fingers and hold your breath when they go live in production. Experience with investigating query performance problems is helpful, along with the ability to write basic T-SQL queries. I hope you'll join me as we explore the many aspects of indexing in this course, SQL Server, Indexing for Performance.