THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance Impact of Frequent Logins (Actually Performance Impact of Frequently Calling SqlConnection.Open() with Connection Reset)

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:

Published Monday, April 16, 2007 4:25 PM by Linchi Shea

Attachment(s): logins.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David Markle said:

My guess is that you've exceeded the number of connections in your connection pool, and at that big jump in the graph, SQL Server has to make actual additional connections?  What happens if you up the pool size to, say, 1000?  IMO you should re-plot the graph with different series, each representing a different pool size.

April 18, 2007 7:52 AM
 

Linchi Shea said:

On the server side, I didn't touch the user connections option. It was left at default. Plus, the number of actual connections used in the tests was far below the max user connections allowed.

On the client side, I ran multiple copies of the test program, and made sure that the number of threads for each copy (or process) was smaller than the max pool size. I doubt I ever exceeded the connection pool size.

April 18, 2007 1:38 PM
 

Yohn said:

Did you checked the IO impact on catalog tables?

June 9, 2009 10:53 AM
 

George said:

Someone should tell SQL Spotlight.  They are throwing red alerts when connections-per-second gets into the low hundreds... very annoying...

November 13, 2009 4:00 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement