Most people who know SQL execution plans will say, without reservation, that an index seek on a particular index is better than an index scan on the same index. In the vast majority of cases, that’s true, but there are times when what appears in the execution plan as an index seek is actually an index scan.
Let me show an example
CREATE TABLE TestingSeeks ( id int identity (1,1) not null, SomeStr char(6) default '' -- a filler ) GO insert into TestingSeeks (SomeStr) select top (500000) '' from sys.columns c1 cross join sys.columns c2
We have a table here with an identity column on it, starting at 1 and incrementing by 1 row. Hence, there will be no negative values in the table. I’m going to then put a nonclustered index on that column (the table has no cluster, it’s a heap)
CREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id)
Fair enough. If I query all the rows in the table and retrieve just the ID column, I’ll get a scan on that index, as is pretty much expected and Statistics IO tells me that 935 pages were read
So a read of the entire index is 935 pages. Now, let me add a filter.
select id from TestingSeeks where id>0
That predicate is SARGable and there is an appropriate index. Sure enough, we get an index seek here.
That’s good. Isn’t it?
Well, not really. That filter’s going to match all the rows in the table. We know there are none with an id less than 1. Statistics IO tells me that 935 pages were read, exactly the same as for the scan. It’s a seek operation, but it’s done exactly the same work as the scan did.
Moral of the story: A seek doesn’t always read only a portion of the index, a seek on an index is not necessarily doing less work than a scan on the same index and silly tricks intended to force an index seek are not going to make a query run faster.
Good information, thanks.
Pingback: SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA
Nice. I see a lot of hints & what not trying to force seeks and loop joins as if they were ALWAYS better than the alternatives. As you’ve just shown, it isn’t always true.
Yup, likewise.
That reminds me, I have a half-written post on optimal joins. Must finish it.
Hi Gail Shaw
Is this test done on SQL 2000 or SQl 2005
What will be the number of pages read if you
select * from testseeks
SQL 2008 RTM. SQL 2005 should have the same behaviour
Doing a SELECT * will read the heap, not the index because the index is not covering and the lookups are far too expensive. The operator that appears in the plan is Table Scan
Table ‘TestingSeeks’. Scan count 1, logical reads 1174
Wont the 2nd query be slower(using index seek for all rows) than reading directly from the heap as you did in the first query?
No, because the seek is just seeking to the first row (on the first page) and then reading all the leaf pages. It’s not as if it’s doing a seek for each value in the table.
The number of page reads was the same and that, for a simple query like this, is the major factor on speed. But feel free to test it yourself, all the code’s in the post.
I ran the code and got almost similar execution time results(index seek operation was marginally better than the other). Number of logical pages reads were identical too.
Pingback: IO Statistics for Index Seek