Accessing Diagnostic Information in the Extended Events Log
In the Microsoft JDBC Driver 4.0 for SQL Server, tracing (Tracing Driver Operation) has been updated to make it easier to easier to correlate client events with diagnostic information, such as connection failures, from the server's connectivity ring buffer and application performance information in the extended events log. For information about reading the extended events log, see View Event Session Data.
For connection operations, the Microsoft JDBC Driver for SQL Server will send a client connection ID. If the connection fails, you can access the connectivity ring buffer (Connectivity troubleshooting in SQL Server 2008 with the Connectivity Ring Buffer) and find the ClientConnectionID field and get diagnostic information about the connection failure. Client connection IDs are logged in the ring buffer only if an error occurs. (If a connection fails before sending the prelogin packet, a client connection ID will not be generated.) The client connection ID is a 16-byte GUID. You can also find the client connection ID in the extended events target output, if the client_connection_id action is added to events in an extended events session. You can enable tracing and rerun the connection command and observe the ClientConnectionID field in the trace, if you need further client driver diagnostic assistance.
You can get the client connection ID programmatically by using ISQLServerConnection Interface. The connection ID will also be present in any connection-related exceptions.
When there is a connection error, the client connection ID in the server's BID trace information and in the connectivity ring buffer can help correlate the client connections to connections on the server. For more information about BID traces on the server, see Data Access Tracing. Note, the data access tracing article also contains information about performing a data access trace, which does not apply to the Microsoft JDBC Driver for SQL Server; see Tracing Driver Operation for information on doing a data access trace using the Microsoft JDBC Driver for SQL Server.
The JDBC Driver also sends a thread-specific activity ID. The activity ID is captured in the extended events sessions if the sessions are started with the TRACK_CAUSAILITY option enabled. For performance issues with an active connection, you can get the activity ID from the client's trace (ActivityID field) and then locate the activity ID in the extended events output. The activity ID in extended events is a 16-byte GUID (not the same as the GUID for the client connection ID) appended with a four-byte sequence number. The sequence number represents the order of a request within a thread. The ActivityId is sent for SQL batch statements and RPC requests. To enable sending ActivityId to the server, you first need to specify the following key-value pair in the Logging.Properties file:
com.microsoft.sqlserver.jdbc.traceactivity = on
For more information, see Tracing Driver Operation. This trace flag is used with corresponding JDBC object loggers to decide whether to trace and send the ActivityId in the JDBC driver. In addition to updating the Logging.Properties file, the logger com.microsoft.sqlserver.jdbc needs to be enabled at FINER or higher. If you want to send ActivityId to the server for requests made by a particular class, the corresponding class logger needs to be enabled at FINER or FINEST. For example, if the class is, SQLServerStatement, enable the logger com.microsoft.sqlserver.jdbc.SQLServerStatement.
The following is a sample that uses Transact-SQL to start an extended events session that will be stored in a ring buffer and will record the activity ID sent from a client on RPC and batch operations:
create event session MySession on server add event connectivity_ring_buffer_recorded, add event sql_statement_starting (action (client_connection_id)), add event sql_statement_completed (action (client_connection_id)), add event rpc_starting (action (client_connection_id)), add event rpc_completed (action (client_connection_id)) add target ring_buffer with (track_causality=on)