title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | dev_langs | |
---|---|---|---|---|---|---|---|---|---|---|
Enable event tracing in SqlClient |
Describes how to enable event tracing or logging in SqlClient by implementing an event listener and how to access the event data. |
David-Engel |
davidengel |
davidengel |
08/21/2024 |
sql |
connectivity |
conceptual |
|
[!INCLUDE appliesto-netfx-netcore-netst-md]
[!INCLUDE Driver_ADONET_Download]
Event Tracing for Windows (ETW) is an efficient, kernel-level, tracing facility that lets you log driver-defined events for debugging and testing purposes. SqlClient supports capturing ETW events at different informational levels. To begin capturing event traces, client applications should listen for events from SqlClient's EventSource implementation:
Microsoft.Data.SqlClient.EventSource
The current implementation supports the following Event Keywords:
Keyword name | Value | Description |
---|---|---|
ExecutionTrace | 1 | Turns on capturing Start/Stop events before and after command execution. |
Trace | 2 | Turns on capturing basic application flow trace events. |
Scope | 4 | Turns on capturing enter and exit events |
NotificationTrace | 8 | Turns on capturing SqlNotification trace events |
NotificationScope | 16 | Turns on capturing SqlNotification scope enter and exit events |
PoolerTrace | 32 | Turns on capturing connection pooling flow trace events. |
PoolerScope | 64 | Turns on capturing connection pooling scope trace events. |
AdvancedTrace | 128 | Turns on capturing advanced flow trace events. |
AdvancedTraceBin | 256 | Turns on capturing advanced flow trace events with additional information. |
CorrelationTrace | 512 | Turns on capturing correlation flow trace events. |
StateDump | 1024 | Turns on capturing full state dump of SqlConnection |
SNITrace | 2048 | Turns on capturing flow trace events from Managed Networking implementation (only applicable in .NET Core) |
SNIScope | 4096 | Turns on capturing scope events from Managed Networking implementation (only applicable in .NET Core) |
The following example enables event tracing for a data operation on the AdventureWorks sample database and displays the events in the console window.
[!code-csharp SqlClientEventSource#1]
Microsoft.Data.SqlClient provides event tracing support in Microsoft.Data.SqlClient.SNI and Microsoft.Data.SqlClient.SNI.runtime starting with v2.1. Events can be collected from the native DLLs using the Xperf and PerfView tools.
Starting with Microsoft.Data.SqlClient v3.0, event tracing can be enabled without any modifications in the client application using event collection tools.
With Microsoft.Data.SqlClient v2.1, event tracing needs to be enabled by configuring the EventCommand
with an event source listener. The valid EventCommand
values applicable to Native SNI are:
// Enables trace events:
EventSource.SendCommand(eventSource, (EventCommand)8192, null);
// Enables flow events:
EventSource.SendCommand(eventSource, (EventCommand)16384, null);
// Enables both trace and flow events:
EventSource.SendCommand(eventSource, (EventCommand)(8192 | 16384), null);
The following example enables event tracing in native SNI DLLs.
// Native SNI tracing example
using System;
using System.Diagnostics.Tracing;
using Microsoft.Data.SqlClient;
public class SqlClientListener : EventListener
{
protected override void OnEventSourceCreated(EventSource eventSource)
{
if (eventSource.Name.Equals("Microsoft.Data.SqlClient.EventSource"))
{
// Enables both trace and flow events
EventSource.SendCommand(eventSource, (EventCommand)(8192 | 16384), null);
}
}
}
class Program
{
static string connectionString = @"Data Source = localhost; Initial Catalog = AdventureWorks;Integrated Security=true;";
static void Main(string[] args)
{
// Event source listener configuration is not required in v3.0 onwards.
using (SqlClientListener listener = new SqlClientListener())
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
}
}
}
-
Start tracing using the following command.
xperf -start trace -f myTrace.etl -on *Microsoft.Data.SqlClient.EventSource
-
Run the native SNI tracing example to connect to SQL Server.
-
Stop tracing using the following command line.
xperf -stop trace
-
Use PerfView to open the myTrace.etl file specified in Step 1. The SNI tracing log can be found with
Microsoft.Data.SqlClient.EventSource/SNIScope
andMicrosoft.Data.SqlClient.EventSource/SNITrace
event names.
-
Start PerfView and run
Collect > Collect
from the menu bar. -
Configure the trace file name, output path, and provider name.
-
Start collection.
-
Run the native SNI tracing example to connect to SQL Server.
-
Stop collection from PerfView. It takes a while to generate the PerfViewData.etl file according to the configuration in Step 2.
-
Open the
etl
file in PerfView. The SNI tracing log can be found withMicrosoft.Data.SqlClient.EventSource/SNIScope
andMicrosoft.Data.SqlClient.EventSource/SNITrace
event names.
On Linux, PerfCollect can be used to capture traces. PerfCollect is a bash script that uses .NET tools to collect traces for .NET applications. For more information about PerfCollect, see Performance Tracing on Linux The traces created by PerfCollect can be viewed in PerfView.
-
If not already installed, install curl (Ubuntu commands are provided):
sudo apt-get update sudo apt-get install curl
-
Install PerfCollect:
sudo curl –OL http://aka.ms/perfcollect sudo chmod +x perfcollect sudo ./perfcollect install
-
Define environment variables for PerfCollect:
export COMPles_perfMapEnab;ed=1 export COMPlus_EnableEventLog=1
-
Collect logs:
sudo ./perfcollect collect <trace file name>
Run the application separately and let it run as long as needed to reproduce the issue. If it's a high CPU issue, 5-10 seconds is usually enough. Press CTRL+C to stop tracing.
Copy the trace file to another system and view it in PerfView.
For another set of examples on how to trace Microsoft.Data.SqlClient cross-platform, see the CSS SQL Networking Tools wiki.
For more information about event tracing, see the following resources.
Resource | Description |
---|---|
EventSource Class | Used to create ETW events. |
EventListener Class | Provides methods for enabling and disabling events from event sources. |