Collect a SQL Driver BID Trace

Collect a SQL Server Driver Built-In Diagnostic (BID) Trace

What is a BID Trace

The built-in diagnostic trace outputs the state of various driver APIs. This is a very detailed trace that can reveal information about issues that the driver may encounter. The trace records the process and thread ID to keep various activities separate, as well as a timestamp accurate to the system timer resolution (about 100ns).

BID Trace vs ODBC Trace

An ODBC trace is performed by the ODBC Driver Manager, a shim DLL between the application and the driver. This DLL acts as a proxy, and as such, intercepts all driver calls and can log the ODBC API state of any ODBC driver.

This trace has a number of major disadvantages over BID traces:

1. It is very slow and can significantly impact application performance.
2. It is very difficult to configure to trace server applications.
3. It only traces the high-level API between the application and the driver and shows no internal driver state.

What Can Be Traced using BID Tracing

• Almost all Windows-based data access components, including ADO, JDBC XA Transactions, and managed providers.
• SQL Servers up through SQL 2014. SQL 2016 and later can be traced via XEvent SNI tracing.
• SQL Server ODBC Drivers for Linux (special instructions apply).

What Cannot Be Traced using BID Tracing

• The Microsoft JDBC Driver. See https://docs.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver15
• .NET Core System.Data and Microsoft.Data libraries.
• Non-Microsoft drivers and Providers. These often have their own trace functionality.

When to Use BID Tracing

References

• https://bobsql.com/using-sql-servers-snitrace-to-troubleshoot-networking-issues-2/
• Older (manual) instructions: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc765421(v=sql.100)