Normally when you're looking at an execution plan, and you see an index seek followed by a key lookup, that means it's running relatively quickly.
To explain, let's take the Users table in the Stack Overflow database and run this query that we explained in this post:
What's the difference between SQL Server's row compression and page compression, and when does each one make sense?
Row compression turns every fixed-length datatype into a variable-length datatype, using as little space as possible to store it
Page compression does that, AND adds a dictionary of repeated data on the page, getting more compression at the cost of more CPU
You've probably heard of the setting Max Degree of Parallelism. I hate that name: it should really be called just plain ol' Degrees of Parallelism, and here's why. There are a lot of conflicting opinions out there about how to set it, and Microsoft has official guidance about it in that article above. It's basically set…
Over the years, tables - like your waistline - tend to get bigger. We keep tacking on more and more columns, one at a time, in order to handle app needs. It's easier to add "just one more column" than it is to break things off into a whole separate table. When you're only handling…
You've heard that page splits are bad, and they're an indication that your table design is making your storage work too hard. You've heard that the right answer to fix it is adjusting fill factor lower, or doing regular index maintenance.
Before you watch the below animation, you'll wanna get up to speed with how index seeks work. Then, let's explain page splits with an animation:
In my free How to Think Like the Engine class, I gloss over the entire concept of B-trees and just hand-wavily say "SQL Server can seek to the page that has the row you're looking for." That's always kinda bothered me, so I finally gave in to temptation and whipped up an animated example of a clustered index on the Users table:
