You’re comfortable writing T-SQL queries, and now you want to make ’em faster – without changing the queries themselves.
In this one-day course, you’ll learn:
- How to design indexes for a query without looking at its execution plan
- How to pick the order of keys in an index
- How query parameter values can change which index is better
- Where to find index recommendations using query plans, DMVs, and sp_BlitzIndex
- How SQL Server’s index recommendations are built, and why they’re often wrong
Note: this course does not cover columnstore, In-Memory OLTP, XML, or spatial indexes. (Nothing against those – they’re just beyond fundamentals.)
Live Class Logistics
Watch the live stream here. The live stream is free only for the April class – after that, it’s back to a paid private class. Be there or be square: the recordings are only available to my paid students.
Read & watch the prerequisites module before the class. I can’t emphasize enough how important that is: if you don’t fully comprehend the stuff in there, you will be terribly behind during class. During this class, you’ll frequently hear me say, “To get this part, you need to think back to ___ from How to Think Like the Engine.”
To ask questions and talk to the other attendees, get an instant invite to Slack here, then join the #BrentOzarUnlimited channel. This way attendees can help each other with their questions during the live labs. (I don’t use the Facebook/YouTube/Twitch comments because I want to be able to show everyone’s questions up onscreen at the same time, and Slack does a better job of code formatting.)
To follow along with the demos and do your homework, download a Stack Overflow database. I’ll be using the medium-sized 50GB StackOverflow2013 database, but if you’ve got a different one handy (either the smaller 10GB or larger 300+GB one), that’s fine too – the same concepts will still apply. If you’re on a slow desktop/laptop, feel free to use the small 10GB StackOverflow2010 database. Then download the homework scripts here.
To watch the instant replay recordings, students with a paid membership can click on the links at right.
- 00 Prerequisites Before the Class (90m)
- 01 Designing Indexes for the WHERE Clause (35m)
- 02 Lab: Indexing for WHERE (38m)
- 03 Designing Indexes for ORDER BY (23m)
- 04 Lab: Indexing for WHERE + ORDER BY (44m)
- 05 Designing Indexes for JOINs (27m)
- 06 Lab – Indexing for Joins (25m)
- 07 SQL Server’s Built-In Index Recommendations (37m)
- 08 Lab – Getting Index Recommendations and Tuning Them (10m)
- 09 Recap and Next Steps (8m)