Extended events for SQL Server Machine Learning Services

THIS TOPIC APPLIES TO: yesSQL Server (Windows only)noAzure SQL DatabasenoAzure SQL Data WarehousenoParallel Data Warehouse

SQL Server provides a set of extended events to use in troubleshooting operations related to the SQL Server Trusted Launchpad, as well as Python or R jobs sent to SQL Server.

Applies to: SQL Server 2016 R Services, SQL Server 2017 Machine Learning Services

SQL Server events for machine learning

To view a list of events related to SQL Server, run the following query from SQL Server Management Studio.

SELECT o.name AS event_name, o.description
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p
ON o.package_guid = p.guid
WHERE o.object_type = 'event'
AND p.name = 'SQLSatellite';

For general information about using extended events, see Extended Events Tools.

Tip

For extended event generated by SQL Server, try the new SSMS XEvent profiler. This new feature in Management Studio displays a live viewer for extended events, and is less intrusive to the SQL Server than a similar Profiler trace.

Additional events specific to machine learning components

Additional extended events are available for components that are related to and used by SQL Server Machine Learning Services, such as the SQL Server Trusted Launchpad, and BXLServer, the satellite process that starts the R runtime. These additional extended events are fired from the external processes, and thus must be captured using an external utility.

For more information about how to do this, see the section, Collecting events from external processes.

Table of extended events

Event Description Notes
connection_accept Occurs when a new connection is accepted. This event serves to log all connection attempts.
failed_launching Launching failed. Indicates an error.
satellite_abort_connection Abort connection record
satellite_abort_received Fires when an abort message is received over a satellite connection.
satellite_abort_sent Fires when an abort message is sent over satellite connection.
satellite_authentication_completion Fires when authentication completes for a connection over TCP or Namedpipe.
satellite_authorization_completion Fires when authorization completes for a connection over TCP or Namedpipe.
satellite_cleanup Fires when satellite calls cleanup. Fired only from external process. See instructions on collecting events from external processes.
satellite_data_chunk_sent Fires when the satellite connection finishes sending a single data chunk. The event reports the number of rows sent, the number of columns, the number of SNI packets usedm and time elapsed in milliseconds while sending the chunk. The information can help you understand how much time is spent passing different types of data, and how many packets are used.
satellite_data_receive_completion Fires when all the required data by a query is received over the satellite connection. Fired only from external process. See instructions on collecting events from external processes.
satellite_data_send_completion Fires when all required data for a session is sent over the satellite connection.
satellite_data_send_start Fires when data transmission starts. Data transmission starts just before the first data chunk is sent.
satellite_error Used for tracing sql satellite error
satellite_invalid_sized_message Message's size is not valid
satellite_message_coalesced Used for tracing message coalescing at networking layer
satellite_message_ring_buffer_record message ring buffer record
satellite_message_summary summary information about messaging
satellite_message_version_mismatch Message's version field is not matched
satellite_messaging Used for tracing messaging event (bind, unbind etc)
satellite_partial_message Used for tracing partial message at networking layer
satellite_schema_received Fires when schema message is received and read by SQL.
satellite_schema_sent Fires when schema message is sent by the satellite. Fired only from external process. See instructions on collecting events from external processes.
satellite_service_start_posted Fires when service start message is posted to launchpad. This tells Launchpad to start the external process, and contains an ID for the new session.
satellite_unexpected_message_received Fires when an unexpected message is received. Indicates an error.
stack_trace Occurs when a memory dump of the process is requested. Indicates an error.
trace_event Used for tracing purposes These events can contain SQL Server, Launchpad, and external process trace messages. This includes output to stdout and stderr from R.
launchpad_launch_start Fires when launchpad starts launching a satellite. Fired only from Launchpad. See instructions on collecting events from launchpad.exe.
launchpad_resume_sent Fires when launchpad has launched the satellite and sent a resume message to SQL Server. Fired only from Launchpad. See instructions on collecting events from launchpad.exe.
satellite_data_chunk_sent Fires when the satellite connection finishes sending a single data chunk. Contains information about the number of columns, number of rows, number of packets, and time elapsed sending the chunk.
satellite_sessionId_mismatch Message's session id is not expected

Collecting events from external processes

SQL Server Machine Learning Services starts some services that run outside of the SQL Server process. To capture events related to these external processes, you must create an events trace configuration file and place the file in the same directory as the executable for the process.

  • SQL Server Trusted Launchpad

    To capture events related to the Launchpad, place the .config file in the Binn directory for the SQL Server instance. In a default installation, this would be:

    C:\Program Files\Microsoft SQL Server\MSSQL_version_number.MSSQLSERVER\MSSQL\Binn.

  • BXLServer is the satellite process that supports SQL extensibility with external script languages, such as R or Python. A separate instance of BxlServer is launched for each external language instance.

    To capture events related to BXLServer, place the .config file in the R or Python installation directory. In a default installation, this would be:

    R: C:\Program Files\Microsoft SQL Server\MSSQL_version_number.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64.

    Python: C:\Program Files\Microsoft SQL Server\MSSQL_version_number.MSSQLSERVER\PYTHON_SERVICES\library\RevoScaleR\rxLibs\x64.

The configuration file must be named the same as the executable, using the format “[name].xevents.xml”. In other words, the files must be named as follows:

  • Launchpad.xevents.xml
  • bxlserver.xevents.xml

The configuration file itself has the following format:

\<?xml version="1.0" encoding="utf-8"?>  
<event_sessions>  
<event_session name="[session name]" maxMemory="1" dispatchLatency="1" MaxDispatchLatency="2 SECONDS">  
    <description owner="you">Xevent for launchpad or bxl server.</description>  
    <event package="SQLSatellite" name="[XEvent Name 1]" />  
    <event package="SQLSatellite" name="[XEvent Name 2]" />  
    <target package="package0" name="event_file">  
      <parameter name="filename" value="[SessionName].xel" />  
      <parameter name="max_file_size" value="10" />  
      <parameter name="max_rollover_files" value="10" />  
    </target>  
  </event_session>  
</event_sessions>  
  • To configure the trace, edit the session name placeholder, the placeholder for the filename ([SessionName].xel), and the names of the events you want to capture, For example, [XEvent Name 1], [XEvent Name 1]).
  • Any number of event package tags may appear, and will be collected as long as the name attribute is correct.

Example: Capturing Launchpad events

The following example shows the definition of an event trace for the Launchpad service:

\<?xml version="1.0" encoding="utf-8"?>  
<event_sessions>  
<event_session name="sqlsatelliteut" maxMemory="1" dispatchLatency="1" MaxDispatchLatency="2 SECONDS">  
    <description owner="hay">Xevent for sql tdd runner.</description>  
    <event package="SQLSatellite" name="launchpad_launch_start" />  
    <event package="SQLSatellite" name="launchpad_resume_sent" />  
    <target package="package0" name="event_file">  
      <parameter name="filename" value="launchpad_session.xel" />  
      <parameter name="max_file_size" value="10" />  
      <parameter name="max_rollover_files" value="10" />  
    </target>  
  </event_session>  
</event_sessions>  
  • Place the .config file in the Binn directory for the SQL Server instance.
  • This file must be named Launchpad.xevents.xml.

Example: Capturing BXLServer events

The following example shows the definition of an event trace for the BXLServer executable.

\<?xml version="1.0" encoding="utf-8"?>  
<event_sessions>  
 <event_session name="sqlsatelliteut" maxMemory="1" dispatchLatency="1" MaxDispatchLatency="2 SECONDS">  
    <description owner="hay">Xevent for sql tdd runner.</description>  
    <event package="SQLSatellite" name="satellite_abort_received" />  
    <event package="SQLSatellite" name="satellite_authentication_completion" />  
    <event package="SQLSatellite" name="satellite_cleanup" />  
    <event package="SQLSatellite" name="satellite_data_receive_completion" />  
    <event package="SQLSatellite" name="satellite_data_send_completion" />  
    <event package="SQLSatellite" name="satellite_data_send_start" />  
    <event package="SQLSatellite" name="satellite_schema_sent" />   
    <event package="SQLSatellite" name="satellite_unexpected_message_received" />    
    <event package="SQLSatellite" name="satellite_data_chunk_sent" />   
    <target package="package0" name="event_file">  
      <parameter name="filename" value="satellite_session.xel" />  
      <parameter name="max_file_size" value="10" />  
      <parameter name="max_rollover_files" value="10" />  
    </target>  
  </event_session>  
</event_sessions>  
  • Place the .config file in the same directory as the BXLServer executable.
  • This file must be named bxlserver.xevents.xml.

See also

Custom Management Studio Reports for Machine Learning Services