locked
SQL Server connectins in a multi-threaded application RRS feed

  • Question

  • I'm trying to solve a problem that is really outside my realm of knowledge.

    It's a problem because of the effect it has on my SQL server.

     

    The application operates on it's server in a multi-threaded environment.  It's apparently important to limit 'garbage collection' because of this.

     

    One of the ways this is managed is to limit the connection pool, which apparently means that it is not possible to do multiple operations within one connection.  A connection is created, a proc is executed, the connection is closed.  As you can imagine, this is a pretty huge load when updating or inserting multiple records at once.  i.e., a user makes several selections on a screen involving a lot of records, and then submits those changes to the database.

     

    We are facing performance issues, and I suspect that this may have something to do with it.  When I look at a trace, I see login, logout, login, logout.

     

    Can anyone explain how the heck this works, and if there is anything i can tell my developers that will help our performance issues?  Most of the developers are new to working with a database back end, and i know very little about application layer development.  i only know what i'm seeing on my server, and that I've never had this problem before.

     

    We are on SQL 2005, .NET 2.0.

    Monday, June 18, 2007 5:11 PM

All replies

  • Hi,

     

    It sounds like connection pooling at the sqlclient layer would be helpful in your case.  But I'm moving your thread to the .Net Framework Data Access forum as there are probably more people here who should be able to help you.

     

    Thanks,

    Il-Sung.

    Monday, June 18, 2007 5:41 PM
  • I've looked a few versions of our database, and it would seem that earlier versions were using connection pooling, as i logged a lot of sp_reset_connection, but later versions have login, logout, which would imply we are not using connection pooling as well as we could...we seem to have changed something in our data access layer.
    Monday, June 18, 2007 5:57 PM
  • You can use a base multi-thread class which provides you  a  full control of  multi-threading.
    Here is the code;

     public abstract class AsyncOperation
        {
            /// <summary>
            /// Initialises an AsyncOperation with an association to the
            /// supplied ISynchronizeInvoke.  All events raised from this
            /// object will be delivered via this target.  (This might be a
            /// Control object, so events would be delivered to that Control's
            /// UI thread.)
            /// </summary>
            /// <param name="target">An object implementing the
            /// ISynchronizeInvoke interface.  All events will be delivered
            /// through this target, ensuring that they are delivered to the
            /// correct thread.</param>
            public AsyncOperation(ISynchronizeInvoke target)
            {
                isiTarget = target;
                isRunning = false;
            }

            /// <summary>
            /// Launch the operation on a worker thread.  This method will
            /// return immediately, and the operation will start asynchronously
            /// on a worker thread.
            /// </summary>
            public void Start()
            {
                lock(this)
                {
                    if (isRunning)
                    {
                        throw new AlreadyRunningException();
                    }
                    // Set this flag here, not inside InternalStart, to avoid
                    // race condition when Start called twice in quick
                    // succession.
                    isRunning = true;
                }
                new MethodInvoker(InternalStart).BeginInvoke(null, null);
            }


            /// <summary>
            /// Attempt to cancel the current operation.  This returns
            /// immediately to the caller.  No guarantee is made as to
            /// whether the operation will be successfully cancelled.  All
            /// that can be known is that at some point, one of the
            /// three events Completed, Cancelled, or Failed will be raised
            /// at some point.
            /// </summary>
            public void Cancel()
            {
                lock(this)
                {
                    cancelledFlag = true;
                }
            }

            /// <summary>
            /// Attempt to cancel the current operation and block until either
            /// the cancellation succeeds or the operation completes.
            /// </summary>
            /// <returns>true if the operation was successfully cancelled
            /// or it failed, false if it ran to completion.</returns>
            public bool CancelAndWait()
            {
                lock(this)
                {
                    // Set the cancelled flag

                    cancelledFlag = true;


                    // Now sit and wait either for the operation to
                    // complete or the cancellation to be acknowledged.
                    // (Wake up and check every second - shouldn't be
                    // necessary, but it guarantees we won't deadlock
                    // if for some reason the Pulse gets lost - means
                    // we don't have to worry so much about bizarre
                    // race conditions.)
                    while(!IsDone)
                    {
                        Monitor.Wait(this, 1000);
                    }
                }
                return !HasCompleted;
            }

            /// <summary>
            /// Blocks until the operation has either run to completion, or has
            /// been successfully cancelled, or has failed with an internal
            /// exception.
            /// </summary>
            /// <returns>true if the operation completed, false if it was
            /// cancelled before completion or failed with an internal
            /// exception.</returns>
            public bool WaitUntilDone()
            {
                lock(this)
                {
                    // Wait for either completion or cancellation.  As with
                    // CancelAndWait, we don't sleep forever - to reduce the
                    // chances of deadlock in obscure race conditions, we wake
                    // up every second to check we didn't miss a Pulse.
                    while (!IsDone)
                    {
                        Monitor.Wait(this, 1000);
                    }
                }
                return HasCompleted;
            }


            /// <summary>
            /// Returns false if the operation is still in progress, or true if
            /// it has either completed successfully, been cancelled
            ///  successfully, or failed with an internal exception.
            /// </summary>
            public bool IsDone
            {
                get
                {
                    lock(this)
                    {
                        return completeFlag || cancelAcknowledgedFlag || failedFlag;
                    }
                }
            }

            /// <summary>
            /// This event will be fired if the operation runs to completion
            /// without being cancelled.  This event will be raised through the
            /// ISynchronizeTarget supplied at construction time.  Note that
            /// this event may still be received after a cancellation request
            /// has been issued.  (This would happen if the operation completed
            /// at about the same time that cancellation was requested.)  But
            /// the event is not raised if the operation is cancelled
            /// successfully.
            /// </summary>
            public event EventHandler Completed;


            /// <summary>
            /// This event will be fired when the operation is successfully
            /// stoped through cancellation.  This event will be raised through
            /// the ISynchronizeTarget supplied at construction time.
            /// </summary>
            public event EventHandler Cancelled;


            /// <summary>
            /// This event will be fired if the operation throws an exception.
            /// This event will be raised through the ISynchronizeTarget
            /// supplied at construction time.
            /// </summary>
            public event System.Threading.ThreadExceptionEventHandler Failed;


            /// <summary>
            /// The ISynchronizeTarget supplied during construction - this can
            /// be used by deriving classes which wish to add their own events.
            /// </summary>
            protected ISynchronizeInvoke Target
            {
                get { return isiTarget; }
            }
            private ISynchronizeInvoke isiTarget;


            /// <summary>
            /// To be overridden by the deriving class - this is where the work
            /// will be done.  The base class calls this method on a worker
            /// thread when the Start method is called.
            /// </summary>
            protected abstract void DoWork();


            /// <summary>
            /// Flag indicating whether the request has been cancelled.  Long-
            /// running operations should check this flag regularly if they can
            /// and cancel their operations as soon as they notice that it has
            /// been set.
            /// </summary>
            protected bool CancelRequested
            {
                get
                {
                    lock(this) { return cancelledFlag; }
                }
            }
            private bool cancelledFlag;


            /// <summary>
            /// Flag indicating whether the request has run through to
            /// completion.  This will be false if the request has been
            /// successfully cancelled, or if it failed.
            /// </summary>
            protected bool HasCompleted
            {
                get
                {
                    lock(this) { return completeFlag; }
                }
            }
            private bool completeFlag;


            /// <summary>
            /// This is called by the operation when it wants to indicate that
            /// it saw the cancellation request and honoured it.
            /// </summary>
            protected void AcknowledgeCancel()
            {
                lock(this)
                {
                    cancelAcknowledgedFlag = true;
                    isRunning = false;

                    // Pulse the event in case the main thread is blocked
                    // waiting for us to finish (e.g. in CancelAndWait or
                    // WaitUntilDone).
                    Monitor.Pulse(this);

                    // Using async invocation to avoid a potential deadlock
                    // - using Invoke would involve a cross-thread call
                    // whilst we still held the object lock.  If the event
                    // handler on the UI thread tries to access this object
                    // it will block because we have the lock, but using
                    // async invocation here means that once we've fired
                    // the event, we'll run on and release the object lock,
                    // unblocking the UI thread.
                    FireAsync(Cancelled, this, EventArgs.Empty);
                }
            }
            private bool cancelAcknowledgedFlag;


            // Set to true if the operation fails with an exception.
            private bool failedFlag;
            // Set to true if the operation is running
            private bool isRunning;


            // This method is called on a worker thread (via asynchronous
            // delegate invocation).  This is where we call the operation (as
            // defined in the deriving class's DoWork method).
            private void InternalStart()
            {
                // Reset our state - we might be run more than once.
                cancelledFlag = false;
                completeFlag = false;
                cancelAcknowledgedFlag = false;
                failedFlag = false;
                // isRunning is set during Start to avoid a race condition
                try
                {
                    DoWork();
                }
                catch (Exception e)
                {
                    // Raise the Failed event.  We're in a catch handler, so we
                    // had better try not to throw another exception.
                    try
                    {
                        FailOperation(e);
                    }
                    catch
                    { }

                    // The documentation recommends not catching
                    // SystemExceptions, so having notified the caller we
                    // rethrow if it was one of them.
                    if (e is SystemException)
                    {
                        throw;
                    }
                }

                lock(this)
                {
                    // If the operation wasn't cancelled (or if the UI thread
                    // tried to cancel it, but the method ran to completion
                    // anyway before noticing the cancellation) and it
                    // didn't fail with an exception, then we complete the
                    // operation - if the UI thread was blocked waiting for
                    // cancellation to complete it will be unblocked, and
                    // the Completion event will be raised.
                    if (!cancelAcknowledgedFlag && !failedFlag)
                    {
                        CompleteOperation();
                    }
                }
            }


            // This is called when the operation runs to completion.
            // (This is private because it is called automatically
            // by this base class when the deriving class's DoWork
            // method exits without having cancelled

            private void CompleteOperation()
            {
                lock(this)
                {
                    completeFlag = true;
                    isRunning = false;
                    Monitor.Pulse(this);
                    // See comments in AcknowledgeCancel re use of
                    // Async.
                    FireAsync(Completed, this, EventArgs.Empty);
                }
            }

            private void FailOperation(Exception e)
            {
                lock(this)
                {
                    failedFlag = true;
                    isRunning = false;
                    Monitor.Pulse(this);
                    FireAsync(Failed, this, new ThreadExceptionEventArgs(e));
                }
            }

            // Utility function for firing an event through the target.
            // It uses C#'s variable length parameter list support
            // to build the parameter list.
            // This functions presumes that the caller holds the object lock.
            // (This is because the event list is typically modified on the UI
            // thread, but events are usually raised on the worker thread.)
            protected void FireAsync(Delegate dlg, params object[] pList)
            {
                if (dlg != null)
                {
                    Target.BeginInvoke(dlg, pList);
                }
            }
        }



    ******Sample Usage*************


    public sealed class DirSearchWorker : AsyncUtils.AsyncOperation
    {
        public delegate void MatchEvent(string name);
        public event MatchEvent Match;
        public event MatchEvent Dir;

        private string searchDir;
        private Regex searchExpr;

        public DirSearchWorker(ISynchronizeInvoke isi, string dir, string regexp)
            : base(isi)
        {
            searchDir = dir;
            // Building a Regex object with the Compiled option has a
            // slightly higher startup overhead than the default, but since
            // we are usually doing loads of comparisons, and the speedup
            // tends to be about an order of magnitude, it's usually worth
            // it.
            searchExpr = new Regex(regexp, RegexOptions.Compiled);
        }

        // Note, 'protected' seems anomalous here, since this is a sealed
        // class, but the compiler won't let us change the protection
        // level when we override a method.
        protected override void DoWork()
        {
            DoSearch(searchDir);
            // When a cancel occurs, the recursive DoSearch drops back
            // here asap, so we'd better acknowledge cancellation.
            if (CancelRequested)
            {
                AcknowledgeCancel();
            }
        }

        private void DoSearch(string dir)
        {
            // Stop if cancellation was requested

            if (!CancelRequested)
            {
                OnDir(dir);

                foreach(string name in Directory.GetFileSystemEntries(dir))
                {
                    // Check for cancellation here too, otherwise for
                    // really large directories (such as those in the IE
                    // cache) the user might have to wait for several
                    // seconds before the cancellation happens.
                    if (CancelRequested) return;
                    if (searchExpr.IsMatch(name))
                    {
                        OnMatch(name);
                    }
                }

                foreach(string name in Directory.GetDirectories(dir))
                {
                    DoSearch(name);
                }
            }
        }

        private void OnMatch(string name)
        {
            lock(this)
            {
                FireAsync(Match, name);
            }
        }
        private void OnDir(string name)
        {
            lock(this)
            {
                FireAsync(Dir, name);
            }
        }

    }
     
    Wednesday, June 20, 2007 2:53 PM
  • Connection pooling is on by default for SqlClient. If you are seeing a lot of login/logout events from the app, here are few possible causes:

    1. Connection pooling has been explicitly turned off in the connection string. Check the string for "pooling = no/off" entries. Remember that connection strings are often kept in config files.
    2. Connection strings &/or users keep changing. Pooling keys of of exact connection string. Any differences in the string (including space characters!) between two connections means they will not use the same pool. Likewise, for Windows Authentication, pools are not shared between identities. You can check for excessive differences by looking at SqlClient's performance counter for number of pool groups/pools (.Net Data Provider for Sql Server).
    3. Serious errors. Some errors from the server or in the CLR can cause a connection to be disposed of when closed instead of returned to the pool. From the server, high-severity errors can do this. On the SqlClient side, thread aborts are the most likely place to encounter this behavior (ASP.Net timeouts use this mechanism).
    4. Lack of connection recover code. If the connection is not properly Closed or Disposed by the app, it has to wait for GC to collect it before it can be returned to the pool for re-use.  On a machine with little memory pressure, this can cause the pool to fill up with little-used connections. Generally this causes a spike of new connections when the app is starting, which levels out after the pool fills (connections will still be used from the pool, there are just more of them open after the steady state is reached). Check the number of connections the app has against the db via SqlClient perf counters or from the Sql Server side.
    Wednesday, June 20, 2007 3:33 PM