Sql-server – Extended Events XEL file reader; Is there a FileName property avaialble in QueryTableXEventData or SqlBulkCopy

extended-eventssql server

Using the QueryTableXEventData class in Powershell I am able to parse 100's of XEL files very quickly and export their content to a table in SQL Server using SQLBulkCopy

Example:

$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData
("\\some_file_path\XELog_Files*.xel")

I don't see though a File property in the the $fields array.

$event | Select-Object -ExpandProperty Fields

is_cached     System.Boolean              True
is_recovered  System.Boolean             False
is_dac        System.Boolean             False
database_id   System.UInt32                 73
packet_size   System.UInt32               8000
options       System.Byte[]  {32, 0, 0, 40...}
options_text  System.String
database_name System.String

Or in $events

Name      : login
UUID      : 13e22e12-3cb8-49bf-a3e1-131faa95601c
Package   : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropPackage
Metadata  : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropEventMetadata
Timestamp : 18/08/2020 03:41:37 +00:00
Fields    : {is_cached, is_recovered, is_dac, database_id...}
Actions   : {server_instance_name, database_name, database_id, client_hostname...}
Location  : Microsoft.SqlServer.XEvent.Linq.EventLocator

Is there a property I can use in SQLBulkCopy or QueryTableXEventData that has the file name currently being read or exported?

Best Answer

I don't believe the file name is exposed via a property.

Instead of passing a wildcard path to the QueryableXEventData constructor, you could get a list of the files and process each individually:

$eventFiles = Get-Item "\\some_file_path\XELog_Files*.xel"
foreach($eventFile in $eventFiles) {
    $events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($eventFile.FullName)
    foreach($event in $events) {
        # event processing here, using $eventFile.FullName or $eventFile.Name
    }
}

Related Solutions

Sql-server – Visualizing SQL Server Extended Events data

Caveat: Much of the information provided below I learned strictly from going through two Pluralsight courses by Jonathan Keyhayias. Well worth the one month expense for the plus subscription to go through his two courses.

First just a few points of interest that I think will help (or at most be of interest):

When an Extended Event session is started it will have a portion of memory allocated to a buffer space to store data generated by the events of the session. In your session this is set to the default value of 4MB
There are multiple targets available to use. These targets are either synchronous or asynchronous in how they receive data. The two most commonly used targets being Target File and Ring Buffer are both asynchronous. BOL article here indicates what type each target is.
The MAX_DISPATCH_LATENCY is a configuration option that controls when event data is dispatched to the target(s). Dispatching only occurs for asynchronous targets. There are two conditions that will cause event data to be dispatched: (1) memory buffer for the session is full or (2) event data in the buffer exceeds the session's MAX_DISPATCH_LATENCY configured option.
When you open the Live Data Viewer it will attach an additional target to the event session called "streaming target". This will receive the live event stream as memory buffers are being dispatched. It actually will also change the dispatch latency associated with the session to 3 seconds in order to get near real time view of the session.

Now to specific points in your question:

The problem that I am having is that it appears that the Live Events feature uses an internal buffer, which means that sometimes I need to execute a query several times to get its information to display in the window. I therefore have a two-part question to ask

I am not aware that it does this other than what I stated above. I would expect that the event was captured it just has not meet the thresholds required for it to be dispatched to your live data viewer. I tested this with the following query from AdventureWorks2012:

SELECT * FROM dbo.ErrorLog
WAITFOR DELAY '00:00:01' ;
GO

Using your event session configuration, with the exception that I am filtering to only capture data for AdventureWorks2012 database on my local instance, I can view the target data for this session and find the query was captured:

enter image description here

Executing this query one more time will finally cause it to get dispatched and the data viewer displays one event. Now if you actually want to see all the events that are displayed simply STOP the session and the buffer will get fully dispatched. I see this once I stop my session:

enter image description here

1.Is there a way to get around this delay in getting the events to display in the live feed? (I'm doing this on a local database so performance is not an issue)

I had thought you could change the MAX_MEMORY to a lower value which would indicate a small buffer size to capture events. However the lowest value you can set this to in SQL Server 2012 is 200KB, which the query I used does not meet that limit to cause it to immediately be dispatched. Only thing I could do was at most execute a query that would cause the buffer to be reached and the previous events captured to be dispatched:

SELECT *
FROM Person.Person
ORDER BY EmailPromotion DESC;

2.Is the live feed the best way to visualize Extended Events data? Is there another tool either in SSMS or not that is better adapted to my use case?

Not that I am aware of currently. I would suggest the best method of getting data out as soon as it occurs is to query the XML for the ring_buffer target and just shred that out. I can repeat the above example and as soon as I execute the query below I see the event.

-- Create XML variable to hold Target Data
DECLARE @target_data XML
SELECT  @target_data = CAST([t].[target_data] AS XML)
FROM    [sys].[dm_xe_sessions] AS s
JOIN    [sys].[dm_xe_session_targets] AS t
        ON [t].[event_session_address] = [s].[address]
WHERE   [s].[name] = N'Simple Query Benchmarking' AND
        [t].[target_name] = N'ring_buffer' ;

-- Return the full XML document
--SELECT @target_data;

--Shred XMl to get needed data
SELECT  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
    n.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
    n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM @target_data.nodes('RingBufferTarget/event[@name=''sql_batch_completed'']') AS q(n)

enter image description here

Sql-server – Blocking Extended events

This query is run by Management Studio. I have checked this Microsoft SQL Server Management Studio(13.0.11000.78).

In recent releases ability to connect to Azure SQL Database been added to SSMS and looks like SSMS executes this query when ever we change focus to any tab or click in SSMS tab.

So now when we switch table from session 1 (which creates blocking) to session 2, below query is getting executed for the session 1. You can check this by running DBCC InputBudder(<>).

DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'

And hence when blocked process report captures event it get this query from Session 1.

Now we may think that why this is not the case for Session 2. But this is not case as while query in session 2 is executing, SSMS not fire above query.

Best Answer

Related Solutions

Sql-server – Visualizing SQL Server Extended Events data

Sql-server – Blocking Extended events

Related Question