[Video] How to Think Like the SQL Server Engine: All-Demo Edition

You’ve heard of my free How to Think Like the Engine class, and maybe you even started watching it, but…it has slides, and you hate slides.

Wanna see me do the whole thing in Management Studio, starting with an empty query window and writing the whole thing out from scratch live? This session is for you.

I’ll teach all of the concepts without a single slide. You’ll still want to print out this 5-page PDF so you can understand the index pages I’m referring to onscreen – it’ll make a world of difference. This was be a lively live-stream session on my Twitch channel full of questions and jumping-off-topic demos: if you prefer a rigidly structured, not-meandering session, stick with the conventional version of this session.

Part 1 (50m)

We start with the clustered index on the Users table in StackOverflow, then gradually layer queries in, talking about execution plans, STATISTICS IO, and why queries probably look different between production & development:

Part 2 (30m)

Now that you’re done with part 1, let’s switch to SELECT *, add an ORDER BY, and see how SQL Server struggles with repeated queries. To make ’em go faster, we’re gonna need an index, so we build one of those too.

Part 3 (28m)

We finished part 2 by adding a nonclustered index. Now, let’s see all the ways that makes our query go faster. Then, we’ll add more columns to our query so that the index isn’t covering anymore, and we’ll force SQL Server to make a tough decision: do lots of key lookups, or just scan the table? Then we’ll learn how SQL Server uses statistics to make that decision.

Part 4 (13m)

We finished part 3 with two similar-looking queries that produce very different behavior: one does a seek, and the other does a scan. Now you’ll learn why, plus add yet another index to make this query go faster, and then discuss the drawbacks of too many indexes.

Enjoyed this session? Follow me on Twitch to be alerted whenever I’m streaming.