Recently, a question came up regarding whether frequent logins from .NET applications--as observed with the prefmon counter SQLServer:General Statistics\Logins/sec--would have a significant performance impact. This is obvious an empirical question that is best answered with test data. In particular, I want to explore the extreme case beyond the kind of login frequency one may see in any real application.

Well, this question is not really about frequent logins. Rather, it is about the impact of frequently calling the SqlConnection.Open() and Close() methods with connection pooling enabled and the Connection Reset option set to true. Note that if Connect Reset is set to false, the SqlConnection.Open() method does not result in the increment of the Logins/sec counter.

So I wrote a little .NET test program to do nothing but generate frequent SQL2005 logins by putting the following code in a loop in each thread:

  SqlConnection cxn = new SqlConnection(connectString);
  while (true)
  {
      try { 
           cxn.Open();
      }
      catch(SqlException e) {
         Console.WriteLine("Open() failed with " + connectString);
         Console.WriteLine(e.ToString());
         throw e;
      }
      SqlDataAdapter sqlDa = new SqlDataAdapter("select 1 as 'abc';", cxn);
      DataTable dt = new DataTable();
      try {
           sqlDa.Fill(dt);
           foreach (DataRow dr in dt.Rows)
             {};
      }
      catch (SqlException e) {
            Console.WriteLine(e.ToString());
      }

      cxn.Close();
      Thread.Sleep(10);
  }

The number of logins per second is controlled by changing the connection string and the number of threads. The connection string looks like the following:

"server=NJSQL123;user=abc;pwd=abc;Max Pool Size=100;pooling=true"

The following chart shows the results from a SQL2005 instance (9.00.3042) running on a machine that has four 2.6GHz processors and 16GB of RAM (the amount of RAM is not a real factor in this case since only a trivial query is run on each connection):

So on this machine, SQL2005 can support up to 6000~7000 logins/sec without taxing the processors at all. However, the processor consumption dramatically jumps to more than 80% between 7000~9000 logins/sec and beyond. I don't know why the processor consumption does not ramp gradually.

Now, you probably would never see 6000 logins/sec in any real application. 2000 logins/sec may already be very high in real applications.

The news is generally good in that if you are writing a .NET application that uses connection pooling with Connection Reset set to true, you shouldn't worry about the performance impact of frequently calling SqlConnection.Open() and Close() because there is practically none as long as you don't go crazy with them (e.g. calling them 7000 times or more a second).

BTW, here are two places you can find information on sp_reset_connection:

• http://www.sqldev.net/misc/sp_reset_connection.htm.
• http://support.microsoft.com/kb/310617