Accessing Diagnostic Information in the Extended Events Log
Beginning in SQL Server 2012, SQL Server Native Client and data access tracing (Data Access Tracing) have been updated to make it easier to get diagnostic information about connection failures from the connectivity ring buffer and application performance information from the extended events log.
For information about reading the extended events log, see View Event Session Data.
Note
This feature is intended only for troubleshooting and diagnostic purposes and may not be suitable for auditing or security purposes.
Remarks
For connection operations, SQL Server Native Client 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 output target, 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 for a failed operation, if you need further diagnostic assistance.
If you are using ODBC in SQL Server Native Client and a connection succeeds, you can get the client connection ID by using the SQL_COPT_SS_CLIENT_CONNECTION_ID attribute with SQLGetConnectAttr.
SQL Server Native Client 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 data access trace (ActivityID field) and then locate the activity ID in the extended events output. The activity ID in the 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 and indicates the relative ordering of batch and RPC statements for the thread. The ActivityID is 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 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)
Control File
In SQL Server 2012, the contents of the SQL Server Native Client control file (ctrl.guid.snac11) is:
{8B98D3F2-3CC6-0B9C-6651-9649CCE5C752} 0x00000000 0 MSDADIAG.ETW
{2DA81B52-908E-7DB6-EF81-76856BB47C4F} 0xFFFFFFFF 0 SQLNCLI11.1
MOF File
In SQL Server 2012, the contents of the SQL Server Native Client mof file is:
#pragma classflags("forceupdate")
#pragma namespace ("\\\\.\\Root\\WMI")
/////////////////////////////////////////////////////////////////////////////
//
// MSDADIAG.ETW
[
dynamic: ToInstance,
Description("MSDADIAG.ETW"),
Guid("{8B98D3F2-3CC6-0B9C-6651-9649CCE5C752}"),
locale("MS\\0x409")
]
class Bid2Etw_MSDADIAG_ETW : EventTrace
{
};
[
dynamic: ToInstance,
Description("MSDADIAG.ETW"),
Guid("{8B98D3F3-3CC6-0B9C-6651-9649CCE5C752}"),
DisplayName("msdadiag"),
locale("MS\\0x409")
]
class Bid2Etw_MSDADIAG_ETW_Trace : Bid2Etw_MSDADIAG_ETW
{
};
[
dynamic: ToInstance,
Description("MSDADIAG.ETW formatted output (A)"),
EventType(17),
EventTypeName("TextA"),
locale("MS\\0x409")
]
class Bid2Etw_MSDADIAG_ETW_Trace_TextA : Bid2Etw_MSDADIAG_ETW_Trace
{
[
WmiDataId(1),
Description("Module ID"),
read
]
uint32 ModID;
[
WmiDataId(2),
Description("Text StringA"),
extension("RString"),
read
]
object msgStr;
};
[
dynamic: ToInstance,
Description("MSDADIAG.ETW formatted output (W)"),
EventType(18),
EventTypeName("TextW"),
locale("MS\\0x409")
]
class Bid2Etw_MSDADIAG_ETW_Trace_TextW : Bid2Etw_MSDADIAG_ETW_Trace
{
[
WmiDataId(1),
Description("Module ID"),
read
]
uint32 ModID;
[
WmiDataId(2),
Description("Text StringW"),
extension("RWString"),
read
]
object msgStr;
};
/////////////////////////////////////////////////////////////////////////////
//
// SQLNCLI11.1
[
dynamic: ToInstance,
Description("SQLNCLI11.1"),
Guid("{2DA81B52-908E-7DB6-EF81-76856BB47C4F}"),
locale("MS\\0x409")
]
class Bid2Etw_SQLNCLI11_1 : EventTrace
{
};
[
dynamic: ToInstance,
Description("SQLNCLI11.1"),
Guid("{2DA81B53-908E-7DB6-EF81-76856BB47C4F}"),
DisplayName("SQLNCLI11.1"),
locale("MS\\0x409")
]
class Bid2Etw_SQLNCLI11_1_Trace : Bid2Etw_SQLNCLI11_1
{
};
[
dynamic: ToInstance,
Description("SQLNCLI11.1 formatted output (A)"),
EventType(17),
EventTypeName("TextA"),
locale("MS\\0x409")
]
class Bid2Etw_SQLNCLI11_1_Trace_TextA : Bid2Etw_SQLNCLI11_1_Trace
{
[
WmiDataId(1),
Description("Module ID"),
read
]
uint32 ModID;
[
WmiDataId(2),
Description("Text StringA"),
extension("RString"),
read
]
object msgStr;
};
[
dynamic: ToInstance,
Description("SQLNCLI11.1 formatted output (W)"),
EventType(18),
EventTypeName("TextW"),
locale("MS\\0x409")
]
class Bid2Etw_SQLNCLI11_1_Trace_TextW : Bid2Etw_SQLNCLI11_1_Trace
{
[
WmiDataId(1),
Description("Module ID"),
read
]
uint32 ModID;
[
WmiDataId(2),
Description("Text StringW"),
extension("RWString"),
read
]
object msgStr;
};