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:
|
1 2 3 |
SELECT Id FROM dbo.Users 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:
|
1 |
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.
Next, let’s add some columns to our query and force SQL Server to make a tough decision.
10 Comments. Leave new
Hi Brent, One problem I always have and I think this article highlights it, is reading the info the plan pops up(the big yellowy box). Do you have another article that goes over what we should be looking for in them? I read and have read lots on Pref tuning, but when I try to do it myself I usually go “Ahhhh! too much info!”.
Simon – yeah, we talk a lot about that in my Mastering Query Tuning class.
Did this change between SQL 2012 and 2016? I always remember seeks looking at less rows/pages than scans
Alen – nope.
ITRW, seeks *often* but not always read fewer rows than scans do. Seeks are usually used when you do a lookup against indexed fields (as long as your values include the first item in the index), and queries with seeks are often limited in scope, such as selecting a page’s worth of Order Detail rows from a table.
Index scans, OTOH, are most often seen with TOP, or when SELECTing based on non-indexed fields. For example, when looking up the most expensive things ordered in your Order Detail table, it is unlikely that you have an index where the net cost is the first item in the index. In this case, you will frequently see a clustered index scan performed.
Also, TOP selects on indexed fields are index scans. For example, you might need to know the last 100 orders approved. For this, your query might be SELECT TOP 100 Orders.OrderId FROM Orders ORDER BY Orders.ApprovalDate DESC. Assuming you have an index by ApprovalDate , this would go to the maximum value of ApprovalDate and count down 100 rows based on the index, just like Brent’s query in his examples.
And its been this way for as long as I can recall… not as long as Brent, perhaps, but since SQL 7.0 at least.
In the first example where you get an index seek in the plan, what if you ordered by Id? Id is part of the index, but in my tests, I get a Clustered Index Scan. Is this because the Id is not the 1st part of the index? Any thoughts?
Todd – if you want the data ordered by Id, can you think of an index that exists where the data might already be ordered by Id?
Of course…PK, assuming Id is PK, which I was assuming it was. But what’s throwing me off is that without an order by, results are supposedly not guaranteed to be ordered, even if the order by uses the clustered key. In my test, I don’t actually get sort operation in the plan, which is good, it’s just that the Index Seek turns to a Clustered Index Scan. I guess since it doesn’t do a sort operation, it’s still good…maybe one of those cases where just because it says “scan” isn’t bad?
Thanks.
Todd – you said you ordered by Id though. If you ask for things ordered by Id, then you’re likely to use indexes ordered by Id. And yep, scan isn’t necessarily bad – I wish I could cover every variation of the queries here in the comments, but your best bet there will be to experiment and learn. Hope that helps!
I also have a warning on that clustered scan, saying 1 of the columns has no statistics, which might be throwing things off.