Skip to content

Collect a .NET Core SQL Driver Trace

Jump to bottom
Malcolm Stewart edited this page Aug 22, 2022 · 21 revisions

Collect a .NET Core SQL Driver Trace

.NET Driver Trace vs. a BID Trace

BID Traces rely on the Event Tracing for Windows (ETW) infrastructure. This is what Windows drivers use. However, .NET Core apps are not targeted specifically at Windows and therefore cannot use the ETW infrastructure. Fortunately, the .NET Core run-time provides the DOTNET-TRACE command to collect these traces on non-Windows operating systems.

Trace Event Name

  • The name of the trace event is Microsoft.Data.SqlClient.EventSource.
  • You can use this in PerfView on Windows to trace .NET Core apps on Windows.
  • This is also the trace name used in the examples below.

Prerequisites for Linux Tracing

Download the dotnet tool: Install .NET on Linux distributions - .NET

e.g. for Ubuntu using apt-get:

	sudo apt-get install -y dotnet-runtime-6.0

Installing dotnet-trace:

	dotnet tool install --global dotnet-trace

SSH is used for the command windows.
WinSCP is used to copy the files to the Windows machine.

Download PerfView: Releases · microsoft/perfview (github.com)

Test Application Details

  • The test app is a .NET Core 5.0 application using Microsoft.Data.SqlClient 4.x. It is a self-contained distribution.
  • The app name is DBCoreTest
  • The app folder is dbcoretest
  • The running user is Gandalf, but the trace user could be another account.

Tracing an Already Running Application

This scenario requires two ssh sessions to the Linux machine. A typical command might be:

ssh Gandalf@10.10.10.10
Gandalf@10.10.10.10's password:
Welcome to Ubuntu 18.04.6 LTS (GNU/Linux 5.4.0-1089-azure x86_64)
...
Gandalf@ubuntuvm:~$

Once both sessions are open ...

Application Window

We have to start our test app and pause it, but something like a .NET Core web service would already be running, in which case this step can be skipped.

cd dbcoretest

./DBCoreTest -connect "Server=SQLProd01;database=northwind;...;encrypt=false" -command "Select * from customers" -wait

Press ENTER to begin the test:

Trace Window

Get the process ID and start the trace. If you have multiple applications, choose the process ID of the application to trace.

dotnet-trace ps
8734  DBCoreTest  /home/Gandalf/dbcoretest/DBCoreTest

dotnet-trace collect –process-id 8734 --providers Microsoft.Data.SqlClient.EventSource

Application Window

Press ENTER to un-pause the application. This step is only for testing a paused Console app. A service would not require this step.

Trace Window

This will show the trace status. You can terminate the trace early by CTRL+C (tracing a service) or let it run to the end (console app).

[00:00:01:03]   Recording trace 1.2843   (MB)
Press <Enter> or <Ctrl+C> to exit...

Trace completed.

Once the trace is complete, copy the trace file to a Windows machine and analyze it with PerfView.
The trace file name will be similar to this: DBCoreTest_20220726_194805.nettrace

Tracing an Application from Startup

This scenario requires two ssh sessions to the Linux machine. A typical command might be:

ssh Gandalf@10.10.10.10
Gandalf@10.10.10.10's password:
Welcome to Ubuntu 18.04.6 LTS (GNU/Linux 5.4.0-1089-azure x86_64)
...
Gandalf@ubuntuvm:~$

Once both sessions are open ...

Trace Window

Set up the trace port:

dotnet-trace collect --diagnostic-port test.sock --providers Microsoft.Data.SqlClient.EventSource

Provider Name                           Keywords            Level               Enabled By
Microsoft.Data.SqlClient.EventSource    0xFFFFFFFFFFFFFFFF  Verbose(5)          --providers

Waiting for connection on /home/Gandalf/test.sock
Start an application with the following environment variable: DOTNET_DiagnosticPorts=/home/Gandalf/test.sock
Process        : test.sock
Output File    : /home/Gandalf/test.sock_20220822_143811.nettrace

Application Window

Change to the application folder and export the environment variable. Start the application.

cd dbcoretest
export DOTNET_DiagnosticPorts=/home/Gandalf/test.sock

./DBCoreTest -connect "Server=SQLProd01;database=northwind;...;encrypt=false" -command "Select * from customers"
...
All Tests Complete.

Trace Window

This will show the trace status. You can terminate the trace early by CTRL+C (tracing a service) or let it run to the end (console app).

[00:00:01:03]   Recording trace 1.2843   (MB)
Press <Enter> or <Ctrl+C> to exit...

Trace completed.

Copy /home/Gandalf/test.sock_20220822_143811.nettrace to a Windows machine folder and analyze with PerfView.

Clone this wiki locally