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

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

海之角 发布于 2021-11-26 字数 679 浏览 807 回复 9 原文

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

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(9

A君 2022-06-07 9 楼

Here's a screenshot.Took me a while to figure out where to look for.

enter image description here

花辞树 2022-06-07 8 楼

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.

沉溺在你眼里的海 2022-06-07 7 楼

Running the stored procedure in management studio (or query analyser) with show actual execution plan (from the query menu) enabled will show you the plan for the stored procedure after you have run it. If you cant run it there is show estimated execution plan (though in my experience that is often less accurate.)

眼眸印温柔 2022-06-07 6 楼

There are quite a few ways to get the actual execution plan of a stored procedure.

SELECT
qp.query_plan, 
SQLText.text
FROM sys.dm_exec_cached_plans AS CP
 CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
 CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
 WHERE objtype = 'Proc' and cp.cacheobjtype = 'Compiled Plan'

looking at the plans on a production server with the statistics of the data in the production server may show a different plan then of a dev box with a smaller dataset.

There is a lot more data to look at, like how often is a procedure executed according to query cache

SELECT
    qp.query_plan, 
    CP.usecounts as [Executed], 
    DB_name(QP.dbid) as [Database],
    OBJECT_NAME(QP.objectid) as [Procedure],
    SQLText.text as [TSQL],
    so.create_date as [Procedure Created],
    so.modify_date as [Procedure  Modified]
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
join sys.objects as so on so.[object_id]=QP.objectid
WHERE objtype = 'Proc' and cp.cacheobjtype = 'Compiled Plan'

The XML query plan (the first column in both queries), contains the XML of the execution plan allowing you, in SSMS to click on it and view the actual plans but also allows you to scan for things you do not like to have like index scan or "god forbid" table scans.

SELECT
    qp.query_plan, 
    CP.usecounts as [Executed], 
    DB_name(QP.dbid) as [Database],
    OBJECT_NAME(QP.objectid) as [Procedure],
    SQLText.text as [TSQL],
    so.create_date as [Procedure Created],
    so.modify_date as [Procedure  Modified]
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
join sys.objects as so on so.[object_id]=QP.objectid
WHERE objtype = 'Proc' and cp.cacheobjtype = 'Compiled Plan'
and cast(qp.query_plan as nvarchar(max)) like '%loop%'

I sample this using a really bad way by casting the XML to string and then doing a wildcard search however XML queries are not things most do every day and string wildcards are easy for everyone.

风为裳 2022-06-07 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'
天赋异禀 2022-06-07 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

挖鼻大婶 2022-06-07 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

维持三分热 2022-06-07 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.

如歌彻婉言 2022-06-07 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