December 31, 2012 at 9:32 am
I am collecting queries using sql profiler which are taking more than 10 seconds using column filters. I also want to see the execution plan for these queries. If I enable "performance->Show Executioin Plan XML" then I get several thousands of execution plan. Is there any way to have the sql profiler to capture only execution plan for queries which takes 10 seconds or more.
Thanks,
RT
December 31, 2012 at 11:56 am
Duration is not a column on the Trace Event, so no. Without the Duration column on the Trace Event your filter will not apply.
You may be able to get what you want using Extended Events but I am not seeing a clear way unless you employ some near-real-time post-processing. In 2008 XE you could use the sqlserver.plan_handle Action and look to retrieve the estimated plan from the plan cache, but that may be as close as you get. Further to that, it only seems to hold up if all your queries are explicitly parameterized. If they are auto-parameterized the plan_handle will never relate to a plan in the plan cache. (Reference: What plan_handle is Extended Events sqlserver.plan_handle action returning?)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 31, 2012 at 3:04 pm
thanks for the reply. I am capturing events rpccompleted, spstatementcompleted and sqlbatchcompleted. none of these have plan handle as their column. but it works fine to filter by duration. i am still finding it hard to get execution plans of the queries which are running long.
December 31, 2012 at 3:30 pm
No way to directly do that, the execution plan event does not have duration as a column.
One way to do this is to first capture procedures running slow and then, as a second trace, filter on those procedure names. Assuming the plan hasn't changed in the interim, it'll work.
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
December 31, 2012 at 5:09 pm
RTSQL (12/31/2012)
thanks for the reply. I am capturing events rpccompleted, spstatementcompleted and sqlbatchcompleted. none of these have plan handle as their column. but it works fine to filter by duration. i am still finding it hard to get execution plans of the queries which are running long.
Trace Events will not contain plan_handle. I was talking about Extended Events (XE). XE is a new tracing sub-system introduced in SQL Server 2008 that will eventually replace the Profiler/Trace subsystem in a future version of SQL Server.
If you would like to stick with Trace, what Gail has outlined should get you some of the plans you are looking for.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 1, 2013 at 12:14 am
opc.three (12/31/2012)
Trace Events will not contain plan_handle. I was talking about Extended Events (XE). XE is a new tracing sub-system introduced in SQL Server 2008 that will eventually replace the Profiler/Trace subsystem in a future version of SQL Server.
There's no way to get the actual plan with XE in 2008. Sure, can get the plan handle and lookup to cache later, but that'll get estimated plan and plan may be gone.
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
January 1, 2013 at 5:02 am
GilaMonster (1/1/2013)
opc.three (12/31/2012)
Trace Events will not contain plan_handle. I was talking about Extended Events (XE). XE is a new tracing sub-system introduced in SQL Server 2008 that will eventually replace the Profiler/Trace subsystem in a future version of SQL Server.There's no way to get the actual plan with XE in 2008. Sure, can get the plan handle and lookup to cache later, but that'll get estimated plan and plan may be gone.
True. I mentioned this in an earlier post. And to that end maybe going after query stats to find long running queries and then going to the plan cache to get the estimated plan would be a more straightforward approach in SQL Server 2008 rather than enlisting XE.
It is not until SQL Server 2012 XE that we get a comprehensive solution. Namely the sqlserver.query_post_execution_showplan XEvent where we can retrieve the actual XML execution plan only after satisfying a condition on the query duration.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply