Execution Plan

Question

Execution Plan

Rechana Rajan

SSCertifiable

Points: 7721
More actions
June 27, 2014 at 2:50 am

#308498

Hi Experts,
How can i get the execution plan of a new procedure like the one below.
CREATE PROCEDURE [dbo].[EP]
(
@ListXML xml = ''
)
AS
BEGIN
SET NOCOUNT ON
SELECT
[ATID]
, [ATNumber]
, [SerialNumber]
, [InstallDate]
, [ModelID]
, [AccountID]
, [IPAddress]
, [IsNetworked]
FROM
[dbo].[EP_TB] a WITH (NOLOCK) INNER JOIN @ListXML.nodes('/Assets/Asset') R(ref)
ON a.ATid = ref.value('@id', 'UNIQUEIDENTIFIER')
END
GO
Also help me to get the details like IO, CPU and Execution times for existing and new procedure.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

There are two kinds of execution plans, estimated and actual. To get an estimated plan inside SQL Server Management Studio, you simply type:

EXEC myNewProc 'myparameter';

Highlight that command and within Management Studio hit CTL-L or click on the Show Estimated Plan button. That will show the plan. To get an actual plan you have a toggle button or you hit CTL-M. Then you execute the command. It will run the query and return the plan.

You can also use extended events to capture execution plans. You can also SET STATISTICS XML to capture an actual plan or SET SHOWPLAN_XML to capture an estimated plan.

To get I/O & CPU, right click in the query window, select Advanced, the select STATISTICS IO and STATISTICS TIME. Or, you can use T-SQL to:

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

You can also capture the query metrics using extended events in 2008 or better or trace events in 2005 or older. Finally there are the Client Statistics. Right click in a query window and select them from the context menu.

For tons more details on al this, see the books in my signature lines below.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rechana Rajan SSCertifiable Points: 7721 More actions · Answer 2

Thanks a lot Grant.

When i pint over an item in Estimated execution plan(eg:Table valued function) i am getting the estimated number of executions =200?

What exactly this means and is this value same for the total script??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The optimiser estimated that the operator in question would execute 200 times during the course of the query's execution. It's an estimation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 4

That's an indication the optimizer thought that operator would be executed 200 times. It may or may not be depending on the accuracy of your statistics. You can run the query and compare the actual executions to the estimated executions to get an idea of that accuracy. Just remember that the estimated costs are just that, estimates. They're not actual measures of runtime values.

For a lot more details on how to read execution plans, I'd suggest getting a copy of my book on execution plans. You can download it for free or buy the paper version. Just make sure you get volume 2. Volume 1 wasn't as accurate.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning