Estimated Execution Plan - interpreting the results

  • david_h_edmonds

    Ten Centuries

    Points: 1232

    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?

    Estimated execution plan

     

  • DinoRS

    SSCrazy

    Points: 2273

    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

    Ten Centuries

    Points: 1232

    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: 395211

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • david_h_edmonds

    Ten Centuries

    Points: 1232

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

     

    Cheers,

     

    Dave

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

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