mjhjmtu

Given the next example:

IF OBJECT_ID('dbo.my_table') IS NOT NULL
 DROP TABLE [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
 [id] int IDENTITY (1,1) NOT NULL PRIMARY KEY,
 [foo] int NULL,
 [bar] int NULL,
 [nki] int NOT NULL
);
GO

/* Insert some random data */
INSERT INTO [dbo].[my_table] (foo, bar, nki)
SELECT TOP (100000)
 ABS(CHECKSUM(NewId())) % 14,
 ABS(CHECKSUM(NewId())) % 20,
 n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM 
 sys.all_objects AS s1 
CROSS JOIN 
 sys.all_objects AS s2
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
 ON [dbo].[my_table] ([nki] ASC);
GO

If I fetch all records ordered by [nki] (Non-clustered index):

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 493 ms

Optimiser choose the clustered index and then applies a Sort algorithm.

Execution plan

But if I force it to use the non-clustered index:

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 311 ms, elapsed time = 188 ms

Then it uses non-clustered index with a Key Lookup:

Execution plan

Obviously if the non-clustered index is transformed into a covering index:

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
 ON [dbo].[my_table] ([nki] ASC)
 INCLUDE (id, foo, bar);
GO

Then it uses only this index:

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 32 ms, elapsed time = 106 ms

Execution plan

Question

• Why SQL Server use the clustered index plus a sort algorithm instead of non-clustered index even if execution time is 38% faster?

If you were to compare the number of reads required in 100,000 lookups with what’s involved in doing a sort, you might quickly get an idea about why the Query Optimizer figures that the CIX+Sort would be the best choice.

The Lookup execution ends up being quicker because the pages being read are in memory (even if you clear the cache, you have a lot of rows per page, so you’re reading the same pages over and over, but with different amounts of fragmentation or different memory pressure from other activity, this might not be the case). It really wouldn’t take all that much to have CIX+Sort go quicker, but what you’re seeing is because the cost of a read doesn’t take into consideration the relative cheapness of hitting the same pages repeatedly.

Why SQL Server use the clustered index plus a sort algorithm instead
of non-clustered index even if execution time is 38% faster?

Because SQL Server uses a cost-based optimizer based on statistics, not runtime info.

During the cost estimation process for this query, it does actually evaluate the lookup plan, but estimates it will take more effort. (Note the "Estimated Subtree Cost" when hovering over SELECT in the execution plan). That's not necessarily a bad assumption either - on my test machine, the lookup plan takes 6X the CPU of the sort/scan.

Look to Rob Farley's answer as to why SQL Server might cost the lookup plan higher.