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.
Course info
Rating
(61)
Level
Beginner
Updated
Sep 15, 2017
Duration
7h 11m
Table of contents
Course Overview
Introduction
Row-based vs. Column-based Indexes
Row-based Index Concepts
Choosing the Clustering Key
Clustered Index Internals
Nonclustered Index Internals
Data Access Patterns
Understanding the Tipping Point
Covering Queries
Using INCLUDE to Cover Queries
Understanding Filtered Indexes
Index Consolidation
Description
Course info
Rating
(61)
Level
Beginner
Updated
Sep 15, 2017
Duration
7h 11m
Description

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!

About the author
About the author

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.

More from the author
Section Introduction Transcripts
Section Introduction Transcripts

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.

Introduction
Hi, this is Kimberley Tripp from sqlskills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server and its entitled Indexing for Performance. In this module we're going to cover a general overview by what I mean by indexing for performance and what I'm going to cover in this course. Specifically in this module what we're going to talk about is my definition of indexing for performance, all the different index uses and types that are possible in the different versions of SQL Server and we'll go through the objectives and structure of this actual course. Indexing for performance to me is really a combination of query tuning and sever tuning and getting a lot of insight into what's going on in your specific query, so let me explain. There's really an art and a science to indexing. The science side of it is to look at a query, figure out exactly which index is best for that query and once you figure out what's best for that query based on the data, the workload, the platform because there's things that are specific to SQL Server that would make an index better than others, so once you figure out the best index for the query, then what you have to do is analyze the environment that you're actually putting that query into, meaning the server itself, what other indexes exist, what are the different usage patterns on that server and how do you really come up with a better server index rather than just what's good for the query? And that involves something called index consolidation, so I'm going to spend a lot of time on what that is, what that means and how to do that effectively.

Choosing the Clustering Key
Hi, this is Kimberly Tripp from SQLSkills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server and is titled Indexing for Performance, in this module, we're going to cover Choosing the Clustering Key. As far as what we're going to cover specifically in this module, we're going to get into what a clustered index should look like and what some of the interesting considerations are for the clustering key, we'll look at choosing that clustered index key, we'll look at the criteria that is behind what makes the good clustering key, we'll go through some suggestions for ideal clustering keys and I'll talk about clustering on an identity column. From a clustered index perspective, you don't have to have a clustered table, I've mentioned this before, a table can be either clustered or it can be a heap, if you'd like a clustered index, and I do highly recommend them, you really need to choose wisely, because you can only have one per table and the reason why you can only have one per table is that the order of the data will be physically structured at the time that clustered index is created, based on what the key is that you're clustering by, from then on, the data will be logically maintained using a doubly-linked list. The most important thing I can stress is that if you choose a clustered index, you will have to do regular and automated maintenance to make sure that that clustered structure stays efficient and effective and in fact, it turns out that some clustered indexes might be more prone to fragmentation and require more maintenance that others, so it's really important to choose wisely for your clustering key, so there's a lot of criteria that I want to look at here.

Understanding the Tipping Point
Hi, this is Kimberly Tripp from SQLskills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server, and it's titled Indexing for Performance. In this module we're going to cover one of my favorite topics, and that's something that I call the Tipping Point. Part of the reason why this is one of my favorite topics is that this will I think for some of you be a huge light bulb moment in understanding why some of your queries sometimes have one plan, and sometimes have a different plan, and it will also help really introduce you to why some of your non-clustered indexes just aren't as useful as you thought they were going to be, so what I have to do to make sure that this makes sense is I have to explain what the tipping point is. I want to talk about selectivity in terms of when is a query selective enough to warrant using a non-clustered index, but it's not as simple as you might think, so we'll talk about why and how the tipping point varies, and we'll really start to get into when bookmark lookups are just too expensive for SQL Server to use as the access method to get to your data.

Covering Queries
Hi, this is Kimberly Tripp from SQLskills. com, and I'm recoding this course for Pluralsight. My course is focused on SQL Server and is titled Indexing for Performance. In this module, we're going to cover Covering Queries, (laughs) which sounds a little bit strange, but it's really using indexes to cover a query. As far as, specifically, what we're going to cover in this module, we're going to look at nonclustered indexes in terms of seeks and scans and what some of the powerful things are that SQL Server can do with certain types of nonclustered indexes based on certain types of queries. So we'll start tying together various request for data and the ways that SQL Server can optimize those requests. It is all tied to selectivity though, and we'll talk a little bit about the amount of data that your query is requesting and whether or not that's going to have an impact on the different choices that SQL Server has. We'll talk about a concept called covering. It's an incredibly powerful concept of covering. We'll talk about how it's possible, how it works, how you can improve low-selectivity queries, and it's going to seem really attractive and you're going to get very excited about taking some of those range-based queries and improving them using these techniques. But I'm really going to stress how to cover cleverly, correctly, and concisely.