none
Terminating a data collection session for extended events - SQL Server 2008 R2/2012

    Question

  • Hi,

    in order to schedule in batch the data collection for the extended events, how can I indicate a final date/time for a session? Fe, the Profiler allows to specify the final date/time for the trace to produce.

    Thanks

    Friday, October 18, 2013 7:29 AM

Answers

  • I don't think there is a built-in way to do that with Extended Events traces.  As you're running it with T-SQL, you could build in a WAITFOR TIME or DELAY, then a STOP, or create a SQL Agent job to do it, eg

    WAITFOR DELAY '00:01:00'	-- wait one minute
    --WAITFOR TIME '18:00:00'	-- wait until 6pm
    GO
    
    ALTER EVENT SESSION [yourXESession] ON SERVER STATE = STOP
    GO

    Friday, October 18, 2013 3:34 PM
  • Hi Andreas, I don't understand your reaction!

    It should be enough having a WAITFOR mechanism dedicated for the Extended Events. And so, it seems that in SQL Server 2012 the Extended Events are improved also with GUI applications (fe the Wizard).

    I know the usefulness of the Extended Events but you try to match the Extended Events of SQL Server 2008 R2 vs the ones of SQL Server 2012.

    Bye

    I don’t know if maybe my answer somehow got taken wrong. I am simply trying to give best advice or in this case trying to explain the background of the technologies involved and why some things make sense and others less.

    I don’t see where I am comparing XEvents 2008 with XEvents 2012 directly, but of course they share the same base. 2012 adds more events and also the mentioned GUI. Right.

    Starting from there, it may be possible to extend the GUI  for the Live-target to a time-limit. But I do not want to focus on things that aren’t there, and quite frankly I see no real need for and don’t expect them to come. The Live-Target Viewer is really meant for “live” watching the events.

    And if we look at SQL Server altogether, I can’t think of any technology that is actually using a WAITFOR mechanism. Anything that needs some kind of scheduling is done via SQL Agent. (Of course there are things in the code, that do work with “timeouts” and “quantum”, but that would go too far)

    Anyhow. The place to request new features would rather be connect.microsoft.com that a forum thread.

    I am sorry if you do not like that answer, but I don't think an answer like "Yeah, we need a time-extended events session feature" would be helpful for real. I hope you can undestand the reasoning and stll be happy with extended Events the way they are ;-)

    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, October 21, 2013 3:54 PM

All replies

  • I don't think there is a built-in way to do that with Extended Events traces.  As you're running it with T-SQL, you could build in a WAITFOR TIME or DELAY, then a STOP, or create a SQL Agent job to do it, eg

    WAITFOR DELAY '00:01:00'	-- wait one minute
    --WAITFOR TIME '18:00:00'	-- wait until 6pm
    GO
    
    ALTER EVENT SESSION [yourXESession] ON SERVER STATE = STOP
    GO

    Friday, October 18, 2013 3:34 PM
  • Ok, thanks.

    I'm thinking to create a dedicated job to create, start and stop an extended event session. But a time mechanism for the extended events that will replace the Profiler is more important. Without a such mechanism xevents constrain to manage an event session inside the existing job to monitor.

    Friday, October 18, 2013 5:34 PM
  • ...

    I'm thinking to create a dedicated job to create, start and stop an extended event session. But a time mechanism for the extended events that will replace the Profiler is more important. Without a such mechanism xevents constrain to manage an event session inside the existing job to monitor.

    You have to remember that Profiler is a GUI-Application. And this application adds enormous overhead to the server.

    Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load )

    So this really isn't comparable. Using a job really is the right way to do accomplish that. Just as it would have been for SQL Trace (without Profiler attached to it)

    You can of course also build a small .Net App usung the Streaming Provider, or just sending this command - and still save the oveahead.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Friday, October 18, 2013 11:05 PM
  • Hi Andreas, I don't understand your reaction!

    It should be enough having a WAITFOR mechanism dedicated for the Extended Events. And so, it seems that in SQL Server 2012 the Extended Events are improved also with GUI applications (fe the Wizard).

    I know the usefulness of the Extended Events but you try to match the Extended Events of SQL Server 2008 R2 vs the ones of SQL Server 2012.

    Bye

    Saturday, October 19, 2013 5:57 AM
  • Hi Andreas, I don't understand your reaction!

    It should be enough having a WAITFOR mechanism dedicated for the Extended Events. And so, it seems that in SQL Server 2012 the Extended Events are improved also with GUI applications (fe the Wizard).

    I know the usefulness of the Extended Events but you try to match the Extended Events of SQL Server 2008 R2 vs the ones of SQL Server 2012.

    Bye

    I don’t know if maybe my answer somehow got taken wrong. I am simply trying to give best advice or in this case trying to explain the background of the technologies involved and why some things make sense and others less.

    I don’t see where I am comparing XEvents 2008 with XEvents 2012 directly, but of course they share the same base. 2012 adds more events and also the mentioned GUI. Right.

    Starting from there, it may be possible to extend the GUI  for the Live-target to a time-limit. But I do not want to focus on things that aren’t there, and quite frankly I see no real need for and don’t expect them to come. The Live-Target Viewer is really meant for “live” watching the events.

    And if we look at SQL Server altogether, I can’t think of any technology that is actually using a WAITFOR mechanism. Anything that needs some kind of scheduling is done via SQL Agent. (Of course there are things in the code, that do work with “timeouts” and “quantum”, but that would go too far)

    Anyhow. The place to request new features would rather be connect.microsoft.com that a forum thread.

    I am sorry if you do not like that answer, but I don't think an answer like "Yeah, we need a time-extended events session feature" would be helpful for real. I hope you can undestand the reasoning and stll be happy with extended Events the way they are ;-)

    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, October 21, 2013 3:54 PM