Index scans aren’t always bad, and index seeks aren’t always great.

Somewhere along the way in your career, you were told that:

• Index seeks are quick, lightweight operations
• Table scans are ugly, slow operations

And ever since, you’ve kept an eye on your execution plans looking for those performance-intensive clustered index scans. When you see ’em, you go root ’em out, believing you’ve got a performance problem.

Thing is, … they lied to you. Seeks aren’t necessarily good, nor are scans necessarily bad. To straighten you out, we’re going to walk through a series of demos.

First up: a “bad” index scan.

Let’s start with this query – using the Stack Overflow 2013 (50GB) database:

The resulting execution plan has a clustered index scan – reading the whole table, 2.5M rows:

That first line, SET STATISTICS IO ON, turns on a set of messages that will show you the number of logical reads SQL Server performs when executing your query. Those are measured in 8KB pages. The output shows up in the Messages tab:

SQL Server had to read 44,530 8KB pages (the whole table) to count all of the rows.

Okay, so that’s a “bad” scan – because it reads all of the pages – although I could argue that it’s really more of a bad query. (Do you really need an accurate, up-to-the-second count of the number of rows? Probably not – you could probably cache that sucker in the app tier and use the same results for hours at a time. But I digress.)

But here’s a “good” index scan.

Take this query:

We’re telling SQL Server, “Just give me the first 10 rows – I don’t care whether they’re the first ten, or the last ten, or just some random ten you pulled out of your back end.” The execution plan for this one is also a table scan:

See the teeny tiny arrows? That means only a limited number of rows came out – which sounds like a good thing, but here’s the real indicator that it was a good thing:

SQL Server only had to read 5 8KB pages – that’s 40KB for those of you with my math skills – and that’s hardly anything at all! Heck, that’s in the exact range as our next demo.

Here’s a “good” index seek.

The plan shows us dive-bombing into one specific user, and only reading out one row:

It’s a super-efficient operation, only reading 3 8KB pages – not that far off the “good” index scan we did a second ago!

That’s the kind of operation you think about when you see the word “seek” on a plan – but that’s only one kind of example. Try this one on for size:

Here’s a “bad” index seek.

As you mentally build an execution plan for this, you should know that the lowest Id in the StackOverflow.Users export is -1. They all go positive from there.

That query reads back every single user in the table.

You would probably consider that a table scan, but that’s not what the plan shows:

It’s technically a “seek” because SQL Server is seeking to a particular value, and then reading out the rows from there. Logical reads shows more proof that it’s reading the entire table:

That’s a lot of pages. That’s not what you think of when I say “seek.”

Here’s what “seek” and “scan” really mean.

Seek means SQL Server knows what specific row it’s going to start at. That’s it.

• It might be the first row in the table
• It might be the last row in the table
• It might read out just one row
• It might read out all of the rows
• It might have been performed multiple times (hover your mouse over the operator in the plan and look at “number of executions” for an indication)

Scan means SQL Server is going to start at either end of the index (min or max) and start reading from there. That’s it.

• It might read out all the rows (which is what you usually expect, but…)
• It might only read out one row

An index seek operation isn’t necessarily good, nor is an index scan inherently bad.

To cast judgment, dig deeper.

Let’s take this query:

Its execution plan does a clustered index seek – it’s going to jump to what happens to be the first row in the table (Id -1) and read through all of the rows in the entire table, looking for ones who have a reputation < 0:

So it’s a seek – but is it worthy of attention? Do I need to do something about that operator? To figure out, hover your mouse over the seek operator and check out the tooltip:

And look at:

• Number of Rows Read: 2,465,713 – meaning SQL Server had to examine this many rows in order to find the juicy rows that you actually wanted.
• Actual Number of Rows: 0 – meaning its search was fruitless.

When SQL Server has to examine a lot of data to find not a lot of results, that’s a slow way to access the data. You might be fine with that – this might be a query that never runs – but if you want it to run faster, this is a place you might want to focus your indexing improvements. It doesn’t matter whether it’s a seek or a scan – if you have to read a lot of noise to get a little bit of signal, that’s a problem.