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

1.pesession (7.3 kB)
avatar image By Joe_Hell 16 asked May 19, 2014 at 04:13 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ May 19, 2014 at 04:34 PM

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

avatar image Joe_Hell May 19, 2014 at 05:40 PM

OS = 2003 r2 sp2 32 gb of RAM dual quad core L5420s SQL Server = 2008 Standard Edition sp1

Number 1 wait is broker transmitter

10|10000 characters needed characters left

1 answer: sort voted first

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)?

avatar image By Aaron Bertrand ♦ 1.7k answered May 19, 2014 at 06:03 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: May 19, 2014 at 04:13 PM

Seen: 214 times

Last Updated: May 22, 2014 at 07:35 AM