How can I improve this plan. Table Insert is costing the most at 84.6% - can it be improved ?

tmp1AE3.pesession (131.0 kB)
avatar image By Pearljammer1 31 asked May 05 at 08:32 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ May 05 at 12:56 PM

Well, an insert costs what it costs. You can sometimes reduce the cost by doing things like eliminating indexes, but it doesn't seem like that's an issue here. It would be great to see an actual plan generated from within Plan Explorer so we can see things like duration. My guess is that while that has a seemingly high I/O cost, the relative cost % is not a meaningful metric.

10|10000 characters needed characters left

1 answer: sort voted first

Hi Aaron,

I have re run the query from with PE like you said. I didn't even know you could do this - so much more info available. I have attached the plan as requested....

Thanks, link text

avatar image By Pearljammer1 31 answered May 05 at 01:51 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ May 05 at 08:35 PM

Seems to me that most of the work is spent doing all those clustered index scans, which contribute to a high number, not the insert itself. Have you considered indexing a persisted computed column like UnsafeConditionsObserved + SuggestedSolution, and then a filtered index against computedcolumn > ''? Just one example that might avoid scanning PC_Core_UnsafeConditionsObserved (the benefit will depend on how many rows have a value in either of those columns).

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

Topics:

x588
x405
x107
x105

asked: May 05 at 08:32 AM

Seen: 135 times

Last Updated: May 05 at 08:35 PM