Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Microsoft.Data.SqlClient exposes data access tracing through the Microsoft.Data.SqlClient.EventSource event source. Other ADO.NET components, such as the .NET Framework providers for Oracle, OLE DB, and ODBC, and DataSet, have their own tracing facilities.
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 multiple ADO.NET components or between ADO.NET 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. Although tracing is not an ADO.NET-only feature, Microsoft providers take advantage of generalized tracing and instrumentation APIs.
For more information about how to collect Microsoft.Data.SqlClient traces, see Enable EventSource tracing in SqlClient.
Accessing Diagnostic Information in the Extended Events Log
Use Microsoft.Data.SqlClient connection IDs to correlate client events with server diagnostic information, such as connection failures, in the connectivity ring buffer and extended events log. For information about how to read the extended events log, see View Event Session Data.
For connection operations, Microsoft.Data.SqlClient sends a client connection ID during the prelogin exchange. If the connection fails, look for the ClientConnectionID field in the connectivity ring buffer to obtain 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 the prelogin exchange, the server won't receive the ID. The client connection ID is a 16-byte GUID. You can also find the client connection ID in the extended events target output if you add the client_connection_id action to events in an extended events session.
To get the client connection ID programmatically, use ClientConnectionId.
The connection ID is available for SqlConnection after it successfully establishes a connection. If a connection attempt fails, the ID might be available through ClientConnectionId.
Microsoft.Data.SqlClient also sends a thread-specific activity ID for batch statements and remote procedure call (RPC) requests when its event source is enabled and the server supports trace headers. To capture the activity ID in an extended events session, enable TRACK_CAUSALITY. For performance issues with an active connection, locate the activity ID in the client trace and then in the extended events output. The activity ID contains a 16-byte GUID and a four-byte sequence number. The sequence number indicates the relative order of requests within a thread. The legacy data access tracing configuration word doesn't configure Microsoft.Data.SqlClient's event source.
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)