SqlConnection.RetrieveStatistics in .NET 2.0

The SqlConnection object has a new RetrieveStatistics method in .NET 2.0, which provides some interesting statistics that could come in handy while debugging and performance tuning your .NET applications.  Normally statistics are turned off by default, so you have to set StatisticsEnabled = true in order for the SqlConnection object to begin collecting statistics.

using (SqlConnection connection =

        new SqlConnection(connectionString))

      {

        connection.StatisticsEnabled = true;
        string sql = “SELECT * FROM Products“;

        SqlDataAdapter adapter =

          new SqlDataAdapter(sql, connection);
        DataSet dataSet = new DataSet();
        adapter.Fill(dataSet, “Products“);
        IDictionary statistics =

          connection.RetrieveStatistics();
        long serverRoundtrips =

            (long) statistics["ServerRoundtrips"];

        long connectionTime =

            (long) statistics["ConnectionTime"];

        long bytesReceived =

            (long) statistics["BytesReceived"];

        long sumResultSets =

            (long) statistics["SumResultSets"];

        long selectRows =

            (long) statistics["SelectRows"];
        // …



      }

There are 18 different statistics gathered at the moment.  You could loop through them all as opposed to requesting one at a time:

foreach (DictionaryEntry entry in statistics)

{

  Console.WriteLine(“Key: {0}, Value: {1}“,

    entry.Key.ToString(), entry.Value.ToString());

}

Related Posts

• ADO.NET 2.0 Provider Model Fundamentals


• DBConnectionStringBuilder and SqlConnectionStringBuilder in ADO.NET 2.0


• SQL Injection Attacks and Parameterized Queries


• SQL Server Transaction Savepoints


• SQL Server Transactions


• Optimistic Concurrency in SQL Server

Drinking:  Dragon Well Green Tea