No perceived improvement even after adding index

Yaraj 2015-07-20 14:35:50

To try to improve performance, I added an index to an exisitng query which did a clustered index scan (table scan) and a sort. I added all the columns required by the query including the sort columns in the correct sequence. Now the query uses that index, but still does an index scan and not a seek on the status field. It also does the sort. I was hoping to gain some performance but I think not. What all am I missing here and doing wrong. Please help.

Table and index are here.

Aaron Bertrand 2015-07-20 14:48:52
The query only yields 5 rows, so it is probably more efficient to perform a scan than to perform 5 seeks (well, probably it would be labeled as a seek which would technically be a range scan anyway). While I'm not convinced your index keys match the sort order defined in the query, I can't imagine the runtime of this query is a problem (you didn't generate the plan from within Plan Explorer, so I don't know what the runtime metrics were). It's important to remember that something in every plan will always be the most expensive operator – it doesn't mean every plan is a problem or needs further optimization. If you can include the table structure and the definition of the index IX_LAScheduleInventoryBuild we can probably comment further on why it is using a scan + sort instead of an ordered scan.
Yaraj 2015-07-20 15:04:40
Thanks Aaron for the quick response. link textThe table definition and index that was added is attached in the file. What should be the correct index to meet the sort order in the query if that could eliminate the sort?

Aaron Bertrand 2015-07-20 15:26:04
This index would get rid of the sort, but I suspect it will still use a scan:

ON dbo.LAScheduleInventoryBuild(StartHour, StartMinute, Status) 

Whether that's a useful or optimal index for the rest of your workload, not sure. A filtered index may be useful as well, if <> x is consistent.

Yaraj 2015-07-20 15:49:41
That's awesome! It did indeed removed the sort. My workload as of now has only 5 rows, but is expected to quickly build upto hundreds of rows. That simple query took as much as 1 second with just 5 rows, so was very discerning if the table grows. <> x will be consistent and a filtered index is something I need to know better. But thanks for all your help, so happy I have learnt yet something new from your site!