Collect a .NET Core SQL Driver Trace - microsoft/CSS_SQL_Networking_Tools GitHub Wiki
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.
- If you are tracing Microsoft drivers specifically for Windows: Collect a SQL Driver BID Trace
- If you are tracing Microsoft ODBC Drivers for Linux: Collect a SQL Driver BID Trace - near the bottom
- If you are tracing Microsoft JDBC Drivers: https://docs.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver15
- The name of the trace event is Microsoft.Data.SqlClient.EventSource.
- To get a verbose trace of all events, decorate the name with :1FFF:5, e.g. Microsoft.Data.SqlClient.EventSource:1FFF:5
- To include DNS tracing, use Microsoft.Data.SqlClient.EventSource:1FFF:5,System.Net.NameResolution:FFFF:5
- You can use this in PerfView on Windows to trace .NET Core apps on Windows.
PerfView Guide: https://docs.microsoft.com/en-us/sql/connect/ado-net/enable-eventsource-tracing?view=sql-server-ver16#use-perfview-to-collect-trace-log - This is also the trace name used in the examples below.
Note: The maximum possible value for event flags is FFFFFFFFFFFFFFFF
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)
- 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.
This scenario requires two ssh sessions to the Linux machine. A typical command might be:
ssh [email protected]
[email protected]'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 ...
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:
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:1FFF:5
To override build in application tracing and verbose level
.net trace flags The trace flags are not cumulative and the values have to be added together per trace flag that is wanted to be collected.
| Key Name | Flag Value | Hex | Description |
|---|---|---|---|
| ExecutionTrace | 1 | 0001 | Turns on Captureing Start/Stop events before and after command execution |
| Trace | 2 | 0002 | Turns on capturing basic application flow trace events. |
| Scope | 4 | 0004 | Turns on capturing enter and exit events |
| NotificationTrace | 8 | 0008 | Turns on capturing SqlNotification trace events |
| NotificationScope | 16 | 0010 | Turns on capturing SqlNotification scope enter and exit events |
| PoolerTrace | 32 | 0020 | Turns on capturing connection pooling flow trace events. |
| PoolerScope | 64 | 0040 | Turns on capturing connection pooling scope trace events. |
| AdvancedTrace | 128 | 0080 | Turns on capturing advanced flow trace events. |
| AdvancedTraceBin | 256 | 0100 | Turns on capturing advanced flow trace events with additional information. |
| CorrelationTrace | 512 | 0200 | Turns on capturing correlation flow trace events. |
| StateDump | 1024 | 0400 | Turns on capturing full state dump of SqlConnection |
| SNITrace | 2048 | 0800 | Turns on capturing flow trace events from Managed Networking implementation (only applicable in .NET Core) |
| SNIScope | 4096 | 1000 | Turns on capturing scope events from Managed Networking implementation (only applicable in .NET Core) |
| All On | 8191 | 1FFF | Capture all events. |
Verbose level determine the level of logging desired.
| String Value | Numeric Value |
|---|---|
| logalways | 0 |
| critical | 1 |
| error | 2 |
| warning | 3 |
| informational | 4 |
| verbos | 5 |
dotnet-trace ps
8734 DBCoreTest /home/Gandalf/dbcoretest/DBCoreTest
dotnet-trace collect -–process-id 8734 --providers Microsoft.Data.SqlClient.EventSource:1FFF:5
Press ENTER to un-pause the application. This step is only for testing a paused Console app. A service would not require this step.
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
This scenario requires two ssh sessions to the Linux machine. A typical command might be:
ssh [email protected]
[email protected]'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 ...
Set up the trace port:
dotnet-trace collect --diagnostic-port test.sock --providers Microsoft.Data.SqlClient.EventSource:1FFF:5
Provider Name Keywords Level Enabled By
Microsoft.Data.SqlClient.EventSource 0x1FFF 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
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.
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.