Can anybody advise on how to remove the key lookup costing 48.1%

Pearljammer1 2014-07-23 11:12:42

SomewhereSomehow 2014-07-23 11:31:58

Is it a theoretical question or this plan performs slowly?

To avoid Lookup you should add the lookup fields (you may find them in the Output property of the Key Lookup operator) into the index IX_CaseActivity_1.

However, if there is a real performance problem, the cost might not be an indicator of the plan bottleneck. The cost is based on the estimates, if the estimates are wrong the cost is also wrong. To determine the wrong estimates you should get the actual plan and compare actual and estimated row count. If you find huge difference – find the bottom most operator that has these difference, and it would be a good starting point to address the plan problem.

Pearljammer1 2014-07-23 12:20:31
Hi – I did try that adding those columns in the include but I'd mistakenly used the wrong index to add them – now i have added them to the index you have suggested and the key lookup is gone – thanks.
Now that the plan has changed im still however getting the same number of logical reads (roughly 9000) which is why i wanted to tune the query in the first place.
The scan at 10.8% is something i am working on – the table includes nvarchar(max) for a fields of lastname and firstname ( i know, i know i didn's design this !!) so untill I change that I cannot include them in my indexs unfortunately.
Thanks for you help so far with this – this is my first post one here !
Edward Norris 2014-07-23 11:40:17
As SomewhereSomehow stated, the quickest way is to add "Include(DateUpdated, NextActionDate, NextActionID, NextActionPriorityID, Notes)" to the IX_CaseActivity_1. It will obviously have some minor negative time impacts as it has to maintain those fields in the leaf of the index…that is something you have to determine if it is acceptable. I also see numerous scans…but one at 10.8 is very high (doing a left join?)