June 22, 2016 at 4:03 am
Hi,
I want to get the Exec Plans for anything that uses my TBS_SEG_WORK_CON_ORDERS_DATA table.
If I run a Profiler Trace with a filter on 'text data' for this name and the event 'Showplan XML', I get a bunch of rows back including the specific query I know runs at 5am and have done for months of running this whilst troubleshooting a performance issue.
So I then thought I'd better start using Ext Events to get used to it, so set up the equivalent using Extended Events, however my query that I can see at 5am in Profiler isn't displayed under Ext Events!?
The specific query I want to track does an insert into my table via a Stored Procedure.
The Ext event is set up to write to disk. When I open it if I've set the filter on the object name I don't get any rows back at all!?
If I set the filter on sql_text I get a couple of rows back from a separate query I know that runs a few hours later, but not the one I'm
trying to track. I know the exact plan and time of the query I'm trying to track as I get it in the Profile Trace no problem.
So am I using the wrong Event or Action or is there something else I'm missing. Because of this, until I can have confidence in Ext Events, I'll continue to use Profiler as I can't trust it captures everything in the same way!!
The two types of code I've used so far are as follows :
-- got no rows back
CREATE EVENT SESSION [Show Plan for Table usage] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlserver.sql_text)
WHERE ([sqlserver].[like_i_sql_unicode_string]([object_name],N'%TBS_SEG_WORK_CON_ORDERS_DATA%')))
-- got one row back but for a totally different query
CREATE EVENT SESSION [Show Plan for Table usage] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlserver.sql_text)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TBS_SEG_WORK_CON_ORDERS_DATA%')))
Thanks in advance..
June 22, 2016 at 6:26 am
You're using the wrong filter.
Object name would be the name of the procedure which the query is in, not the name of a table used by the query. The second one should work, but requires that the table is referred to in the query directly, not via a view or similar.
A word of caution, that's a VERY heavy event with significant overhead. I've heard of cases where just adding that event to a session (filtered or not) added a 25% CPU overhead. Use with caution and for very short time frames.
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 22, 2016 at 6:49 am
Thanks for your input Gail. So it's different to Profiler in what it picks up by the sounds of it as the query on this object is run via a SP, Profiler can pick it up ok but Ext Events can't?? If this is the case how do I code Ext Events to pick something up from within a SP.
Thank-you
June 22, 2016 at 6:51 am
Sorry, just re-read and you suggested using the Proc name. Thought I'd tried that but will give it another go.
thank-you
June 22, 2016 at 7:08 am
No, I didn't suggest using the procedure name. I said that your filter on ObjectName is wrong because that ObjectName is the procedure name, not the name of tables in the query.
You can, but then you'll get all the queries in the procedure.
When I'm looking for specific queries, I filter on the Query Hash
XE and Profiler are different, there is tweaking you need to do to convert one to the other
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 22, 2016 at 7:59 am
Yes I see. Putting the SP name in the 2nd query works like you say. But doesn't for Profiler. Be nice for MS to point out these very key differences!
I was also under the impression that Profiler is resource intensive because it brings everything back but then just displays the filtered data and that Ext Events only brought back what you needed and therefore wasn't as bad. This also doesn't seem to be the case then? Will run tests on a quiet dev system. The test system I'm using now is a bit busy but it does seem to go up\down 10% when I start\stop Ext Events.
Thanks for your help
June 22, 2016 at 8:13 am
Profiler, the gui, is a server-killer. Extended events are much lighter-weight, but that doesn't mean they're free.
Server-side traces only returned the filtered events, profiler didn't filter them after the fact. Extended events filter very early and only return events that passed the filter, but the event data (not the actions) still has to be generated, and for some events that process itself is intensive.
The post_execution_showplan event has a specific warning in Books Online that it has a performance overhead.
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 22, 2016 at 9:23 am
simon.letts (6/22/2016)
Hi,I want to get the Exec Plans for anything that uses my TBS_SEG_WORK_CON_ORDERS_DATA table.
Is this a problem that you want to solve with Extended events and only Extended events as a project?
Cos the plan cache is there, juicy enough to eat, with all the info you need in one crispy query.
June 22, 2016 at 9:28 am
MadAdmin (6/22/2016)
Cos the plan cache is there, juicy enough to eat, with all the info you need in one crispy query.
His event is for actual execution plans (plans with the run-time data in them), which can't be got from the plan cache. Plans retrieved from cache only have estimated values.
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 22, 2016 at 9:30 am
Cheers Mad, should have thought of that! 🙂
June 22, 2016 at 9:31 am
I'm learning lots here...I didn't realise that? So the only way to get actuals is from XE
June 22, 2016 at 9:38 am
simon.letts (6/22/2016)
I'm learning lots here...I didn't realise that? So the only way to get actuals is from XE
If you're talking about actual plans from queries run by an application, yes.
That said, the only difference is the run-time information in the plans, so unless you need both the actual rowcount|executions|execution mode|data size, you can fetch the plan from cache and use that.
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 22, 2016 at 9:38 am
GilaMonster (6/22/2016)
MadAdmin (6/22/2016)
Cos the plan cache is there, juicy enough to eat, with all the info you need in one crispy query.His event is for actual execution plans (plans with the run-time data in them), which can't be got from the plan cache. Plans retrieved from cache only have estimated values.
Ah.
Lesson learnt.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply