SQL Server Performance

# of connections problem

Discussion in 'T-SQL Performance Tuning for Developers' started by tlaurie, Dec 11, 2002.

  1. tlaurie New Member

    What do you do if you have many more front end users than connections to the SQL server available?

    Do you write the front end to open a connection every time you make a transaction and close it right after? What do you do then about record locking (record won't be locked if you close the connection)?

    What about connection pooling?

    Tom Laurie
  2. royv New Member

    Well your front-end should take advantage of connection pooling automatically, if you are using the latest version of MDAC which is where ADO comes from. Our solution here is to write our own server that is allowed 25 connections to the database and then our front-end hooks into our server instead of the database directly.


    "How do you expect to beat me when I am forever?"
  3. bradmcgehee New Member

  4. trifunk New Member

    Brads' right, it's really a huge question and it can also depend on the type of application and it's needs.
    Using .NET you can take advantage of the fact that alot of ADO.Net is written to work with data offline so you could make a single connection and retrieve most of the data in one swoop and then work with it in the DataSet 'offline' (from the database) which is more scalable as you're freeing up database connections and don't have to keep on returning, you could also take advantage of caching data, especially data that's frequently used and is fairly static.

    If you're worried about updating data and need record locks, the ADO.Net DataSet can also help you out here as you can make updates and inserts offline and then make one call to the database to do this, any 'dirty data' won't be updated.

    You can also cache with the previous version of ADO by persisting the data as xml which is also quite elegant.. all depends on what your application is required to do.

    Cheers
    Shaun


    World Domination Through Superior Software
  5. satya Moderator

    Fair commends by fellow members, For example, if the maximum number of user connections to your SQL Server box is equal to 50, you can set the 'max worker threads' options to 50. This frees up resources for SQL Server to use elsewhere. If the maximum number of the user connections to your SQL Server box is equal to 500, you can set the 'max worker threads' options to 500, which can improve SQL Server performance because thread pooling will not be used.

    Satya SKJ

Share This Page