estimated execution plan

  • sizal0234

    SSCrazy

    Points: 2194

     

    I have an SP which is truncating a table and then insert into and selecting again.  Now, I am planning to tune this SP. if I decide to view the estimated execution plan then is it a safe action or will it change any data, I know actual execution plan will change. Please let me know your thoughts.

    Ok, I was able to generate the estimated plan, I see the plan is broken into multiple parts, that could be due to the code. However can we change it to display like a continuous flow. Broken plan does not make much sense.  attached picture for your reference.

    Also, the query cost steps in execution plan do not display much, is it like I have to generate actual plan for more information>?

    sorry about attaching multiple images, I am unable to delete the extra image.

    Thanks!

     

    Attachments:
    You must be logged in to view attached files.
  • Chris Harshman

    SSC-Forever

    Points: 42108

    this looks like the execution plan for creating a bunch of stored procedures, not the execution of a stored procedure.  Did you run the estimated plan against the stored procedure script, a call to the stored procedure, or the body of the stored procedure directly as a script?

  • sizal0234

    SSCrazy

    Points: 2194

    Yes, I selected the code generated using sp_helptext ...and then for that code, I generated the estimated plan.

  • Phil Parkin

    SSC Guru

    Points: 244584

    Do you understand why there would be a difference between the execution plans for

    CREATE Proc x AS

    Select * from tab1

    and

    Select * from tab1

    ? The first is pretty much useless, it's the second which is interesting.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Grant Fritchey

    SSC Guru

    Points: 396558

    So, you're looking at the markers that SQL Server creates when you ask for an execution plan for Data Definition Language (DDL). There are no execution plans created for DDL. However, when you request a plan, SQL Server has to show you something. So it shows this. This is not the same as an execution plan.

    If you want to see the plan for your procedure then do this:

    EXEC dbo.MyProc @MyParameter = '42';

    Highlight that code and ask for what's called an "Estimated Plan". You'll get an execution plan, or plans, based on the query, or queries, within the procedure.

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

  • sizal0234

    SSCrazy

    Points: 2194

    Great, Thanks!! yes, I was able to do SP call in the test environment and generate the actual plan. Attached. Please let me know your thoughts, I do not want to rush on missing IDX hints, also the SP call is processing around 150000 rows...the good things is sp call took only 03 secs..which is nice.

    Attachments:
    You must be logged in to view attached files.
  • Grant Fritchey

    SSC Guru

    Points: 396558

    sizal0234 wrote:

    Great, Thanks!! yes, I was able to do SP call in the test environment and generate the actual plan. Attached. Please let me know your thoughts, I do not want to rush on missing IDX hints, also the SP call is processing around 150000 rows...the good things is sp call took only 03 secs..which is nice.

    Glad to hear it's running fast.

    However, I'm sorry, but I can't help. An execution plan isn't a picture. The details that determine what's going on are all in the properties behind the picture. You'd need to post the plans as an XML file for any of us to be able to comment on them. However, if you want to protect the information in the plan, you should get a copy of Plan Explorer from SentryOne. It's free. It has a way to anonymize the plan. Run through that before you post the plan.

    Sorry to make this difficult. There's just no way to say anything about a plan based on a picture of it.

    By the way, if you want to learn more about execution plans, look down at my signature. That book on execution plans is free to download.

     

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

  • sizal0234

    SSCrazy

    Points: 2194

    Thanks! I do have the sql sentry ...so let me try to anonymize the plan ....this is a great tip!. Yes, I will be downloading the book and thanks for sharing the free version.

     

  • sizal0234

    SSCrazy

    Points: 2194

    I was able to attached the plan, please look at the actual plan and let me know your thoughts. Thanks!

    • This reply was modified 1 month ago by  sizal0234.
    Attachments:
    You must be logged in to view attached files.
  • Grant Fritchey

    SSC Guru

    Points: 396558

    sizal0234 wrote:

    I was able to attached the plan, please look at the actual plan and let me know your thoughts. Thanks!

    Not sure what you uploaded, but it's not a .sqlplan file that I can open in SSMS.

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

  • sizal0234

    SSCrazy

    Points: 2194

    Sorry for the inconvenience, I uploaded the zipped format of sql sentry cost explorer plan. So, I think you might need to view it.

  • Chris Harshman

    SSC-Forever

    Points: 42108

    Looking at your pesession file, I see the first query is doing an index scan on Object12 and Object13, then hash matching them together.  The reads of Object12 seem to be a large part of the IO cost.  Is there an appropriate index on Object12 that matches whatever filtering criteria there is?

    In query 2, we see that Object12 is a problem again, it looks like it did a full scan to return 1 row.  Query 3 looks similar to Query 1, and is constrained by reading Object12 as well.

    How many rows are in Object12 and what indexes are on it?  I notice that Object12 joins to Object13 on Column19,  is Column19 indexed in both tables?  Considering that is the largest join in 2 of the queries, it's a place to start for optimization.

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

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