Sql-server – How to access ETW Traces on SQL Server for linux

extended-eventslinuxsql server

I have SQL Server running in a docker image. I have an extended event session running on it. I can connect to it fine and view live data with SSMS. I can use T-SQL to view the histogram target. If this was a windows box, I could use logman.exe to view and manage the event trace session. How do I do it in SQL Server for Linux?

I am absolutely sure that the ETW is getting created.

SELECT
    xes.name AS [Session Name],
    CAST(xest.target_data AS xml) [Histogram XML],
    xest.target_name
    FROM sys.dm_xe_session_targets xest  
    INNER JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address  
    WHERE xest.target_name = 'etw_classic_sync_target';

<ETWTarget truncated="0"> <DefaultETWSession> <Name>XE_DEFAULT_ETW_SESSION</Name> <Path>C:\Users\Client\Temp\XEEtw.etl</Path> <BufferSizeKb>128</BufferSizeKb> <FileSizeMb>20</FileSizeMb> </DefaultETWSession> <DefaultXESession> <Name>XE_DEFAULT_ETW_SESSION</Name> </DefaultXESession> </ETWTarget>

However, if I execute find / -iname XEEtw.etl inside bash on the docker container I get no results. I know that SQL Server on Linux is running its own internal copy of Windows NT. I just don't know if that ETW is exposed.

Best Answer

How do I access ETW Traces on SQL Server for linux?

Unfortunately, ETW (Event Tracing for Windows) is not available on Linux. It is a Windows-only function. So for the time being you are stuck with the SQL and filesystem options for view the EE data.

The .NET Core team chose LTTng for trace support when .NET apps are running on Linux. You could explore that as an option for correlating OS events with those recorded by SQL Server.

After your update (super interesting that ETW events might be firing in Drawbridge), I see that the .etl file is getting created on a file path that probably doesn't exist on your Linux-based Docker container. You should try setting a known-good file path when you create the XE session:

ADD TARGET package0.etw_classic_sync_target   
    (SET default_etw_session_logfile_path = N'/temp/sqletw.etl' )

Related Solutions

Sql-server – How to determine what is using a particular resource (table, view, or function, etc) on the SQL Server

I did a DuckDuckGo.com search for the like_i_sql_unicode_string since there appears to be no documentation that describes it included with SQL Server, and came across this Connect item, https://connect.microsoft.com/SQLServer/feedback/details/699053/sql-batch-completed-event-returns-0-for-row-count#details - it contains a sample extended event in the "steps to reproduce" section of the Connect item. In the sample extended event is this text, which wraps the search terms in question in the standard SQL Server wildcard, %.

 WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%sys.objects%')))

I modified my extended event definition like:

DROP EVENT SESSION FindCaller ON SERVER;

CREATE EVENT SESSION [FindCaller] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting
(
    ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.nt_username
        , sqlserver.session_id
        , sqlserver.sql_text
    )
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ObjectInQuestion%'))
)
WITH 
(
    MAX_MEMORY=4096 KB
    , EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
    , MAX_DISPATCH_LATENCY=30 SECONDS
    , MAX_EVENT_SIZE=0 KB
    , MEMORY_PARTITION_MODE=NONE
    , TRACK_CAUSALITY=OFF
    , STARTUP_STATE=OFF
);

Now, when I 'Watch Live Data' I see the culprit statements, along with the actual SQL text being executed, the name of the application, the client machine name, etc. Quite useful.

The take-home for me is the Extended Event GUI needs better documentation!

SQL Server 2012 – Troubleshooting Extended Events for Selective XML Indexing

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.

Best Answer

Related Solutions

Sql-server – How to determine what is using a particular resource (table, view, or function, etc) on the SQL Server

SQL Server 2012 – Troubleshooting Extended Events for Selective XML Indexing

Related Question