Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Is there a way to guarantee the order of results when reading an Extended event .xel log file using the sys.fn_xe_file_target_read_file() DMF? Ideally I would like to number the records 1..X based on the order in which they were inserted into the .xel file.
I need to poll the file target multiple times and insert new records into a table. If I could guarantee the order of results then I could add the Meta data column "Entry Number", and use this column to avoid inserting the same records over multiple executions i.e.
SELECT entries.EntryNumber
,entries.file_name
,entries.data
,entries.file_offset
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY <GUARANTEE ORDERING BASED ON ORDER OF INSERTION>) AS EntryNumber,
file_name,
CONVERT (XML, event_data) AS data
,file_offset
FROM sys.fn_xe_file_target_read_file ('C:\ExEvent_0_123456789.xel', 'C:\ExEvent_0_123456789.xem', NULL, NULL)
) entries
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.ExEventLog d
WHERE entries.EntryNumber = d.EntryNumber
)
If this is not possible, can I guarantee that all records with the same 'file_offset' value are loaded into the .xel file in one single batch? For example if I query the .xel file for all records with a 'file_offset' of 123, can I guarantee that no additional records with a ‘file_offset’ of 123 will be loaded subsequently?
This is based on creating the extended event WITH MAX_DISPATCH_LATENCY = 1 SECONDS
and polling the .xel file every 60 seconds.
I don't feel comfortable using the @initial_file_name and @initial_offset paramters because of this bug / feature : http://connect.microsoft.com/SQLServer/feedback/details/714286/sys-fn-file-target-read-file-reads-incorrect-files-when-offset-reading#details
You can use the
timestamp
attribute from the root node inevent_data
.