Is there a way to post Extended Events to SQL Server Broker queue, similar to Event Notifications

Question

Hi all

I am fascinated by Extended Events.

Do you know if there is any plan to release Extended Events target/consumer that is Service Broker based, just like Event Notifications?

I would like to be able to do the same thing as Event Notification but with Extended Events.

I think it is missing piece to make Extended Events to be able to post into Broker queue.

Any ideas on how this can be done now? I want no polling of ring buffer!

Thank you for your time and expertise.

Vladimir

Answer 1

Following is the best forum for this question:

SQL Server Database Engine

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

Hi all

I am fascinated by Extended Events and see good use for these.

Do you know if there is any plan to release Extended Events target/consumer that is Service Broker based, just like Event Notifications?

I would like to be able to do the same thing as Event Notification but with Extended Events.

I think it is missing piece to make Extended Events to be able to post into Broker queue.

Any ideas on how this can be done now? I want no polling of ring buffer!

Thank you for your time and expertise.

Vladimir

Answer 3

Hi all

I am fascinated by Extended Events.

Do you know if there is any plan to release Extended Events target/consumer that is Service Broker based, just like Event Notifications?

I would like to be able to do the same thing as Event Notification but with Extended Events.

I think it is missing piece to make Extended Events to be able to post into Broker queue.

Any ideas on how this can be done now? I want no polling of ring buffer!

Thank you for your time and expertise.

Vladimir

You can't do this, it wasn't designed for that ability.  The best you can do is use the .NET streaming API in 2012 (won't work in 2008 or 2008 R2) and read the data with an external service and send it to Service Broker on your own. Of course at that point, you could just have the service handle the event and whatever you wanted to do with it.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 4

Hi all

I am fascinated by Extended Events.

Do you know if there is any plan to release Extended Events target/consumer that is Service Broker based, just like Event Notifications?

I would like to be able to do the same thing as Event Notification but with Extended Events.

I think it is missing piece to make Extended Events to be able to post into Broker queue.

Any ideas on how this can be done now? I want no polling of ring buffer!

Thank you for your time and expertise.

Vladimir

You can't do this, it wasn't designed for that ability.  The best you can do is use the .NET streaming API in 2012 (won't work in 2008 or 2008 R2) and read the data with an external service and send it to Service Broker on your own. Of course at that point, you could just have the service handle the event and whatever you wanted to do with it.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

OK, thank you! I am not that good at .NET programming but can do a lot in T-SQL and have good Broker experience. I guess I will find supporting info on streaming and make development request for .NET guy.

It appears that MS develops something like Event Notifications, then they rework it in different enhanced way, call it Extended Events and leave some pieces out, like bridge to Broker. Thanks again for your expert advice though

Answer 5

OK, thank you! I am not that good at .NET programming but can do a lot in T-SQL and have good Broker experience. I guess I will find supporting info on streaming and make development request for .NET guy.

It appears that MS develops something like Event Notifications, then they rework it in different enhanced way, call it Extended Events and leave some pieces out, like bridge to Broker. Thanks again for your expert advice though

No, that is incredibly incorrect.  Event Notifications and Extended Events are two completely different features in SQL Server entirely.  Extended Events is the replacement for SQL Trace.  I've blogged about the differences between the two features in the past.

http://sqlskills.com/blogs/jonathan/post/Event-Notifications-vs-Extended-Events.aspx

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 6

Ok, great link. Yet, it seems that a way to post-process Extended Event in TSQL is missing. I can output it to file from SQL Server, but I cannot consume it in TSQL in SQL Server? Don't you think it's missing? If I were to have have automatic way to launch procedure and have access to EVENTDATA, would this not be awesome?

Answer 7

Ok, great link. Yet, it seems that a way to post-process Extended Event in TSQL is missing. I can output it to file from SQL Server, but I cannot consume it in TSQL in SQL Server? Don't you think it's missing? If I were to have have automatic way to launch procedure and have access to EVENTDATA, would this not be awesome?

You can consume it in TSQL in SQL Server by querying the target in sys.dm_xe_session_targets or reading the file with fn_xe_file_target_read_file() and then performing XQuery operations.  Read through the Extended Events category on my blog, I've written nearly 400 pages of content about them.  Prior to the introduction of Event Notifications in SQL Server 2005, there was no way to access Trace events in near-real time as they were generated in the engine.  Trace was introduced in SQL 6.5, so it was in it's fourth (6.5, 7, 200, and then 2005) release where Event Notifications was added.  Yes Event Notifications are a fantastic feature, I've written a number of articles and presented sessions on how to use them to automate a lot of things in SQL Server.  There is also a Connect item to change the underlying mechanism from Trace to Extended Events, but the features are owned by two completely different parts of the development team, so it's not unreasonable in my opinion that they didn't hook a new feature like Extended Events, which is only v2 in 2012, to Event Notifications immediately.

Consider the challenges this could pose.  First it would be a breaking change because Extended Events events don't generate data like Trace events do.  So you would have to either A) define a fixed set of columns and get rid of the flexibility and better performance of Extended Events or B) implement this functionality as a specialized target that can interact with Service Broker, but in a manner that all of the Broker related events inside of the engine wouldn't fire, creating their own infinite loop of events feeding the target and firing more Broker events.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 8

Hi Vladimir,

As you said, Extended Events and Event Notifications are two good ways for us to use for providing event information. But there is some difference between them.

Event Notifications were introduced in SQL Server 2005 and offer the ability to collect a very specific subset of SQL Trace and DDL Events through a Service Broker service and queue.

Extended Events were introduced in SQL Server 2008 and use entirely different architecture inside of the database engine for providing events. Extended Events contain a number of events that are not available by any other means in SQL Server, For example the blocked process report is not available by Extended Events.

The key difference between the two features is that Event Notifications allow automated processing of the events asynchronously through Service Broker. But there is no similar mechanism for Extended Events currently. So if you want to post the Extended Events to Service Broker, it may be the key.

Regards,
Amy Peng

---

Amy Peng

TechNet Community Support

Answer 9

Hi Vladimir,

As you said, Extended Events and Event Notifications are two good ways for us to use for providing event information. But there is some difference between them.

Event Notifications were introduced in SQL Server 2005 and offer the ability to collect a very specific subset of SQL Trace and DDL Events through a Service Broker service and queue.

Extended Events were introduced in SQL Server 2008 and use entirely different architecture inside of the database engine for providing events. Extended Events contain a number of events that are not available by any other means in SQL Server, For example the blocked process report is not available by Extended Events.

The key difference between the two features is that Event Notifications allow automated processing of the events asynchronously through Service Broker. But there is no similar mechanism for Extended Events currently. So if you want to post the Extended Events to Service Broker, it may be the key.

Regards,
Amy Peng

---

Amy Peng

TechNet Community Support

I think you nailed it: we (I) need a way "to post the Extended Events to Service Broker, it may be the key". that is the bridge that is missing in my opinion. I went ahead and implemented Event Notifications, as I am after such events at this point, in particular DEADLOCK_GRAPH event.

And that is the beauty of Extended Notifications: being able to automate post event processing in TSQL. I created queue, created procedure, assigned to queue, in SP integrated call to my own workflow engine with email capability and low and behold, I have automated notification system that proactively informs me about any configured event.

Extended Events need bridge to Service Broker to achieve this automation. It is the same payload: EVENTDATA().

The way I think thsi can be done is to allow another Event Notification event: Extended_Events. When Extended Events fires an event, allow it to be posted as

'http://schemas.microsoft.com/SQL/Notifications/EventNotification'

 message. This would do it. :)

That would give new powerful consumer for Extened Events and a way to automate post event processing.

Otherwise, Extened Events usefulness is limited. I will not program .NET streaming, too complicated for me, but I will do it in TSQL.

Answer 10

This is a duplicate thread of:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/6b249f78-a40b-42cf-8830-f93cdf1eb375

I am merging the two threads to keep all of the information in one place.  In the future please don't post multiple threads on the same topic in different forums.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!