WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Tracing allows you to keep track of various events happening in the system. Tracing allows you to track the sequence of code execution along with the time taken for each step. Together, tracing can throw light on how your code is being executed and the performance of every step. Many real world applications are database driven and call for performance optimization of your data access code. Luckily, ADO.NET 2.0 provides built-in support for trace logging. The ADO.NET trace logging is tuned for performance and uses Event Tracing for Windows (ETW) providers. This article explains how you can configure the ADO.NET trace logging feature.
Enabling ADO.NET Trace Logging
The ADO.NET trace logging is not enabled by default. To enable it, you need to follow these steps:
- Add certain Registry entries
- Configure ETW providers for ADO.NET
- Create a trace log using the Logman command line tool
- Start the tracing
Add Required Registry Entries
The trace logging feature of ADO.NET is dependent on a DLL named AdoNetDiag.dll. This DLL is located in the installation folder of .NET. The following figure shows this DLL in Windows Explorer.
The AdoNetDiag.dll is a component that makes any class library designed for trace logging appear as an ETW provider. In the example, it will make assemblies such as System.Data.dll appear as ETW providers.
Now, open the Windows Registry editor (regedit.exe) and locate the following key:
By default, the BidInterface key will not have any sub-key. Add a sub-key to BidInterface named Loader. Then, add a string value under Loader with a name such as :Path and a value as the full path and name of AdoNetDiag.dll assembly (see below).
Configure ETW Providers for ADO.NET
Now, you need to configure AdoNetDiag.dll so that it is visible on a public ETW provider list as well as the WMI (Windows Management Instrumentation) provider list. This can be done with the help of the mofcomp.exe command line tool. The mofcomp.exe tool expects a .mof (Managed Object Format) file containing details about the objects to be added to the WMI repository. For AdoNetDiag.dll, the required .mof file is available in the same folder as the AdoNetDiag.dll itself (see below).
Open Command Prompt and issue the following command:
mofcomp.exe C:\Windows\Microsoft.Net\Framework\v2.0.50727\ AdoNetDiag.mof
The following figure shows a successful run of the above command. Make sure to replace the path of AdoNetDiag.mof file as per your installation.
To ensure that the ETW providers are enabled correctly, you can use the logman.exe command line tool. Issue the following command at the command prompt:
logman.exe list providers
The above command will display a list of ETW providers. The following figure shows a sample run of this command.
Out of the providers listed in the above figure, the following were registered because of the mofcomp.exe tool and AdoNetDiag.mof file.
|ADONETDIAG.ETW||This is a provider for events in the ETW adapter|
|System.Data.OracleClient.1||This is a provider for events in the System.Data.OracleClient.dll assembly|
|System.Data.SNI.1||This is a provider for the SQL Server Network Interface (SNI)-related events from System.Data.dll assembly|
|System.Data.1||This is a provider for events in the System.Data.dll assembly|
Create a Trace Log by Using the Logman Tool
Now that you have published the required ETW providers, it's time to create a trace log. You do that with the help of the Logman command line tool. Before you use this tool, however, you need to create a text file that supplies information about the providers to be used. In the example, the text file will look like this:
"ADONETDIAG.ETW" 0x2 0x0 ADONETDIAG.ETW "System.Data.1" 0x2 0x0 System.Data.1 "System.Data.SNI.1" 0x2 0x0 System.Data.SNI.1
The four columns indicate Provider, control bit, logging level, and provider name. The provider can be specified as a string value enclosed in double quotes or a GUID. The control bit is used to filter the trace. 0x2 means basic trace information. The logging level indicates what will be logged. The value of 0x0 means normal. Finally, the provider name indicates the name of the provider as expected by ETW.
Create the tab-delimited file above and save it as ProviderInfo.txt in a local folder on your machine.
Now, start a trace log by using logman tool as shown below:
logman.exe start MyTrace -pf providerinfo.txt -o MyTraceLog.etl -ets
In the above command:
- start indicates that you want to start a new trace log named MyTrace
- The -pf switch supplies a file containing the list of providers to be used. Remember to use the correct path of ProviderInfo.txt file as per your setup
- The -o switch indicates the output file to store the log information. The log information is stored in binary format
- The -ets switch indicates that the trace collection definition will be persisted in the system