Paul White (4/2/2009)
Not so sure. The article you wrote simply shows that a range seek/scan can cover all the rows in a table. Well, yeah 😉
I was challenging your statement that a seek returns 1 row. It does not.
In the terms used by the query optimiser and query processor, a seek returns 0 or more rows and does a search down the b-tree to find the start or end of the range. A scan reads all the rows in the table and may or may not traverse the b-tree. Even if it does, it doesn't search, but just uses the b-tree to find the leaf pages
The 'Index Seek' operator in that example has a tool-tip description of "*scan* a particular range of rows from a nonclustered index". So it's a seek logical operator doing a physical scan of a portion of the index?
English language semantics here. Perhaps 'Reads a particular range of rows' would be more correct.
Even a full scan of an index is a seek operation (ordered or unordered) down the b-tree.
Not necessarily. There are other ways to get at the leaf pages than via the b-tree. Especially in an unordered scan of a cluster.
To complicate things further, a single-row seek done as part of a key lookup operation (if the QO estimates that it will access many rows eventually) may implement ordered or unordered prefetch, which is a scan, isn't it?
We're crossing portions of the engine now.
Seek/scan is a QO/QP operation. Prefetch is done and controlled by the storage engine. Prefetch is what affects the readahead reads in the IO stats
What is the real different between a load of consecutive single-row seeks and an ordered scan?
A lot. Each of the single-row seeks would traverse the b-tree. The scan would do it at most once, and may not need to access the b-tree at all if it's doing an unordered scan, as that just uses the IAM pages.
So, if you were to compare 500 single row seeks with a scan that reads 500 pages, the number of IOs would be dramatically different. If we say those 500 rows are on 100 data pages (at 5 rows per page) and the b-tree is 2 levels deep (just the root and the leaf) then 500 single row seeks would read a total of 1000 pages. The scan would read 100 or 101 pages.
That, by the way, is why bookmark/key lookups are so expensive. They are single row seeks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability