Would adding an Index on the AddedDate column help minimize the Sort 70.4% impact

Yaraj 2014-08-21 21:06:49

The table dbo.tblhall_contacts_ajax_search_lkp LKP has a surrogate PK and no other indexes. Wondering adding an index on the ChildID and ParentID columns on LKP would also help.

Aaron Bertrand 2014-08-28 15:56:02
Are those columns 22 and 23 in table 7? If so, then yes, an index on those two columns (either ix(col22,col23) or ix(col22) include(col23)) should allow them to be pulled from the index without an additional sort operator. That said, these columns are ultimately pulled from a table scan – is there a reason there is no clustered index on this table?

What other changes that may make to the plan (I didn't inspect where else that index might be used, if it exists) and of course the impact that will have on the DML portion of your workload is a different issue altogether.

Yaraj 2014-08-28 18:44:57
I am afraid I lost the original plan because I anonymized it. I am trying to get an answer from the original developers as to why it was created as a heap table. I think there is no reason to, and this column being widely used I believe as a search criteria, I will try to add a non-clustered index on it. I suspect the table is mainly used as some sort of a parent-child link table to facilitate ajax driven queries from the front end. I will test some as soon as I get a chance and post back my results. Thanks for your help.