1

Why QueryableXEventData when configured to read live Extended Events stream is always one event behind? Same true for "Watch Live Data" window because it's based on QueryableXEventData (event_stream target) too.

Just in case below my create event session script.

CREATE EVENT SESSION [XE_Alter] ON SERVER 
    ADD EVENT sqlserver.object_altered(
        ACTION(sqlserver.database_name,sqlserver.sql_text)
        WHERE ([sqlserver].[database_name]='AdventureWorks2014')),
    ADD EVENT sqlserver.object_created(
        ACTION(sqlserver.database_name,sqlserver.sql_text)
        WHERE ([sqlserver].[database_name]='AdventureWorks2014')),
    ADD EVENT sqlserver.object_deleted(
        ACTION(sqlserver.database_name,sqlserver.sql_text)
        WHERE ([sqlserver].[database_name]='AdventureWorks2014'))
Dmitry Morozov
  • 170
  • 6
  • One event behind with respect to what? I'm fairly certain that it uses a polling methodology, so you're going to be behind truly live as a result. – Ben Thul Nov 04 '15 at 15:25
  • The event stream isn't using polling, it actually reads off a internal private "event_stream" target that gets attached to the event session which uses internal copies of the memory buffers to prevent performance impacts. Those just aren't flushing out to the target in true real time. – Jonathan Kehayias Nov 05 '15 at 15:56
  • @JonathanKehayias Thank you for the answer. Can you suggest a workaround? It's important for my application to get DDL updates within 1 min. – Dmitry Morozov Nov 05 '15 at 18:12
  • Event Notifications or a DDL Trigger would be the route I would take for catching DDL changes but without knowing your requirements or the full scope of what you are doing I can't suggest much in the way of a full solution. – Jonathan Kehayias Nov 05 '15 at 20:35
  • @JonathanKehayias I need DDL updates notifications for this project http://fsprojects.github.io/FSharp.Data.SqlClient/. I worked on this project for 2 years and it's quite popular in F# community. If you have access to Pluralsight video library, Tomas Petricek just did very course on F# type providers and data access. Sub-chapter "Type Providers for Azure and SQL/Demo: Storing Latest Movies in SQL" covers my library. – Dmitry Morozov Nov 06 '15 at 22:42
  • @JonathanKehayias F# type providers like code-gen in C# on steroids. I want F# intellisense inside VS to reflect changes in backing tables/objects within 30-60 secs to have nice user experience. – Dmitry Morozov Nov 06 '15 at 22:48
  • @dmitry, I don't think the solution to your problem will come from this question. The internal buffer copies and flush rate won't likely fit your needs here from XE but if you want to email me through my blog maybe we can create a solution you can use for this. – Jonathan Kehayias Nov 13 '15 at 04:19

2 Answers2

0

We encountered this same issue recently and were able to trace it (thanks to SQL team contacts) to this Microsoft Connect bug "https://connect.microsoft.com/SQLServer/feedback/details/2681550/queryablexeventdata-and-watch-live-data-one-event-behind". That link will refer to this Stackoverflow entry and a pretty educational blog https://itsalljustelectrons.blogspot.ca/2017/01/Hide-And-Seek-With-Extended-Events.html. It's taken us days and leveraging relationships to find this out, so hopefully this answer saves time and effort for others. - Shaun

drmssst
  • 123
  • 2
  • 14
0

This was already fixed in later version of the .NET SQL Client library.

Tomer (MSFT).

Tomer
  • 151
  • 4