June 27, 2014 at 2:50 am
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.
June 27, 2014 at 4:37 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 27, 2014 at 6:44 am
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??
June 27, 2014 at 6:51 am
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
June 27, 2014 at 6:53 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply