Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Unanswered: large number of connections

    hi,
    Does large number of connections to a sql server result in slogging queries??
    by large number of connections I mean in the range of 2000 to 2500 connections to various databases on the same server.
    This happens on the production database, if I download the copy of the database and run the specific queries on the local box it hardly takes 1 second to execute, whereas on the production box it takes about 45 to 60 secs, i m working on the indexes part... was not sure whether number of connections to a server affect the performance of the queries or it is just that the indexes need defragmentation...

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    The performance can be affected by that many connections Harshal. What do you have your max connections set at? Basically, you set a max on this and each connection takes 2k (or something like this) to maintain. The connections after this just back up and wait for the order they can run in. You can increase the max connections so you can process more parallel requests if you have the memory and CPU available to do it. Just be aware that it does consume resources just to maintain the connections.

    Unless you are a hosting company, you also probably need to look at how your applications do connection pooling (or don't do it as might be the case).
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Thanks derrick.
    The max connections is set at 32767
    and at all the times there are about 2k connections.
    Memory and other resources won't be a problem since this is a high end server, since I don't have the access to the server not able to confirm the resource availablity, but am sure it got ample ram and it is a quad processor system, and the database tables that in question are not that big...
    secondly I think what you said applies here as in the morning IST it doesn't take that long though in the evening time it takes longer, but it seems that the connections are not killed they are there but seems to be like they are dormant .. at that time the queries are faster.. moreever the most of the connections access other databases not the ones in question.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •