I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan.

MSDN states the following:

If the statement is a stored procedure or Transact-SQL statement, it becomes the root of the graphical execution plan tree structure. The stored procedure can have multiple children that represent statements called by the stored procedure. Each child is a node or branch of the tree.

In this case I expect to run a stored procedure and see only one tree where the root node is the stored procedure.

How it is possible to switch on such plan visualization for a stored procedure?

UPDATE: It looks like an estimated subtree cost is not the same for actual and estimated execution plans.

Is there a way how one can see Estimated execution cost for the whole stored procedure and not only for its statements?

  • @gbn: added the link – Tim Feb 15 '11 at 10:31

The Estimated plans (CTRL + L) show a tree structure. Actual plans (CTRL + M) do not.

I've never seen a tree for a procedure in an actual plan (and I never use estimated plans anyway)

| improve this answer | |
  • @gbn: sounds dissapointing.... – Tim Feb 15 '11 at 10:41
  • @gbn: the reason why I want to have that is to see "Estimated query subtree cost" of a procedure execution: ToolTip on the left most operator shows this. Do you probably know some other methods? – Tim Feb 15 '11 at 10:43
  • You will see this for Estimated plans – gbn Feb 15 '11 at 10:46
  • Ok, that's the answer and +1. – Tim Feb 15 '11 at 10:52
  • @Martin: As I understand Estimated subtree cost is exactly the number which optimizer looks at in order to choose the plan. For which constructs they are inaccurate? – Tim Feb 15 '11 at 11:04

Is there a way how one can see Estimated execution cost for the whole stored procedure and not only for its statements?

The only way I think you can do this is by capturing the XML plans and inspecting these (aggregating the value for the individual plans). Capturing these plans is only possible by a trace as far as I know so you could create a couple of helper stored procedures to manage the setup and termination of the trace and sandwich your actual stored procedure call inbetween.

EXEC StartCapture @@SPID, @TraceID OUTPUT
EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/
EXEC StopCapture @TraceID

Example StartCapture Definition

@Spid INT,
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)

EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL 

exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1

Example StopCapture Definition

@TraceID INT
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), 
     as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
                /*costs accumulate up the tree so the MAX should be the root*/
                MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
         FROM   fn_trace_getinfo(@TraceID) fn
                CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
                CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
                CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
                                            'float') AS EstimatedTotalSubtreeCost
                             FROM   xPlan.nodes('//sql:RelOp') T(relop)) ca
         WHERE  property = 2
                AND TextData IS NOT NULL
                AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
         GROUP  BY CAST(TextData AS VARCHAR(MAX)),
SELECT ObjectName,
       SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost

-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.