ADO.NET 2.0 new SqlClient Provider Statistics

Presenting ODBC-style statistics for SqlClient. SqlClient statistics are strictly per connection, this should be obvious looking at how the API has been designed. Statistics are not kept for operations that are canceled or datareaders closed before their result-set is fully retrieved. These statistics are not going to map one to one to the old ODBC statistics, for example we do not support CurrentStmtCount, MaxOpenStmt and SumOpenStmt.

using System.Data.Common;

using System.Collections;

using System;

using System.Data.SqlClient;

namespace DataViewer.Repro

{

            public class Repro

            {

                        public static int Main(string[] args)

                        {

                                    using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;"))

                                    {

                                                sqlconnection1.StatisticsEnabled = true;

                                                sqlconnection1.Open();

                                                SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                                                sqlcommand1.CommandText = "sp_who";

                                                sqlcommand1.ExecuteNonQuery(); // -1

                                                IDictionary result = sqlconnection1.RetrieveStatistics();

                                                foreach (DictionaryEntry entry in result)

                                                {

                                                            Console.WriteLine(entry.Key + " = " + entry.Value);

                                                }

                        }

                                    return 1;

                        }

            }

}

All the base counters for statistics use the CLR int64 type, when the counters reach int64.MaxValue they should no longer be considered accurate.

 

Code Output (and comments on what each entry means) :

NetworkServerTime = 0 //amount of time the provider spends waiting for replies from the server.

BytesReceived = 10466 //Number of bytes of data in TDS packets received by the provider.

UnpreparedExecs = 1 //Number of unprepared statements executed.

SumResultSets = 1 //Number of result sets used.

SelectCount = 3 //Number of SELECT statements executed through this connection (I guess sp_who does two selects under the covers?)

PreparedExecs = 0 //Number of prepared commands executed.

ConnectionTime = 31 //amount of time that the connection has been opened.

ExecutionTime = 31 //amount of time the provider spent processing.

Prepares = 0 //statements prepared.

BuffersSent = 1 //TDS packets sent to Sql Server

SelectRows = 28 //Number of rows selected ServerRoundtrips = 1

CursorOpens = 0 //Number of times a cursor was opened.

Transactions = 0 //Number of transactions started, includes rollbacks. When a connection is running with autocommit on each command is considered a transaction. This counter increments as soon as Begin Tran is executed and regardless of whether the transaction is committed or rolled back later.

BytesSent = 42 //number of bytes sent to Sql Server

BuffersReceived = 2 //Number of TDS packets received by the provider

IduRows = 0 //Number of rows affected by INSERT, DELETE or UPDATE statements.

IduCount = 0 //number of INSERT, DELETE and UPDATE statements

Rambling out: Standard disclaimer: This post is provided “AS IS” and confers no rights.

Comments

• Anonymous
  August 04, 2004
  The comment has been removed
• Anonymous
  August 04, 2004
  The comment has been removed
• Anonymous
  August 05, 2004
  Angel:
  
  That's the identical code i used. The weird thing was that it was almost as if it was alternating making me think something else was going on. I have the code at home but basically i just commented out the ExecuteNonQuery with that piece you have up there. I've got a little time to play with it later tonight so I can document it a little better.
  
  You have no idea how much we appreciate cool featuress ;-)