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 executed and results updated in below Document.
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
Attachments:
You must be
logged in to view attached files.