With the query in hand (well, part of it, since it was too large to be fully included in the plan XML) I would say the plan is complex, but pretty efficient, with the exception that I don't really know if estimated rows and actual rows are comparable (seems to be an estimated plan). You could consider adding Table5.Column18 to the index key (or it may be better as a filtered index), and Table5.Column4 as an INCLUDE column, to Index13. This would eliminate the key lookup, but this is unlikely to solve your problem of occasional delays. Instead I would examine exactly what is blocking during those times - when it is slow, what exactly is the query waiting on? Are the page/table locks on any or all of the source tables? Can you consider technologies meant to prevent writers from blocking readers (such as RCSI)?
By Aaron Bertrand ♦ 1.7k answered May 19, 2014 at 06:03 PM