CODEWITHSUNDEEP
sql-serverindexing
variable
variable

Reputation: 9704

Is an Index seek operation more costly compared to index scan when the data volume is high?

I understand that table scan looks at every record in a table. And a table seek looks at specific records.

Also, an Index scan/seek concept is same as above with the difference being that the values are in sorted order.

Question: Is an Index seek operation more costly compared to index scan when the data volume of the item being searched is high? and why?

Example: Lets say statistics are stale and the estimated rows is 100 but actual rows are 100000. The engine decides to use index seek. Will this more costly than index scan and why?

SELECT StockItemID 
FROM Examples.OrderLines 
WHERE StockItemID = 1;

I am referring to a book "Exam Ref 70-762 Developing SQL Databases" which has this example and it reads on page number 338 as: "Because this (stale statistics) value is relatively low, the query optimizer generated a plan using an index seek, which could be less optimal than performing a scan when data volumes are high". I am trying to understand why a seek is considered expensive.

Upvotes: 0

Views: 145

Answers (1)

Martin Smith
Martin Smith

Reputation: 453426

You will never see SQL Server choose a scan for this query if you have an index on StockItemID as this covers the query and there is no "tipping point" issue.

It will always choose a seek even if it estimates that 100% of the rows match.

Example

CREATE TABLE OrderLines
(
OrderID     INT IDENTITY PRIMARY KEY,
StockItemID INT INDEX IX1
);

INSERT INTO OrderLines
            (StockItemID)
SELECT 1
FROM   sys.all_objects

SELECT StockItemID
FROM   OrderLines
WHERE  StockItemID = 1;

enter image description here

In the case that the seek returns all the rows in the table the only difference between a seek and an index ordered scan is how the first row is located (by navigating the depth of the B tree or simply going to the first index page from metadata). This is likely to be negligible.

One edge case where a scan may perform better would be if an allocation ordered scan was preferable and you are running with a table lock or nolock so this becomes a viable option.

Upvotes: 2

Related Questions