How to retrieve SqlConnection statistics for executed T-SQL commands

Introduction

In this short article, I want to illustrate a sample code describing how you can retrieve statistical information about the T-SQL statements executed over a SqlConnection.

Background

While I was developing the PragmaSQL T-SQL editor, guys using the initial versions wanted to know how many rows were affected as a result of a T-SQL statement (DML) being executed. My first attempt was to trace the T-SQL statements sent by Management Studio to the server. But this was useless, since Management Studio was not sending any extra statements to figure out how many rows were affected. My second attempt was to wire-up to SqlInfoMessageEventHandler and inspect the info messages returned by the server. But, SQL Server was not sending any extra information about the executed statements via this event. Then, while I was looking at the SqlConnection properties, I noticed the StatisticsEnabled property, and this was what would meet my requirement.

Using the code

Retrieving statistical information over a SqlConnection is very simple. Here is the sample code:

//
// Sample T-SQL execute code
//  
private void ExecuteSql(SqlConnection conn, string scriptText)
{
    SqlCommand cmd = null;
    try
    {
        conn.StatisticsEnabled = true;
        conn.ResetStatistics();
        cmd = new SqlCommand(conn, conn);
        cmd.ExecuteNonQuery();
        
        // Here is the sample wrapper to process statistics.
        ProcessConnectionStatistics(conn.RetrieveStatistics());
    }
    finally
    {
        conn.StatisticsEnabled = false;
        if(cmd != null)
         cmd.Dispose();
    }
}


// Sample function to process SqlConnection statistics
// This function only extracts the entry with name IduRows.
// to reflect the rows affected.
private void ProcessConnectionStatistics(IDictionary stats)
{
  foreach (object key in stats.Keys)
  {
    object statVal = stats[key];
    if (key == null || key.ToString().ToLowerInvariant() != 
        "IduRows".ToLowerInvariant())
      continue;

    string statValStr = statVal.ToString();
    if (String.IsNullOrEmpty(statValStr) || statValStr.Trim() == "0")
      continue;

    MessageBox.Show(String.Format("( %s ) rows affected.",statValStr);
  }
}

That is all. You can explore the dictionary items for other statistical data. The SqlConnection statistical data includes timing and other interesting statistics about the T-SQL statements executed.

Notice: Sure, gathering statistical information from the SqlConnection increases the time your scripts take to get executed. I did not measure the increase in time, but in my case, this was unimportant.