Select Query slow for Non-clustered index I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table  compared to table without index.
Query Used:
select * from table order by customer_name

It is  too slow (more than 1 minute for 40000 rows)
Error was due to non-clustered index , without index  query executed in 5 sec.
How did indexing caused  problem?
The query plan:
For without index is : https://www.brentozar.com/pastetheplan/?id=rJZtQfltv
For with index is : https://www.brentozar.com/pastetheplan/?id=Bkrg8zgKD
Queries with Different conditions are executed and results updated.
Query plan  for Query  with order by : https://www.brentozar.com/pastetheplan/?id=ByOxkbpYP
Query plan  for Query  without order by : https://www.brentozar.com/pastetheplan/?id=S1WB1bpYD
Summery :

Column [sl_no] is clustered index
Column [Customer_Name] is non-clustered index
For the queries column with large text used
The elapsed time for  :


With Order by Query1 - 12ms
With Order by  Query2- 1sec
Without Order by Query1-0ms
Without Order by Query2- 3sec
Query with order by with non clustered index on column -5 sec
Because when your query using a non-clustered index (not covering every column), SQL Server finds your data with key lookup. This is more work than scan the entire clustered index.
SQL Server has a cost-based optimization. So, it compares two execution plans and chooses the less coster. But sometimes (depends on your statistics), it does wrong chooses.
Also, check out the tipping point good blog post from Brent Ozar.
If you change your query with a less SELECT col1,col2 than SELECT * and add columns on the non-clustered index as an include which are selecting, you can get rid of from key lookup operation. But, in your situation in which there is no condition, actually no need for the non clustered index.
I create table,index and write a query just like you. As you can see the cost of queries, a non-clustered index+key lookup more than the clustered index scan.


First Query:

SQL Server Execution Times:    CPU time = 32 ms,  elapsed time = 241
ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
time = 0 ms.

Second Query:

SQL Server Execution Times:    CPU time = 125 ms,  elapsed time = 245
ms. SQL Server parse and compile time: