-
Notifications
You must be signed in to change notification settings - Fork 55
Collect a SQL Driver 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).
On Windows systems, a BID Trace is based on LOGMAN Event Tracing for Windows (ETW) tracing and results in an ETL file that can be read by NETMON or converted into a CSV file that you can open in Excel.
On Linux systems, parameters in the ODBC configuration enable the tracing and the driver writes directly to the log file.
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:
- It is very slow and can significantly impact application performance.
- It is very difficult to configure to trace server applications.
- It only traces the high-level API between the application and the driver and shows no internal driver state.
- 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).
- 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.
BID tracing is generally warranted if you need to see specifics of the driver actions. Two main scenarios present themselves:
- In a network trace, you see an unexpected behavior from the driver or server, such as:
1.1. A long delay where there should be none, such as a delay in sending out the PreLogin packet after the TCP 3-way handshake. The BID trace may reveal an internal API taking a long time, such as a call to Active Directory or the DNS server.
1.2. Unexpectedly closing the connection, e.g. after the TCP 3-way handshake, or in the middle of SSL handshake. It can also reveal how much of the Connection Timeout was spent on preparing to put the first packet on the wire.
In the above cases, the client and server network trace should agree on the bad behavior. Take the BID trace of the server if it is the entity with the delay or closing the connection. Likewise, take it of the client driver if the delay or closure originates on the client.
- The client is making a local connection to the server and that is failing and you cannot reproduce it in a remote connection in order to take a network trace.
In this case, trace both the client and server.
The BID trace can tap into events from a variety of drivers. You should specify as few as necessary in order to take the trace. In order to do this, you should know which driver the application is using and what the SQL Server version is. There are also generic trace points for ODBC, OLE DB, and ADO, that can help even if not using a Microsoft driver. Since most applications use the SqlClient managed Provider, using System.Data and System.Data.SNI are common trace points.
The following extract from the bid2etwConfig.vbs script shows drivers that can be traced and their LOGMAN guids:
"{8B98D3F2-3CC6-0B9C-6651-9649CCE5C752}","{8B98D3F3-3CC6-0B9C-6651-9649CCE5C752}","MSDADIAG","ETW", _
"{04C8A86F-3369-12F8-4769-24E484A9E725}","{04C8A870-3369-12F8-4769-24E484A9E725}","ADODB","1", _
"{7EA56435-3F2F-3F63-A829-F0B35B5CAD41}","{7EA56436-3F2F-3F63-A829-F0B35B5CAD41}","ADOMD","1", _
"{24722B88-DF97-4FF6-E395-DB533AC42A1E}","{24722B89-DF97-4FF6-E395-DB533AC42A1E}","BCP","1", _
"{ED303448-5479-CA3F-5686-E020BA4F47F9}","{ED303449-5479-CA3F-5686-E020BA4F47F9}","BCP10","1", _
"{BD568F20-FCCD-B948-054E-DB3421115D61}","{BD568F21-FCCD-B948-054E-DB3421115D61}","DBNETLIB","1", _
"{76DBA919-5A36-FC80-2CAD-3185532B7CB1}","{76DBA91A-5A36-FC80-2CAD-3185532B7CB1}","MSADCE","1", _
"{101C0E21-EBBA-A60A-EC3D-58797788928A}","{101C0E22-EBBA-A60A-EC3D-58797788928A}","MSADCF","1", _
"{5C6CE734-1B3E-705E-C2AB-B272D99AAF8F}","{5C6CE735-1B3E-705E-C2AB-B272D99AAF8F}","MSADCO","1", _
"{13CD7F92-5BAA-8C7C-3D72-B69FAC139A46}","{13CD7F93-5BAA-8C7C-3D72-B69FAC139A46}","MSADDS","1", _
"{6C770D53-0441-AFD4-DCAB-1D89155FECFC}","{6C770D54-0441-AFD4-DCAB-1D89155FECFC}","MSADOX","1", _
"{F02A5DAC-6DB2-F77F-F6A8-6404FE697B7D}","{F02A5DAD-6DB2-F77F-F6A8-6404FE697B7D}","MSDAORA","1", _
"{64A552E0-6C60-B907-E59C-10F1DFF76B0D}","{64A552E1-6C60-B907-E59C-10F1DFF76B0D}","MSDAPRST","1", _
"{564F1E24-FC86-28E1-74F8-5CA0D950BEE0}","{564F1E25-FC86-28E1-74F8-5CA0D950BEE0}","MSDAREM","1", _
"{CEB7253C-BB96-9DFE-51D1-53D966D0CF8B}","{CEB7253D-BB96-9DFE-51D1-53D966D0CF8B}","MSDART","1", _
"{B6501BA0-C61A-C4E6-6FA2-A4E7F8C8E7A0}","{B6501BA1-C61A-C4E6-6FA2-A4E7F8C8E7A0}","MSDASQL","1", _
"{87B93A44-1F73-EC83-7261-2DFC972D9B1E}","{87B93A45-1F73-EC83-7261-2DFC972D9B1E}","MSDATL3","1", _
"{F34765F6-A1BE-4B9D-1400-B8A12921F704}","{F34765F7-A1BE-4B9D-1400-B8A12921F704}","ODBC","1", _
"{932B59F1-90C2-D8BA-0956-3975C344AE2B}","{932B59F2-90C2-D8BA-0956-3975C344AE2B}","ODBCBCP","1", _
"{0DD082C4-66F2-271F-74BA-2BF1F9F65C66}","{0DD082C5-66F2-271F-74BA-2BF1F9F65C66}","OLEDB","1", _
"{74A75B02-36D8-EDE6-D10E-95B691503408}","{74A75B03-36D8-EDE6-D10E-95B691503408}","RowsetHelper","1", _
"{FC9F92E6-D521-9C9A-1D8C-D8980B9978A9}","{FC9F92E7-D521-9C9A-1D8C-D8980B9978A9}","SQLBROWSER","1", _
"{C5BFFE2E-9D87-D568-A09E-08FC83D0C7C2}","{C5BFFE2F-9D87-D568-A09E-08FC83D0C7C2}","SQLOLEDB","1", _
"{BA798F36-2325-EC5B-ECF8-76958A2AF9B5}","{BA798F37-2325-EC5B-ECF8-76958A2AF9B5}","SQLNCLI","1", _
"{A9377239-477A-DD22-6E21-75912A95FD08}","{A937723A-477A-DD22-6E21-75912A95FD08}","SQLNCLI10","1", _
"{2DA81B52-908E-7DB6-EF81-76856BB47C4F}","{2DA81B53-908E-7DB6-EF81-76856BB47C4F}","SQLNCLI11","1", _
"{EE7FB59C-D3E8-9684-AEAC-B214EFD91B31}","{EE7FB59D-D3E8-9684-AEAC-B214EFD91B31}","MSOLEDBSQL","1", _
"{AB6D5EEB-0132-74AB-C5F5-B23E1644DADA}","{AB6D5EEC-0132-74AB-C5F5-B23E1644DADA}","SQLSERVER.SNI", "1", _
"{48D59D84-105B-00FA-6B49-03462F696737}","{48D59D85-105B-00FA-6B49-03462F696737}","SQLSERVER.SNI10","1", _
"{B2A28C42-A7C2-1563-97CC-3BE49FDA19F9}","{B2A28C43-A7C2-1563-97CC-3BE49FDA19F9}","SQLSERVER.SNI11","1", _
"{5BD84A98-C66F-1694-6E42-B18A6243602B}","{5BD84A98-C66F-1694-6E42-B18A6243602B}","SQLSERVER.SNI12","1", _
"{4B647745-F438-0A42-F870-5DBD29949C99}","{4B647746-F438-0A42-F870-5DBD29949C99}","SQLSRV32","1", _
"{7C360F7F-7102-250A-A233-F9BEBB9875C2}","{7C360F80-7102-250A-A233-F9BEBB9875C2}","MSODBCSQL11","1", _
"{85DC6E48-9394-F805-45C9-C8B2ACA2E7FE}","{85DC6E49-9394-F805-45C9-C8B2ACA2E7FE}","MSODBCSQL13","1", _
"{053A11C5-BC2B-F7CE-4A10-9D2602643DA0}","{053A11C6-BC2B-F7CE-4A10-9D2602643DA0}","MSODBCSQL17","1", _
"{914ABDE2-171E-C600-3348-C514171DE148}","{914ABDE3-171E-C600-3348-C514171DE148}","System.Data","1", _
"{DCD90923-4953-20C2-8708-01976FB15287}","{DCD90924-4953-20C2-8708-01976FB15287}","System.Data.OracleClient","1", _
"{C9996FA5-C06F-F20C-8A20-69B3BA392315}","{C9996FA6-C06F-F20C-8A20-69B3BA392315}","System.Data.SNI","1", _
"{A68D8BB7-4F92-9A7A-D50B-CEC0F44C4808}","{A68D8BB8-4F92-9A7A-D50B-CEC0F44C4808}","System.Data.Entity","1", _
"{172E580D-9BEF-D154-EABB-83429A6F3718}","{172E580E-9BEF-D154-EABB-83429A6F3718}","SQLJDBC_XA","1"
Server trace names:
!Trace Name!Component! !----------!---------! !SQLBROWSER!SQL Browser! !SQLSERVER.SNI!SQL Server 2005 (unsupported)! !SQLSERVER.SNI10!SQL 2008 and 2008 R2! !SQLSERVER.SNI11!SQL Server 2012! !SQLSERVER.SNI12!SQL Server 2014! !SQL 2016 and later!Use XEvent SNI tracing!