SQL Server Performance

db connection jump from 350 to 2000

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by gahdhi, Jun 30, 2006.

  1. gahdhi New Member

    Approximately once a week for the past 3 weeks I have had some issues with
    performance in sql database that have caused signification problems with the custom winform based editorial tool and/or live site rendering.
    The primary symptoms have been extreme sluggishness in the tool, the publish
    process very slow or not moving at all and slow rendering on the live
    site. When the issues have occurred we have noticed a couple of things on the
    sql database. The user connections on the database take a significant and
    fairly sudden jump from about 350 to 2000 or more, the server itself becomes
    very slow, in 2 of the 3 cases it became totally unresponsive, unable to
    connect via QA. A sql restart has fixed the problem in all cases. Any suggest
    to look at appreciated..... We have a custome built CMS winApp to push contents to
    live site. All contents stored in sql database. But for some reason we have
    this issue with connetion jump suddenly without any indication.
  2. pyao88 New Member

    The application is not closing the SQL connection properly so it just keeps on increasing until SQL Server runs out of memory.
    You should be able to find out which machine/task/SQL the rouge connections are doing and inform developer of the CMS winApp.
  3. joechang New Member

    what's probably happening is you have web server with connection pooling
    normally 350 connections is sufficient such that whenever a request comes in, there is an available open connection from the pool

    then, some query or other event comes along causing a horrible slow down in SQL Server,
    so all requests now take a very long time.
    as new user requests come in, these first get allocated an already open connection
    but the query never completes, and the connection is never returned to the pool

    so the web server opens new connections for each subsequent request.
    the true cause of the problem is whatever started this.
    the excessive new connections is just throwing fuel on the fire

    there could be any number of causes.
    if it is predicted, then is there a scheduled query causing this?
    if it is over time with no apparent source, it could be the architect did not know certain things about virtual address space (VAS), see if restarting SQL Server every 2-3 days makes this go away

Share This Page