In our last episode, we added a nonclustered index, and now even though this query has a filter and an ORDER BY, it hardly did any work at all – just a lightweight index seek:
Ah, index seeks – nothing better than that, right?
Well, not exactly. Let’s make one tweak to the WHERE clause and find all of the people who accessed Stack Overflow going all the way back to the days of programming your ox team:
WHERE LastAccessDate > '1800-01-01';
We still get an index “seek”:
Yes, it’s a seek, even though it’s reading every single row in the index. You probably thought that would be a scan, but it’s not.
Index seek doesn’t mean lightweight.
Index seek only means SQL Server is going to jump to a specific point in the index and start reading.
It has absolutely nothing to do with how few or how many rows you’re going to read. You can do an index seek and read just one row, or read all of the rows in the table.
If you’ve been looking at an execution plan and going, “seek seek seek, all seeks here, guess it can’t get any better than that,” then you’ve been missing out on something pretty big.
Index scan doesn’t mean reading the entire table, either.
Let’s get any 10 users:
SELECT TOP 10 * FROM dbo.Users;
We get a clustered index scan – meaning, it’s reading the entire table, right?
Nope – SQL Server starts scanning the table, but it can bail as soon as it’s found enough rows to deliver your query results. Hover your mouse over the clustered index scan, and it says “Number of Rows Read: 10.” If your number of rows read equals the number of rows your query outputted, that’s great! I can’t possibly design a better index to support this query. A clustered index scan is absolutely perfect here.
Here’s what seeks and scans really mean:
- Index seek: “I’m going to jump to a specific point and start reading.”
- Index scan: “I’m going to start at either end of the table and start reading.”
Here’s what they don’t mean:
- How many rows we’re going to read
- Whether we’re seeking on all of the columns in the index, or just the first one (more on that later)
- Whether the index is good or bad for this query
I can’t emphasize this enough: you can’t make index judgements based purely on seeks or scans. This is especially true when you start looking at index usage metrics from tools like sp_BlitzIndex: we’re simply reporting how the index is being accessed. You can’t say, “This index is getting seeks, so it’s perfect,” nor can you say, “This index is getting scans, so the keys must be in the wrong order.” You’ve gotta dig deeper than that to see how much data is moving around – and that just isn’t something the DMVs track. You’ve gotta look at query plans closely to decipher what’s going on.