SYSK 257: .NET way to analyze SQL data I/O performance

  • Article

Would you like to know how many server round trips it took to retrieve your data using ADO.NET? Would about the number of bytes sent and received? Total connection time? Command execution time? Number of returned rows?

Welcome to the System.Data.SqlClient.SqlStatistics class. While the class itself is unavailable outside the .NET framework (it’s implemented using internal class modifier), we can still using it via RetrieveStatistics method on the connection class.

Here is the information you would get:

  • NetworkServerTime => amount of time (in milliseconds) the data provider spent waiting for replies from the server
  • BytesReceived => number of bytes of data received by the data provider
  • UnpreparedExecs => number of unprepared statements executed
  • SumResultSets => number of result sets
  • SelectCount => number of SELECT statements executed during the connection
  • PreparedExecs => number of prepared commands executed (i.e. the statement(s) has been compiled and an execution plan is prepared)
  • ConnectionTime => amount of time (in milliseconds) that the connection has been opened
  • ExecutionTime => amount of time (in milliseconds) the provider spent processing SQL commands
  • Prepares => number of prepared statements
  • BuffersSent => Number of TDS (tabular data stream) packets sent to SQL Server
  • SelectRows => number of rows selected
  • ServerRoundtrips => number of client/server round trips
  • CursorOpens => number of times a cursor was opened
  • Transactions => number of started transactions (i.e. failed transactions are included in this count).
  • BytesSent => number of bytes sent to SQL Server
  • BuffersReceived => number of TDS packets received by the data provider
  • IduRows => number of rows affected by INSERT/DELETE/UPDATE statements
  • IduCount => number of INSERT/DELETE/UPDATE statements

To get the statistics above, all you need to do is set StatisticsEnabled property to true prior to opening the connection:

cn.StatisticsEnabled = true;

and get the stats at any point you want to see the results (but before disposing the connection) by calling RetrieveStatistics method:

System.Collections.IDictionary stats = cn.RetrieveStatistics();

POINT FOR CONSIDERATION : think of creating a wrapper MySqlConnection class that encapsulates System.Data.SqlClient.SqlConnection class (unfortunately, the later on is sealed, so we can’t simply inherit from it)… In the Open() method, your MySqlConnection class would read the StatisticsEnabled value from a configuration file and set it accordingly (this gives you the flexibility to turn this type of tracing on when you are hunting down a database related performance problem). In the Dispose() method, if the statistics were collected, it would publish them, for example, by firing a DataStats event, so that “interested parties” could receive this event and do what they need with that data following the loosely-coupled event design model. You could have a subscriber of DataStats event that simply logs it to database for further analysis; and another one that looks at execution time and if it’s over a certain threshold, inform proper individuals immediately, etc. And yes, using WMI for this should certainly be considered…