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.
While you’re stuck inside wondering if you’ll ever be able to attend a user group meeting again, I’m giving you free access to training materials that your boss probably wouldn’t have been nice enough to buy you anyway. So far this week, we’ve covered DBCC CHECKDB,how to set up Ola Hallengren’s maintenance scripts, and restoring for oops deletes. Today, let’s talk about two common configuration issues. Don’t dilly-dally on these though: these blog posts will be online during May only, and they’ll disappear on June 1.
How to Configure TempDB (9m)
We joke that it’s like SQL Server’s public toilet, and just like a public toilet, you can make it a much nicer place if you … okay, you can’t make it much nicer. A little nicer, maybe.
Configuring and Using the Dedicated Admin Connection (DAC) (7m)
When you’re really desperate – no seriously, this isn’t a public toilet joke – and you need to get access to the SQL Server, and it’s not responding to connection requests, the Dedicated Admin Connection is like a…whoa, it’s like a public toilet, isn’t it? This is deep.
Configuring Lock Pages in Memory (6m)
This one’s shorter and sweeter – although isn’t everything sweeter than a public toilet – because maybe you shouldn’t actually configure this one. Erik talks about the pros and cons:
While you’re stuck inside wondering if you’ll ever be able to attend a user group meeting again, I’m giving you free access to training materials that your boss probably wouldn’t have been nice enough to buy you anyway. So far this week, we’ve covered DBCC CHECKDB, and today let’s talk about restores. Don’t dilly-dally on these though: these blog posts will be online during May only, and they’ll disappear on June 1.
Setting Up Ola Hallengren’s Maintenance Scripts (34m)
Ola Hallengren’s Maintenance Solution is the most popular free, open-source script for backups, corruption checking, and index maintenance. Maintenance plans are better than nothing, but Ola’s stuff is better than maintenance plans. Here’s how to set them up:
Sending Emails with Database Mail (10m)
You’ve got mail! It’s from your SQL Server. There’s corruption. You don’t wanna use database mail to send greeting cards, but you definitely wanna get alerted when all hell breaks loose – especially if you’ve got your monitoring software’s alert emails set up to go into a folder you never read.