Simplified steps for creating BID / ETW traces for ADO.Net and SQLNCLI

In order to create BID traces for ADO.Net and/or SQLNCLI/10 follow these simplified steps:

 

#1 On the machine where client code runs, create a new directory called C:\BID

#2 Locate the appropriate diagnostic dll.

 

ADO.Net:

For .Net 2.0, default location is: C:\Windows\Microsoft.NET\Framework\v2.0.50727\AdoNetDiag.dll

For .Net 4.0, default location is: C:\Windows\Microsoft.NET\Framework\v4.0.30319\AdoNetDiag.dll

 

SQLNCLI an SQLNCLI10 (SQL Server Native Client and SQL Server Native Client 10):

Default location is: C:\Windows\System32\Msdadiag.dll

For 32bit application on 64bit machine, default location is: C:\Windows\SysWOW64\Msdadiag.dll

 

#3 Start regedit (Start->Run->regedit) and navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface\Loader

 

#4 Create a new String Value key (REG_SZ) here. Set the name to :Path (with the colon), then set the value to be the full path to the AdoNetDiag.dll or Msdadiag.dll,

i.e. C:\Windows\Microsoft.NET\Framework\v2.0.50727\AdoNetDiag.dll or C:\Windows\System32\Msdadiag.dll

 

Example of exported registry key:

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface\Loader

Name: :Path

Type: REG_SZ

Data: C:\Windows\System32\Msdadiag.dll

 

#5 Start a command prompt as Administrator and navigate to the C:\BID directory.

#6 Execute the following to start tracing:

 

For ADO.Net:

logman start DataAccessBidTrace -p {914ABDE2-171E-C600-3348-C514171DE148} 0x00000000 -ct perf -o DataAccessBidTraceOut.etl -ets

   

SQLNCLI (SQL Server Native Client):

logman start DataAccessBidTrace -p {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} 0xFFFFFFFF -ct perf -o DataAccessBidTraceOut.etl -ets

    

SQLNCLI10 (SQL Server Native Client 10):

logman start DataAccessBidTrace -p {A9377239-477A-DD22-6E21-75912A95FD08} 0xFFFFFFFF -ct perf -o DataAccessBidTraceOut.etl -ets

 

#7 Run the application that you wish to trace. Once issue has repro'd or the event you wish trace has occurred, run the following to stop the tracing:

logman stop DataAccessBidTrace -ets

 

#8 You now have a file in the C:\BID directory called DataAccessBidTraceOut.etl send that to us.

#9 Remove the registry key created in step #4 if you wish.

 

This is a simplified way to BID trace on ADO.Net and SQLNCLI. The full story is here:

"Data Access Tracing in SQL Server 2008"

https://msdn.microsoft.com/en-us/library/cc765421(SQL.100).aspx#_Toc135190849