Search code examples
sql-serverstored-proceduresoptimizationssms

How can I display the execution plan for a stored procedure?


I am able to view the Estimated Execution Plan (Management Studio 9.0) for a query without a problem but when it comes to stored procedures I do not see an easy way to do this without copying the code from the ALTER screen and pasting it into a query window, otherwise it will show the plan for the ALTER and not the procedure. Even after doing this, any inputs are missing and I would need to DECLARE them as such.

Is there an easier way to do this on stored procedures?

Edit: I just thought of something that might work but I am not sure.

Could I do the estimated execution plan on

exec myStoredProc 234

Solution

  • SET SHOWPLAN_ALL ON
    GO
    
    -- FMTONLY will not exec stored proc
    SET FMTONLY ON
    GO
    
    exec yourproc
    GO
    
    SET FMTONLY OFF
    GO
    
    SET SHOWPLAN_ALL OFF
    GO