Comparision between A select statement and Multiple select statment that act as batch

I have a stored procedure that contains some Select statements that return union result of them.

I think and write one select instead of several Select and want to compa开发者_运维知识库re EXECUTION PLAN of them (SP and one Select statement).

the problem is when run this twe statement(SP and Select) all select statement in sp have their execution plan.I want to have execution plan of SP as an UNIT.

I have another question.Consider execution plan that attached to this post.Is sum cost of an execution plan for all statement must be 100%?

But why sum cost of all statement of this execution plan not be 100%?

Execution plan

thanks

If you are trying to compare two versions of the logic then a script along the lines of:

Exec <storedProc>

Select <data> From <tables>

Will let you do it. Yes the execution plan will show you all of the queries inside the stored proc. Look for the plans for the queries that occur outside of the SP. If they give a total cost > 50% then the SP performed better.

As a side note, if your SP takes parameters make sure you compare the execution plans across a range of parameter values.

A UNION is several select statements. You are essentially saying "Show me the results from this query, joined with the results from this query, joined with the results from this query..." How else will SQL Server get the results from those queries without executing them?