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

Question

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 1

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

Solution 2

Select the storedprocedure name (just type it in a query window), right click, and choose the 'Display Estimated Execution Plan' button in the toolbar of SQl Server Mgmt Studio. Note that you don't have to have the stored procedure code open. Just the procedure name has to be selected.

The plan for the stored procedure from with in the called procedures will also be displayed in graphical form.

Solution 3

When executing a stored procedure in SQL Management Studio 2008 you can click Query -> Include Actual Execution Plan from the menu...its also on the tool bar

After reading through the comments executing seems to be an issue and to solve this issue i would recommend wrapping the execution of the stored procedure in a transaction rolling it back at the end

Solution 4

Use

SET SHOWPLAN_ALL ON
Go
exec myStoredProc 234
GO
SET SHOWPLAN_ALL OFF
GO

See http://msdn.microsoft.com/en-us/library/aa259203.aspx As long as you aren't using tmp tables i think this will work

Solution 5

I know answer was submitted a while ago but I find query below useful

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT  [ProcedureName]          =   OBJECT_NAME([ps].[object_id], [ps].[database_id]) 
       ,[ProcedureExecutes]      =   [ps].[execution_count] 
       ,[VersionOfPlan]          =   [qs].[plan_generation_num]
       ,[ExecutionsOfCurrentPlan]    =   [qs].[execution_count] 
       ,[Query Plan XML]         =   [qp].[query_plan]  

FROM       [sys].[dm_exec_procedure_stats] AS [ps]
       JOIN [sys].[dm_exec_query_stats] AS [qs] ON [ps].[plan_handle] = [qs].[plan_handle]
       CROSS APPLY [sys].[dm_exec_query_plan]([qs].[plan_handle]) AS [qp]
WHERE   [ps].[database_id] = DB_ID() 
       AND  OBJECT_NAME([ps].[object_id], [ps].[database_id])  = 'TEST'

View more solutions