Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
@gbn: added the link –  Tim Feb 15 '11 at 10:31
    
I found it thanks –  gbn Feb 15 '11 at 10:36
add comment

migrated from stackoverflow.com Jan 26 '12 at 14:32

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

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)

share|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
    
@Tim - BTW these figures can be woefully innacurate for certain constructs. You are always better looking at actual reads, cpu etc in assessing different options. –  Martin Smith Feb 15 '11 at 11:01
show 6 more comments

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.

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

Example StartCapture Definition

CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
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

CREATE  PROCEDURE StopCapture
@TraceID INT
AS
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), 
      CTE
     as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
                ObjectID,
                ObjectName,
                EventSequence,
                /*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)),
                   ObjectID,
                   ObjectName,
                   EventSequence)
SELECT ObjectName,
       SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM   CTE
GROUP  BY ObjectID,
          ObjectName  

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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