1

There is a table Sales.SalesOrderDetail in database AdventureWorks2014.

I have two queries:

--Query 1 uses index IX_SalesOrderDetail_ProductID
SELECT
sod.SalesOrderID
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderID = 1

and: 

--Query 2 uses index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
SELECT
sod.SalesOrderID
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderID > 1

Query plan: enter image description here

The query plan at Brentozar.com can be seen here.

And indexes:

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales]. 
[SalesOrderDetail]
(
    [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

and clustered index looks like this:

ALTER TABLE [Sales].[SalesOrderDetail] ADD  CONSTRAINT 
[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

My question is why does query optimizer prefer another index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID instead of IX_SalesOrderDetail_ProductID?

StepUp
  • 36,391
  • 15
  • 88
  • 148
  • probably there is another index interfering with the clustered index. Check the indexes, disable any index that uses `SalesOrderID`, just leave the clustered index. and try to update statistics. Then check again. – iSR5 Nov 03 '18 at 21:11
  • @iSR5 how is it possible to update statistics? – StepUp Nov 03 '18 at 21:23
  • 1
    use `UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN` – iSR5 Nov 03 '18 at 21:27
  • Actually your comments are backwards. The first query uses a non-clustered index scan, and the second query uses a clustered index seek. – Aaron Bertrand Nov 03 '18 at 21:31
  • Please see [paste the plan](https://www.brentozar.com/pastetheplan/instructions/) for a better way to include an execution plan in your question. – HABO Nov 03 '18 at 21:56
  • @HABO I've inserted a query plan https://www.brentozar.com/pastetheplan/?id=rkjpNB33m – StepUp Nov 04 '18 at 10:09
  • @AaronBertrand I've inserted an actual execution plan https://www.brentozar.com/pastetheplan/?id=rkjpNB33m . In my view, my comments are not backwards or did I misunderstand something? – StepUp Nov 04 '18 at 10:11
  • 1
    Look at what you’re calling “query 1” and “query 2” - the order you’ve listed them, and the comments in the code. – Aaron Bertrand Nov 04 '18 at 12:17

1 Answers1

3

Well, I mean, you're selecting all the rows (except maybe one). There really is no difference between a seek and a scan here. SQL Server is choosing to perform a single scan of the skinniest index instead of doing 80,000 seeks (or however many orders are in the table).

A seek is not always the best choice, but this is a common misconception. In fact sometimes you absolutely want a scan.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Am I correctly understood that `table scan` is better to see the whole table when I want to show the all rows of a table than `table seek`? – StepUp Nov 04 '18 at 10:16
  • 1
    Yes, usually a seek won’t be good for that. SQL Server makes a decision based on number of rows / % of table or index, look up “tipping point in SQL Server.” – Aaron Bertrand Nov 04 '18 at 12:20
  • thank you for your answer, is there a way to see that `table scan` is better than `table seek`? – StepUp Nov 04 '18 at 18:22
  • 1
    Well it’s an index scan not a table scan. But sure, use a FORCESEEK hint and compare the I/O and duration. For extra fun add more columns and even some columns that aren’t covered by the index. – Aaron Bertrand Nov 04 '18 at 20:16