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

  1. Bill says:

    Everythign ‘worked’ I’ve been banging on it for a good while here. I’m actually surprised b/c I was expecting the need to specify a commandtype – noticed your code didn’t have it so I figured it will still work. Alas it did. Even tried breaking it by turning off EnabledStatistics but everythign still worked really well.

    But, you get quite different behavior between ExecuteReader and ExecuteNonQuery (and reading through the reader didn’t seem to cause any distinction):

    dataReader w/ only ExcecuteReaderCalled

    BytesReceived = 6866

    UnpreparedExecs = 1

    SumResultSets = 0

    SelectCount = 1

    PreparedExecs = 0

    ConnectionTime = 721

    ExecutionTime = 0

    Prepares = 0

    BuffersSent = 1

    SelectRows = 1

    ServerRoundtrips = 1

    CursorOpens = 0

    Transactions = 0

    BytesSent = 24

    BuffersReceived = 1

    IduRows = 0

    IduCount = 0

    //Items are identical whether or not I read through

    //the reader or not

    Same query w/ ExecuteNonQuery

    NetworkServerTime = 0

    BytesReceived = 6866

    UnpreparedExecs = 1

    SumResultSets = 1

    SelectCount = 3

    PreparedExecs = 0

    ConnectionTime = 0

    ExecutionTime = 0

    Prepares = 0

    BuffersSent = 1

    SelectRows = 19

    ServerRoundtrips = 1

    CursorOpens = 0

    Transactions = 0

    BytesSent = 24

    BuffersReceived = 1

    IduRows = 0

    IduCount = 0

    The part that really stumps me is the Select(s). 3 in the second case, 1 in the first? Everything else is exactly what I’d expect though and this difference has to be a Sql Server thing not really an ado.net issue.

    This really stands to be a Killer feature for monitoring – you could just wire this into a Trace.Listener for instance and get some great feedback quite easily.

    BTW, last night I was writing a Compact Framework SqlCe app and the SqlDbType.Xml started working in VS.NET – IN THE CF. I double checked the includes statements and it’s identical to the project that didn’t work. Nonetheless everything is working now. Anyway, absolutely loving the samples – many thanks!

  2. Angel says:

    The SelectCount = 3 is also bothering me, I will see what I can find out.

    I am not able to repro your SelectCount=1 when I read through the DataReader. All I do is replace the ExecuteNonQuery line with:

    SqlDataReader reader = sqlcommand1.ExecuteReader();

    while (reader.Read()){} //without this line I see SelectCount=1.

    And Bill, thank you for playing with these features!! You have no idea how much we value your enthusiasm.

  3. Bill says:

    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 😉