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.