Post reply

Query execution plan

  • June 17, 2015 at 3:35 am

    #299064

    Hi Guys,

    Is it possible to check query execution plan of a store procedure from create script (before creating it)?

    Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

    Thanks.

  • June 17, 2015 at 3:44 am

    #1805984

    I don't see the problem: create it, grab the execution plan, then drop it.

    If it overwrites an existing procedure, create it with a different name.

    -- Gianluca Sartori

  • June 17, 2015 at 3:51 am

    #1805989

    Thanks for your reply Gianluca.

    Yes, that's one option. However we generally have to go through change request process to make any changes and every changes are audited at the database level as well. I guess if there's no way to check that, I'll have to go through that bureaucratic process.

    Thanks

  • June 17, 2015 at 3:54 am

    #1805990

    You could also create a temporary stored procedure (just prepend # to the proc name).

    -- Gianluca Sartori

  • June 17, 2015 at 4:17 am

    #1805997

    Okay, thanks. So bottom line is that procedure has to be created to look at the execution plan, right?

  • June 17, 2015 at 4:24 am

    #1806000

    That's how it works

    -- Gianluca Sartori

  • June 17, 2015 at 4:28 am

    #1806002

    Understood, thanks once again for your time Gianluca.

  • June 17, 2015 at 4:33 am

    #1806008

    Any time

    -- Gianluca Sartori

  • June 17, 2015 at 6:55 am

    #1806049

    salamlemon (6/17/2015)

    Okay, thanks. So bottom line is that procedure has to be created to look at the execution plan, right?

    Yes, but no. If there are no parameters, you can capture the estimated plan(s) by selecting everything within the bounds of the procedure and clicking on the estimated plan button. If there are parameters, you have to change them to DECLARE then set values, otherwise the same. Technically you're not adding a new stored procedure nor running any statements which could change the database state.

    If you want to capture the actual execution plan of the stored procedure and especially if the code contains INSERT/UPDATE/DELETE statements, then I find it easiest to create it then run it in a transaction like this:

    BEGIN TRAN

    run stored procedure

    ROLLBACK TRAN

    Something between these two should work for you.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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