C# Data Access Tracing for ADO.Net - Need to do profiler to check what are all connection is being shared across multiple threads

Question

I have tried to enable Data Access Tracing using ETW as explained in this article.

To verify that it traces the event, I have created a simple console application instead of the MVC application as it is there in the article. Code as follows, it is intended to throw so i can verify the trace works.

var connectionString = "Data Source=local;Trusted_Connection = True; Initial Catalog = Data; Timeout = 2; Max Pool Size = 4";
using (var conn = new SqlConnection(connectionString))
{
   using (var command = new SqlCommand("SELECT * FROM DATA", conn))
   {
      conn.Open();
      var reader = command.ExecuteReader();
      using (var data = new SqlCommand("SELECT * FROM PEOPLE", conn))
      {
           var reader1 = command.ExecuteReader();
      }
   }
}

I am using .Net Framework v4.0.30319. Ideally i want to trace the connection objects which are being shared between threads. Any help would be much appreciated!

Thanks

[register the MOFs](https://msdn.microsoft.com/en-us/library/cc765421.aspx), use [Perview](https://stackoverflow.com/a/46022660/1466046)->Collect->provider-browser and add everything from ADO.net and SQL, click on start and run your code. after code was running, go to perfview and stop logging and look for the ADO/SQL events — magicandre1981, Sep 08 '17 at 14:12

score 2 · Answer 1 · answered Jan 08 '18 at 17:14

TL;DR Try registering the other .dll/mof pairs instead of the one recommended by your guide.

The steps to manually register the DLL (modified from this MS guide)

Add the following registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface\Loader
Create a new string value (note the ':' in the key) and Modify... it

Value Name = ":Path"

Value Data = "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\AdoNetDiag.dll"
Register ETW Providers in your WMI repository with mofcomp.exe (installed with windows)

mofcomp.exe C:\Windows\Microsoft.NET\Framework64\v4.0.30319\adonetdiag.mof

To test:

logman start test -p "System.Data.1" -o test.etl -ets // Start a trace
// Do things with ADO.NET (run your sample)
logman stop test -ets // Stop a started trace
tracerpt.exe test.etl -of CSV -o dump.csv // make it human readable

If things worked, you should now have a summary.txt and dump.csv file in the same directory filled with tracing information. Good luck.

Which ADONETDIAG.dll did you register with those steps? I followed these (they appear to be the ones Microsoft published that everyone else uses for their guides) and found them to work with one gotcha.

The one gotcha I discovered is that there can be multiple copies of ADONETDIAG.dll installed on your machine - and the DLL you register matters! For example, my machine has one installed in the following locations

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\AdoNetDiag.dll
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\AdoNetDiag.dll
C:\Windows\Microsoft.NET\Framework\v2.0.50727\AdoNetDiag.dll
C:\Windows\Microsoft.NET\Framework\v4.0.30319\AdoNetDiag.dll

All of the steps I've found direct you to register \Framework\v2.0.50727\AdoNetDiag.dll. When I tried this I was not getting any logs (my .etl files capture 1 event, the trace start, and never grow beyond 8KB).

Trial and error led to discovering \Framework64\v4.0.30319\AdoNetDiag.dll working for me.

C# Data Access Tracing for ADO.Net - Need to do profiler to check what are all connection is being shared across multiple threads

1 Answers1