1

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 :

  1. Column [sl_no] is clustered index
  2. Column [Customer_Name] is non-clustered index
  3. For the queries column with large text used
  4. 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

1 Answer 1

1

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.

enter image description here

enter image description here

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.