February 8, 2011 at 1:40 pm
I am in the middle of performance tuning of a proc. I am capturing the execution plan from trace and i have also attached my trace definition file. However when i enable include exec plan it is totally different from the exec plan from the trace. I am relying more on trace but can someone please confirm as to which one is more reliable?
February 8, 2011 at 2:44 pm
Assuming you're getting an actual execution plan from the trace or through management studio, they're the same because they're coming from identical sources. Neither does anything that the other does not. If you're getting different actual execution plans, it's because something is causing the plans to be different, parameters, statistics, databases, code changes, ANSI settings, any or all of these can lead to differences in execution plans.
It gets tougher if you're trying to compare actual plans to estimated plans. It's possible through recompiles & other events for the two plans to differ.
But there is no fundamental difference in what is being gathered.
"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
February 8, 2011 at 2:54 pm
Grant Fritchey (2/8/2011)
Assuming you're getting an actual execution plan from the trace or through management studio, they're the same because they're coming from identical sources. Neither does anything that the other does not. If you're getting different actual execution plans, it's because something is causing the plans to be different, parameters, statistics, databases, code changes, ANSI settings, any or all of these can lead to differences in execution plans.It gets tougher if you're trying to compare actual plans to estimated plans. It's possible through recompiles & other events for the two plans to differ.
But there is no fundamental difference in what is being gathered.
Forgot to add one thing, my procedure using bunch of functions also. Enabling actual execution plan form management studio doesn't give the plan for function and as far as i know can only be achieved from trace. Is that the reason why it is different?
February 8, 2011 at 3:32 pm
Two updates on the same question,
i) In my proc i have the following options set up
Set ANSI_NULLS ON
go
SET QUOTED_IDENTIFIERS ON
go
Set Transaction isolation level read uncommitted
SET NOCOUNT ON
ii) This article "http://www.simple-talk.com/sql/performance/execution-plan-basics/" mentions that using " Showplan XML Statistics Profile " event gives actual execution plan for each query.
P.S: Please refer to my post before answering. Thanks !!
February 8, 2011 at 3:58 pm
sqldba_icon (2/8/2011)
Two updates on the same question,i) In my proc i have the following options set up
Set ANSI_NULLS ON
go
SET QUOTED_IDENTIFIERS ON
go
Set Transaction isolation level read uncommitted
SET NOCOUNT ON
ii) This article "http://www.simple-talk.com/sql/performance/execution-plan-basics/" mentions that using " Showplan XML Statistics Profile " event gives actual execution plan for each query.
P.S: Please refer to my post before answering. Thanks !!
Yes,Show Plan XML Statistics Profile shows the actual plan.
The UDF's would explain it, yes. If you dig around a bit in the trace, I'm sure you'll see the same plan as that shown within Management Studio.
"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
February 8, 2011 at 5:47 pm
Grant Fritchey (2/8/2011)
sqldba_icon (2/8/2011)
Two updates on the same question,i) In my proc i have the following options set up
Set ANSI_NULLS ON
go
SET QUOTED_IDENTIFIERS ON
go
Set Transaction isolation level read uncommitted
SET NOCOUNT ON
ii) This article "http://www.simple-talk.com/sql/performance/execution-plan-basics/" mentions that using " Showplan XML Statistics Profile " event gives actual execution plan for each query.
P.S: Please refer to my post before answering. Thanks !!
Yes,Show Plan XML Statistics Profile shows the actual plan.
The UDF's would explain it, yes. If you dig around a bit in the trace, I'm sure you'll see the same plan as that shown within Management Studio.
Unfortunately the place where i am looking at,in the management studio it shows index seek and in the plan from trace shows index scan. I am not worried why the plan is different. I just want to knoww can i rely more on the exec plan from trace than management studio?
February 9, 2011 at 4:30 am
sqldba_icon (2/8/2011)
Grant Fritchey (2/8/2011)
sqldba_icon (2/8/2011)
Two updates on the same question,i) In my proc i have the following options set up
Set ANSI_NULLS ON
go
SET QUOTED_IDENTIFIERS ON
go
Set Transaction isolation level read uncommitted
SET NOCOUNT ON
ii) This article "http://www.simple-talk.com/sql/performance/execution-plan-basics/" mentions that using " Showplan XML Statistics Profile " event gives actual execution plan for each query.
P.S: Please refer to my post before answering. Thanks !!
Yes,Show Plan XML Statistics Profile shows the actual plan.
The UDF's would explain it, yes. If you dig around a bit in the trace, I'm sure you'll see the same plan as that shown within Management Studio.
Unfortunately the place where i am looking at,in the management studio it shows index seek and in the plan from trace shows index scan. I am not worried why the plan is different. I just want to knoww can i rely more on the exec plan from trace than management studio?
The answer is yes, you can rely on that plan. It's an actual execution plan. The thing is, because you're using what I'm pretty sure are multi-statement table valued functions, the plan in SSMS will not show you what's going on inside those functions. Is that plan accurate? Yes it is. Does that plan show everything? No it does not.
By the way, this type of UDF is a notorious performance bottleneck. It relies on the table variable functionality within SQL Server. Table variables have no statistitics, so when you use one of these, it assumes 1 row of data. Now, if you have only small amounts of data, great, it should work fairly well, but if you have larger amounts of data moving through it, say over 100 rows, or you start using it in JOINs to other tables or, worst of all, other UDFs, or you filter on it in WHERE clauses, you're guaranteed very poor performance.
"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
February 9, 2011 at 8:29 am
Thanks Grant. We are actually using UDF's inside the procedure. There is a table scan going inside the UDF. How do i fix that?
February 9, 2011 at 8:40 am
You can't. Not really. There's just so much that can be done with UDF's, like I said.
"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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply