Analyzing Performance With Connection Statistics

The .NET Framework supports run-time statistics, which are gathered on a per-connection basis. The DB2, Oracle, and Sybase data providers support 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.

Enabling 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.

Note: Applications that use the ADO.NET Entity Framework require some implementation differences from that shown in the examples in this section, although the resulting functionality is the same. See "Using Statistical Items with an ADO.NET Entity Framework Data Provider" for more information.

Overview

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 five 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 statistical items return values that pertain to the number of statements and stored procedures that have been executed.
• Statement cache statistical items return values that describe the activity of statements in a statement cache. For more information on using the statement cache, refer to Using Statement Caching.

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();

See "Statistical Items Supported" for a description of the supported statistical items.

Using Statistical Items with an ADO.NET Entity Framework Data Provider

The Connection object includes properties and methods that provide reauthentication and enhanced statistics functionality. The methods and properties are standard in the ADO.NET data provider, but are not available at the ADO.NET Entity Framework layer. Instead, you expose the same functionality through "pseudo" stored procedures.

This approach uses the Entity Data Model (EDM) to achieve results that correspond to the ADO.NET results. This in effect provides entities and functions backed by pseudo stored procedures.

The following table lists the mapping of the methods and properties of the Connection class to the corresponding pseudo stored procedure.

Mapping to Pseudo Stored Procedure

You can create a function mapping in the entity model to invoke the pseudo-stored procedure. Alternatively, applications can use the ObjectContext to create a stored procedure command as shown in the following C# code fragment:

using (MyContext context = new MyContext())
{
   EntityConnection entityConnection = (EntityConnection)context.Connection;
   // The EntityConnection exposes the underlying store connection
   DbConnection storeConnection = entityConnection.StoreConnection;
   DbCommand command = storeConnection.CreateCommand();
   command.CommandText = "DDTek_Connection_EnableStatistics";
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add(new OracleParameter("cid", 1));
}
 
bool openingConnection = command.Connection.State == ConnectionState.Closed;
if (openingConnection) { command.Connection.Open(); }
int result;
try
{
   result = command.ExecuteNonQuery();
}
finally
{
   if (openingConnection && command.Connection.State == ConnectionState.Open)
   {
      command.Connection.Close();
   }
}

Statistical Items Supported

When you enable statistics gathering on a Connection object and then connect to a database, the data provider begins measuring all of the supported statistical items. You can retrieve all of the statistical items, or select only the specific items in which you are interested.

The following table lists the statistical items supported by the data providers, grouped by functional categories, and describes each statistical item. The statistical items have the form:

item=value

Note: The statistical items are returned in unsorted order. The categories shown in the following table are used only to clarify the types of statistical items available.

Statistical Items

Tuning Statistics Gathering

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

As a general good practice, be careful to return only the rows you need. If you return five rows when you only need two rows, performance is decreased. Performance is further decreased when statistics gathering is enabled, because the data provider must dispose of the unread rows.

When you let the database filter the rows for you, only the data that you need is sent across the network to the application. The following SQL statement is carefully written so that the application reads all rows retrieved:

SELECT * FROM employee WHERE hiredate > 2000 AND job_title='Manager'

The statistical items returned show that there is virtually no cost associated with disposing rows.

TimeToDisposeOfUnreadRows = 9.07936623230047E-05
SocketReadsToDisposeOfUnreadRows = 0
BytesReceivedToDisposeOfUnreadRows = 0
SelectRowsRead = 1000

With a less focused SQL statement, the application returns 1000 rows and reads only half of the rows returned. Disposing 500 rows has a cost.

TimeToDisposeOfUnreadRows = 0.56059694737739
SocketReadsToDisposeOfUnreadRows = 344
BytesReceivedToDisposeOfUnreadRows = 87989
SelectRowsRead = 500

Suppose the application reads none of the 1000 rows returned. The cost of disposing all 1000 rows roughly doubles.

TimeToDisposeOfUnreadRows = 1.12182134880271
SocketReadsToDisposeOfUnreadRows = 692
BytesReceivedToDisposeOfUnreadRows = 177077
SelectRowsRead = 0

If you cannot properly qualify your queries so that the application reads all the rows you request of the server, you might be able to mitigate the effect on performance of disposing of unread rows using a connection string option. For the Sybase data provider, use ReaderCloseBehavior=0. This option cancels the query and efficiently processes the remaining reply. Running with this option improves the results of the previous examples dramatically.

Suppose the application reads 500 of the 1000 rows. Disposing of 500 rows is 10 times faster!

TimeToDisposeOfUnreadRows = 0.0508947366215539
SocketReadsToDisposeOfUnreadRows = 34
BytesReceivedToDisposeOfUnreadRows = 8313
SelectRowsRead = 500

However, if the application reads 0 of the 1000 rows, disposing of all 1000 rows is roughly the same cost as 500 rows.

TimeToDisposeOfUnreadRows = 0.0513160192147326
SocketReadsToDisposeOfUnreadRows = 34
BytesReceivedToDisposeOfUnreadRows = 8349
SelectRowsRead = 0