Why index setting is able to affect query cost when scan is imperative

Question

I'm having a review of performance tuning study and practicing with AdventureWorks2012.
I built 4 copies from Product table then setup with the following indexes.

--tmpProduct1 nothing
CREATE CLUSTERED INDEX cIdx ON tmpProduct2 (ProductID ASC)
CREATE NONCLUSTERED INDEX ncIdx ON tmpProduct3 (ProductID ASC)
CREATE NONCLUSTERED INDEX ncIdx ON tmpProduct4 (ProductID ASC) INCLUDE (Name, ProductNumber)

Then I do the execution plan with following queries.

SELECT ProductID FROM tmpProduct1
SELECT ProductID FROM tmpProduct2
SELECT ProductID FROM tmpProduct3
SELECT ProductID FROM tmpProduct4

I expected the performance should be the same to all four of them since they all need to scan. Plus, I select only ProductID column and there is no WHERE condition.
However, it turns out to be

Why is clustered index more expensive than non-clustered index?
Why non-clustered index reduce the cost in this scenario?
Why columns store makes query4 cost more than query3?

Scanning the clustered index is more expensive because it's wider. It contains all columns, as opposed to only the indexed columns. Less columns = more rows fit on a page = less pages scanned. That's why you'll hear people talk about "covering indexes". — Jeroen Mostert, Nov 20 '17 at 16:13

TheGameiswar · Accepted Answer · 2017-11-20T16:42:33.840

For query1 without indexes, you are scanning entire table..

For query2 ,you have a clustered index,but then again..you are scanning the entire table..any index is usefull only when you use to eliminate rows..so this is same as query1

Reason for query4 cost more than query 3 may be due to the index you have and the way indexes are stored..For know ,it is enough to know keys are stored at root level and data is stored at leaf level...For more info read this :https://www.sqlskills.com/blogs/kimberly/category/indexes/

For query3,there is only key,so the number of pages required to store the data will be less and thus requires less traversal

For query 4, you have few more columns,thus more pages and more traversal

Below screenshot shows you the pages tmproduct4(18),tmproduct3(15)..so the extra cost may be IO cost required to traverse additional pages

Why index setting is able to affect query cost when scan is imperative

1 Answers1