Data tracing in SqlClient
Applies to: 
.NET Framework
.NET
.NET Standard
.NET features built-in data tracing functionality that is supported by the Microsoft SqlClient Data Provider for SQL Server and SQL Server network protocols.
Tracing data access API calls can help diagnose the following problems:
Schema mismatch between client program and the database.
Database unavailability or network library problems.
Incorrect SQL whether hard coded or generated by an application.
Incorrect programming logic.
Issues resulting from the interaction between Microsoft SqlClient Data Provider for SQL Server and your own components.
To support different trace technologies, tracing is extensible, so a developer can trace a problem at any level of the application stack. The Microsoft SqlClient Data Provider for SQL Server takes advantage of generalized tracing and instrumentation APIs.
For more information about setting and configuring managed tracing in .NET, see Tracing Data Access.
Access diagnostic information in the extended events log
In the Microsoft SqlClient Data Provider for SQL Server, Data Access Tracing makes it 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 SqlClient Data Provider 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 data access tracing and rerun the connection command and observe the ClientConnectionID field in the data access trace, if you need further client driver diagnostic assistance.
You can get the client connection ID programmatically by using the SqlConnection.ClientConnectionID property.
Note
The Microsoft SqlClient Data Provider for SQL Server supports the server process ID since version 2.1.0. You can get it programmatically by using the SqlConnection.ServerProcessId property.
The ClientConnectionID and ServerProcessId are available for a SqlConnection object that successfully establishes a connection. If a connection attempt fails, ClientConnectionID may be available via SqlException.ToString.
The Microsoft SqlClient Data Provider for SQL Server 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_CAUSALITY option enabled. For performance issues with an active connection, you can get the activity ID from the client's data access 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 4-bytes sequence number. The sequence number represents the order of a request within a thread and indicates the relative ordering of batch and RPC statements for the thread. The ActivityID is currently optionally sent for SQL batch statements and RPC requests when data access tracing is enabled on and the 18th bit in the data access tracing configuration word is turned ON.
The following SQL statement 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)