How can I display the execution plan for a stored procedure?
SET SHOWPLAN_ALL ONGO-- FMTONLY will not exec stored procSET FMTONLY ONGOexec yourprocGOSET FMTONLY OFFGOSET SHOWPLAN_ALL OFFGO
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.
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