Estimated Execution Plan - interpreting the results

Question

Estimated Execution Plan - interpreting the results

david_h_edmonds

Default port

Points: 1430
More actions
April 18, 2019 at 7:57 am

#3446057

Hi all,
Having used SQ server daily for many years, I have finally found the need/time to use execution plans in some more complex code.
The below is my estimated execution plan for a view that contains 4 left outer joins and multiple currency conversions based on the results of those joins.
It appears I have a sort that is costing 78% whilst most other steps don't get over 4%.
I am wanting to tune the query to relieve this issue but unsure as to what will make a difference.
Can anyone offer any advice please?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DinoRS SSCrazy Points: 2683 More actions · Answer 1

A reduction in Cost for Sort Operations would be to bring the columns being sorted in the right order by an appropriate index so the sort actually doesn't have to happen.

david_h_edmonds Default port Points: 1430 More actions · Answer 2

Hi Dino, thanks for the reply.

I actually ended up simplifying the left outer joins as there were some casts and conversions on one of the sides. As I control both tables in the join, I was able to add in a computed column to the reference table that removed the need for the converts/casts in the join.

The nett result is that the that view was searching circa 280,000 records in about 30 minutes is now running in under 3 minutes. I may add the index you suggested as well and see if I can improve further.

Best wishes,

Dave

Grant Fritchey SSC Guru Points: 398230 More actions · Answer 3

Sounds like you're progressing well. I can't see the execution plan to make any suggestions. Also, an execution plan isn't a picture. It's all the data in the properties behind the picture. If you really want some concrete suggestions, post the full plan here.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software

david_h_edmonds Default port Points: 1430 More actions · Answer 4

Hi Grant, that's really kind thanks. What format would you prefer the plan in? Text in an Excel file?

Cheers,

Dave