none
C# MS SQL Connection Issues with many users - Correct way to connection pool RRS feed

  • Question

  • Hey guys, so I've got an issue I'm hoping is a simple fix.

    I have a C# web service that I interact with via JSON calling web methods that interact with a database (using the code below).

    Connection String:

    <add name="MainConnection" connectionString="SERVER=10.218.147.71;DATABASE=******;UID=******-web;PWD=******;Pooling=True;Min Pool Size=25;Max Pool Size=250;" providerName="System.Data.SqlClient" />

    Method Code:

    public List<MatchmakingSessions> GetMatchmakingSessionsWithSearchQuery(string mThisQuery = "") {
    	List<MatchmakingSessions> matchmakingsessionsList = new List<MatchmakingSessions>();
    
    	SqlConnection mConnection = new SqlConnection();
    	SqlCommand mCommand = new SqlCommand();
    	try {
    		using (mConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnection"].ConnectionString)) {
    			mCommand = new SqlCommand("SELECT * FROM [MatchmakingSessions] " + mThisQuery, mConnection);
    
    			if (mConnection.State == ConnectionState.Closed) {
    				mConnection.Open();
    			}
    			SqlDataReader mReader = mCommand.ExecuteReader();
    
    
    			while (mReader.Read()) {
    				if (mReader.HasRows) {
    					MatchmakingSessions mMatchmakingSessions = new MatchmakingSessions();
    					mMatchmakingSessions.SessionID = (mReader["SessionID"] is DBNull) ? 0 : Int32.Parse(mReader["SessionID"].ToString());
    					//More DB calls to set properties...
    					matchmakingsessionsList.Add(mMatchmakingSessions);
    				}
    			}
    		}
    	}
    	catch (Exception x) {
    		if (!GGErrorHandler.ContainsRegularTypes(x.Message)) {
    			BusinessHelper.SendMailMessage(string.Format("<b>Error Message</b>: <br /><br />{0}</br><b>Stack Trace</b>: <br />{1}<br />", x.Message, x.StackTrace));
    			throw new Exception(GGErrorHandler.GetUserFriendlyError("DefaultErrorMessage", ErrorSource.WebAPI, x.Message));
    		}
    		else
    			throw new Exception(GGErrorHandler.GetUserFriendlyError(x.Message, ErrorSource.WebAPI, x.Message));
    	}
    	finally {
    		mConnection.Close();
    		mCommand.Dispose();
    	}
    
    	return matchmakingsessionsList;
    }

    I ran a stress test with only about 200 users calling the same method with about 1 second intervals per user, all users simultaneously and I started getting the "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. " errors, which eventually just crashed the entire web service.

    Am I totally off base with how I'm managing my connections?

    Sunday, April 13, 2014 6:45 AM

All replies

  • Hello,

    >>Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

    The error means the connection pool is out of connections. The default max size is 100 that means in the same time, it only allows 100 connections is opened. If you tries to open 101 connection, it will throw such an error. For a simple fix, to increase the max value in the connection string as:

    max pool size=250;

    >>Am I totally off base with how I'm managing my connections?

    I think you have done it with using try-catch-finally block to close and dispose the connection instance. Or you can use the "using" syntax:

    using(SqlLiteConnection conn = new SqlLiteConnection(...))
    {
       // Do work here
    }

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 14, 2014 9:22 AM
    Moderator
  • I am using the "using" syntax to wrap the SQLConnection.

    I am also closing the data reader.  The only thing I'm not doing is disposing the SQLCommand or the SQLDataReader -- that shouldn't keep the connection from disposing once it finalizes and leaves the using block though, should it?

    Monday, April 14, 2014 7:35 PM
  • >> that shouldn't keep the connection from disposing once it finalizes and leaves the using block though, should it?

    No, it should not.            

    Having you tried to increase the max pool size since it is 100 by default.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 21, 2014 8:12 AM
    Moderator