none
How to evaluate the query execution plan for optimization? RRS feed

  • Question

  • I have a query that's running really slow when I add another tbale to it. I see in the execution plan that the 88% cost is coming from two areas. What I don't know is what this information means relative to improving performance. 
    Tuesday, May 28, 2019 3:35 PM

All replies

  • The percentages in the plan you see are only estimates, even if you have an actual execution plan. I rarely pay attenton to these percentages when looking at the query plan. I'm more interested in the thickness of the arrows.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 28, 2019 9:56 PM
  • Hi David9501,

     

    Since a scan touches every row in the table, whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

     

    Could you please show more information? Also, there is an article about Scans vs. Seeks, please refer to it.

     

    By the way, you can 'SET STATISTICS IO ON; ' to display information regarding the amount of disk activity generated. Please compare the messages and you will see the difference. For more details , you can refer to it: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 29, 2019 3:16 AM
  • These are my statistics, would <g class="gr_ gr_4 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="4" id="4">changing</g> it to a <g class="gr_ gr_3 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="3" id="3">seek scan</g> work more efficiently?

    Table 'soship'. Scan count 1, logical reads 3747842, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'inmastx'. Scan count 5051, logical reads 30491, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'soitem'. Scan count 5051, logical reads 30337, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'sorels'. Scan count 263, logical reads 16365, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'somast'. Scan count 1, logical reads 1383, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'slcdpmx'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'syphon'. Scan count 1, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    • Edited by David9501 Wednesday, May 29, 2019 2:25 PM
    Wednesday, May 29, 2019 2:25 PM
  • Hi David9501,

     

    Thank you for your reply.

     

    Without your script and execution plan, it might be hard to find out a way to improve the performance.

     

    Could you  please share us  your script and execution plan? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 30, 2019 7:42 AM
  • The exectution plan is attached left to right and the third photo is the bottom of the plan. I couldn't fit it all in one image.

    SELECT sorels.fsono,sorels.finumber,sorels.frelease,soitem.fenumber,fshipbook+fshipbuy+fshipmake as totalshipped,soitem.fquantity,sorels.forderqty, somast.fcustno,soitem.fac,soitem.fpartno,soitem.fpartrev,sorels.fduedate, somast.fcompany,slcdpmx.fcusrchr1,somast.fcontact,syphon.fctitle,soitem.fgroup,soitem.fprodcl,soitem.manualplan,inmast.fsource,inmast.fgroup,inmast.fbuyer,inmast.fprodcl, somast.fshipvia,somast.fstatus,somast.fsocoord,somast.fordername,sorels.fshptoaddr, somast.fcustpono,somast.fcusrchr1,somast.fcusrchr2,somast.fcusrchr3,somast.forderdate,sorels.fpostatus, sorels.fshipbook,sorels.fshipbuy,sorels.fshipmake,sorels.fshpbefdue,sorels.fsplitshp,soitem.fshipitem,soitem.fmultiple,sorels.fstatus ,inmast.fonhand, inmast.fonorder, inmast.fproqty FROM sorels LEFT JOIN somast on sorels.fsono=somast.fsono LEFT JOIN soitem on sorels.fsono=soitem.fsono AND sorels.finumber=soitem.finumber LEFT JOIN inmast on soitem.fpartno=inmast.fpartno and soitem.fpartrev=inmast.frev LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid LEFT JOIN soship ON sorels.fsono+sorels.finumber+sorels.frelease=soship.fcsono+soship.fcinumber+soship.fcrelease WHERE somast.fsocoord='IFP' AND LEFT(somast.fstatus,1)<>'C' ORDER BY somast.fduedate, somast.festimator, sorels.fsono, SOITEM.FCFROMNO, SORELS.FDUEDATE

    Right





    • Edited by David9501 Thursday, May 30, 2019 2:47 PM
    Thursday, May 30, 2019 2:43 PM
  • There is not much of a question there.

    When it comes to execution plans, the grapihcs only tells us that much. To be able to say something more useful, we need the XML. It is too big to insert here, but you can upload it somewhere (Google Drive, OneDrive etc) and post the link. Or you can use http://www.brentozar.com/pastetheplan.

    As for your query, I note some issues. You have:

    FROM sorels  LEFT JOIN somast on sorels.fsono=somast.fsono
    But then you have this in the WHERE clause:

    WHERE somast.fsocoord='IFP' AND LEFT(somast.fstatus,1)<>'C'
    I don't know what your intention is, but this effectively tranform the outer join to an inner join, since it will filter out all rows from sorels that did not get a match in somast.

    It certainly helps if there are indexes to support all joins, but then there is this condition:

    LEFT JOIN soship ON  sorels.fsono + sorels.finumber + sorels.frelease =
                         soship.fcsono + soship.fcinumber + soship.fcrelease 
    Maybe this condition is required to get the correct result, but it has the unfortunate effect to prevent index seeks on soship, so this could the cause for any performance problem you may have.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 30, 2019 9:31 PM
  • Hi David9501,

     

    Thank you for your kind reply.

     

    Per your description, I think you might do a little change for your current index. And in your original script , there are many columns in SELECT clause. Please delete the column which you do not need.

     

    According to your execution plan, Key Lookup might cause a performance issue. Key lookups of various flavors occur when the query processor needs to obtain values from columns that are not stored in the index used to locate the rows required for the query to return results. Please try to use INCLUDE to remove it.

     

    There is a similar posting . Please refer to it.

    Eliminate Key Lookup (Clustered) operator that slows down performance

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 31, 2019 7:22 AM
  • I was able to get the query performance from 4:07 to 0:03. I changed somast.fstatus NOT Like 'C%'  to get better indexing and removed the ORDER BY and performed my sorting in SSRS.

    New Statistics

    able 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'soship'. Scan count 1, logical reads 746, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'inmastx'. Scan count 4809, logical reads 29023, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'soitem'. Scan count 4809, logical reads 28887, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'sorels'. Scan count 248, logical reads 15578, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'somast'. Scan count 1, logical reads 1353, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'slcdpmx'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'syphon'. Scan count 1, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.





    • Edited by David9501 Friday, May 31, 2019 2:47 PM
    Friday, May 31, 2019 2:44 PM
  • Hi David9501,

     

    Thank you for your kind reply.

     

    I'm sorry that if you don't share with us the full implementation plan and your existing index, we could not have a good direction for your problem.

     

    By the way , the query with NOT LIKE is not using any index because the optimizer decides that it is cheaper to do a sequential scan. The condition omast.fstatus NOT Like 'C%'  will probably keep a very small number of rows (say 1%), so it is good to find this small number by using an index.

     

    Hope it can help you .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 3, 2019 9:14 AM