way to improve the nested lookups? or is should I be looking elsewhere

Joe_Hell 2014-05-19 16:13:15

Aaron Bertrand 2014-05-19 16:34:45
Any way you could share a less- or non-anonymized version of statement 3? While I can see the table relationships in the join diagram, anonymizing the query plan also has the effect of removing the entire statement. You can e-mail this to me (abertrand at sqlsentry dot com) if you don't want to share it here. Thanks! A
Joe_Hell 2014-05-19 17:40:54
OS = 2003 r2 sp2 32 gb of RAM dual quad core L5420s
SQL Server = 2008 Standard Edition sp1

Number 1 wait is broker transmitter

Aaron Bertrand 2014-05-19 18:03:17
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)?