Unit testing Sql for logical reads

Question

We have unit tests for our data access layer. This has helped to spot sql syntax errors.
Now that we have these tests I would like to take it one step further. I would like to run the unit tests and find sql that has a high number of logical reads, Automatically.(to find sql in need of tuning)

Adding "set statistics IO" to the sql is not difficult. What I am looking for is a stored procedure where I send it the db and it returns a string containing the logical read information about the sql. I am working in Sybase, but if you know of stored proc/etc that does this in a different DB, that would still be a huge help.

Thanks!

score 1 · Answer 1 · answered Mar 11 '11 at 11:04

This is not possible with a TSQL procedure, but the output can be captured in .net by subscribing to the InfoMessage event on the SqlConnection object.

  ...
  using (var connection = new SqlConnection("connectionstring")
  using (var command = new SqlCommand("SET STATISTICS IO ON"))
  {
      connection.Open();
      connection.InfoMessage += OnInfoMessage;

      using (var reader = command.ExecuteReader())
      {
          ....
      }
  }
  ...

  private static void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
  {
      foreach (SqlError err in args.Errors)
      {
           Console.WriteLine(err.Message);
      }
  }

Unit testing Sql for logical reads

1 Answers1