Provide XEvents target directly into SQL Server table
Provide a way to directly write XEvent output directly into a SQL Server database table in a same or different instance and same or different database.
The goal being to be able to not have any events missed but have the entire stream captured to the table and also provide header information for when the capture table was created for easier management and deletion. Ideally there should be a purge task that could be activated to clean out such captures after X days.
XEvents is a great and powerful tool.
Customers should have an easier way to get it directly into SQL Server so they can consume the historization back via normal SQL syntax. XEvent output is structured in nature.
2 new target types in sys.dmxeobjects would likely be helpful:
Appropriate Security management would need to be put in place.
Most important here are: the victim-list, which for a multi-victim-deadlock contains one process-id per victim, which again one can investigate more deeply in the process-list. In the executionStack one can find the executes SQL-command and if applicable even the procedure name. Below in the resource-list follows a list of all involved (blocked) resources. All nodes are peppered with details such as row number, objectname, lock-mode, waiting time, spid and of course timestamps. Who wants to start from here can of course take apart the XML further and try to break individual nodes relationally into columns. For that one has to first extract the deadlock-graph. I visited back for ap ssc results on https://resultsism.in/ap-ssc-results-2019/
Automated clean-up process needs to be more flexible to have the following options "No Clean up", "After x Seconds" where x is a big integer.
Further the table can have the following 3 columns. They can be an Auto Increment Number (big integer) as a clustered index, Date Time of the event this needs to be datetime type, event info (xml) capturing the extended event detail as extended event.
Gianluca Sartori commented
XESmartTarget can fill this gap. Check it out here: https://github.com/spaghettidba/XESmartTarget
Vladimir Moldovanenko commented
SQL Server Broker was requested as a new target for Extended Events. Some parts of this were delivered in SQL 2017 but it still does not work.
Once this works you can capture and whatever you want with extended events, even INSERT these into your own table, either by RECEIVEing from queue or having activation procedure coded to do what you want.