How can I display the execution plan for a stored

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

Answer 1

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.

Answer 2

You can also use Profiler to see the execution plan. You'll want to include the Performance : Show Plan Statistics Profile option and be sure to inlcude Binary Data in your columns.

You can then run any query or procedure and see the execution plan.

Edit

If you can't use profiler, and you don't want to open another window I suggest that you include a comment block at the begining of your stored procs. For example imagine the following:

/* 
     Description: This procedure does XYZ etc...
     DevelopedBy: Josh
     Created On:  4/27/09

     Execution: exec my_procName N'sampleparam', N'sampleparam'
*/

ALTER PROCEDURE  my_procName
   @p1 nvarchar(20),
   @p2 nvarchar(20)

AS

What this allows is that you can highlight just the execution purpose and turn on show execution plan. And run it.

Answer 3

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

Answer 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

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

Answer 6

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