Using Diagnostic Features

Using Diagnostic Features

The .NET Framework provides a Trace class that can help end users identify the problem without the program having to be recompiled.

The PSQL ADO.NET Data Provider delivers additional diagnostic capability:

•

Ability to trace method calls

•

Performance Monitor hooks that let you monitor connection information for your application

Tracing Method Calls

Tracing capability can be enabled either through environment variables or the PsqlTrace class. The data provider traces the input arguments to all of its public method calls, as well as the outputs and returns from those methods (anything that a user could potentially call). Each call contains trace entries for entering and exiting the method.

During debugging, sensitive data can be read, even if it is stored as a private or internal variable and access is limited to the same assembly. To maintain security, trace logs show passwords as five asterisks (*****).

Using Environment Variables

Using environment variables to enable tracing means that you do not have to modify your application. If you change the value of an environment variable, you must restart the application for the new value to take effect.

Table 3 describes the environment variables that enable and control tracing.

Table 3

Environment Variables

Environment Variable

PVSW_NET_Enable_Trace

If set to 1 or higher, enables tracing. If set to 0 (the default), tracing is disabled.

PVSW_NET_Recreate_Trace

If set to 1, recreates the trace file each time the application restarts. If set to 0 (the default), the trace file is appended.

PVSW_NET_Trace_File

Specifies the path and name of the trace file.

•

Setting PVSW_NET_Enable_Trace = 1 starts the tracing process. Therefore, you must define the property values for the trace file before enabling the trace. Once the trace processing starts, the values of the other environment variables cannot be changed.

•

If tracing is enabled and no trace file is specified by either the connection string option or the environment variable, the data provider saves the results to a file named PVSW_NETTrace.txt.

Using Static Methods

Some users may find that using static methods on the data provider’s Trace class to be a more convenient way to enable tracing. The following C# code fragment uses static methods on the .NET Trace object to create a PsqlTrace class with a trace file named MyTrace.txt. The values set override the values set in the environmental variables. All subsequent calls to the data provider will be traced to MyTrace.txt.

PsqlTrace.TraceFile="C:\\MyTrace.txt";

PsqlTrace.RecreateTrace = 1;

PsqlTrace.EnableTrace = 1;

The trace output has the following format:

<Correlation#> <Timestamp> <CurrentThreadName>
<Object Address> <ObjectName.MethodName> ENTER (or EXIT)

Argument #1 : <Argument#1 Value>

Argument #2 : <Argument#2 Value>

RETURN: <Method ReturnValue> // This line only exists for EXIT

Correlation# is a unique number that can be used to match up ENTER and EXIT entries for the same method call in an application.

Value is the hash code of an object appropriate to the individual function calls.

During debugging, sensitive data can be read, even if it is stored as private or internal variable and access is limited to the same assembly. To maintain security, trace logs show passwords as five asterisks (*****).

PerfMon Support

The Performance Monitor (PerfMon) and VS Performance Monitor (VSPerfMon) utilities allow you to record application parameters and review the results as a report or graph. You can also use Performance Monitor to identify the number and frequency of CLR exceptions in your applications. In addition, you can fine-tune network load by analyzing the number of connections and connection pools being used.

The data provider installs a set of PerfMon counters that let you tune and debug applications that use the data provider. The counters are located in the Performance Monitor under the category name PSQL ADO.NET data provider.

Table 4 describes the PerfMon counters that you can use to tune connections for your application.

Table 4

PerfMon Counters

Current # of Connection Pools

Current number of pools associated with the process.

Current # of Pooled and Non-Pooled Connections

Current number of pooled and non-pooled connections.

Current # of Pooled Connections

Current number of connections in all pools associated with the process.

Peak # of Pooled Connections

The highest number of connections in all connection pools since the process started.

Total # of Failed Commands

The total number of command executions that have failed for any reason since the process started.

Total # of Failed Connects

The total number of attempts to open a connection that have failed for any reason since the process started.

For information on using PerfMon and performance counters, refer to the Microsoft documentation library.

Analyzing Performance With Connection Statistics

The .NET Framework 2.0 and higher supports run-time statistics, which are gathered on a per-connection basis. The PSQL ADO.NET Data Provider supports a wide variety of run-time statistical items. These statistical items provide information that can help you to:

•

Automate analysis of application performance

•

Identify trends in application performance

•

Detect connectivity incidents and send notifications

•

Determine priorities for fixing data connectivity problems

Measuring the statistics items affects performance slightly. For best results, consider enabling statistics gathering only when you are analyzing network or performance behavior in your application.

Statistics gathering can be enabled on any Connection object, for as long as it is useful. For example, you can define your application to enable statistics before beginning a complex set of transactions related to performing a business analysis, and disable statistics when the task is complete. You can retrieve the length of time the data provider had to wait for the server and the number of rows that were returned as soon as the task is complete, or wait until a later time. Because the application disables statistics at the end of the task, the statistical items are measured only during the period in which you are interested.

Functionally, the statistical items can be grouped into four categories:

•

Network layer items retrieve values associated with network activities, such as the number of bytes and packets that are sent and received and the length of time the data provider waited for replies from the server.

•

Aggregate items return a calculated value, such as the number of bytes sent or received per round trip to the server.

•

Row disposition statistical items provide information about the time and resources required to dispose of rows not read by the application.

•

Statement cache statistical items return values that describe the activity of statements in a statement cache (see Using Statement Caching for more information on using the statement cache).

Enabling and Retrieving Statistical Items

When you create a Connection object, you can enable statistics gathering using the StatisticsEnabled property. The data provider begins the counts for the statistical items after you open a connection, and continues until the ResetStatistics method is called. If the connection is closed and reopened without calling ResetStatistics, the count on the statistical items continues from the point when the connection was closed.

Calling the RetrieveStatistics method retrieves the count of one or more statistical items. The values returned form a "snapshot in time" at the moment when the RetrieveStatistics method was called.

You can define the scope for the statistics gathering and retrieval. In the following C# code fragment, the statistical items measure only the Task A work; they are retrieved after processing the Task B work:

connection.StatisticsEnabled = true;

// do Task A work

connection.StatisticsEnabled = false;

// do Task B work

IDictionary currentStatistics = connection.RetrieveStatistics();

To view all the statistical items, you can use code like the following C# code fragment:

foreach (DictionaryEntry entry in currentStatistics) {

Console.WriteLine(entry.Key.ToString() + ": " + entry.Value.ToString());

Console.WriteLine();

To view only the SocketReads and SocketWrites statistical items, you can use code like the following C# code fragment:

foreach (DictionaryEntry entry in currentStatistics) {

Console.WriteLine("SocketReads = {0}",
currentStatistics["SocketReads"]);

Console.WriteLine("SocketWrites = {0}",
currentStatistics["SocketWrites"]);

Console.WriteLine();

Note for ADO.NET Entity Framework Users: The PsqlConnection methods and properties for statistics are not available at the ADO.NET Entity Framework layer. Instead, the data provider exposes the same functionality through "pseudo" stored procedures. See Using Stored Procedures with the ADO.NET Entity Framework for more information.