SQL Server Performance

how to restrict concurrent connection on sql servr

Discussion in 'Performance Tuning for DBAs' started by shreesoft00, Jan 23, 2003.

  1. shreesoft00 New Member

    can we restrict a limited number of concurrent connections to sql servers? how we can do it? anybody can help it as i just want to get 1000 concurrent connections to my sql server and what will happen when i will happen to other connections which will be restricted it will wait or it will get diconnected ??

    pls help
  2. bradmcgehee New Member

    By default, SQL Server only allocates as many user connections as it needs. This allows those who need to connect to connect, while at the same time minimizing the amount of memory used. When the "user connections" setting is set to its default value of "0", user connections are dynamically set. Under virtually all circumstances, this is the ideal setting.

    If you change the default value for "user connections," what you are telling SQL Server to do is to allocate only the number of user connections you have specified, no more or no less. Also, it will allocate memory for every user connection specified, whether or not it is being used. Because of these problems, and because SQL Server can perform this task automatically and efficiently, there is no reason to change this setting from the default.

    The above is from a tip on my website. If you want to limit the number of concurrent user connections to 1000, you can by changing this setting to 1000.

    Any connection over 1,000 will have to wait until an available connections is available.

    You can also read more about this topic in Books Online under this topic "user connections option".

    Brad M. McGehee
  3. shreesoft00 New Member

    thaanx bradmcgehee

    but if i have only 1000 CALs(client Access License) then what should i do ?
    i have to restrict concurrent connection
    any suggestions? bradmcgehee?

  4. satya Moderator

    If you know that in a peak time those 1000 connections would be sufficient to cater the needs, then it should be fine. We have few of our production systems restricted to 500 user connections only which gets to the limit once in a year.

    As far as disconnections concerned, you can control that from the application once the transaction finishes the process. If its a user on network then well before make them understand to close their connection once they finishes the job.

    As Brad referred BOL has got few good points to consider.


    Satya SKJ
  5. bradmcgehee New Member

    When you install SQL Server, you are given the ability to enter the number of CALs you have, so this is where you do that. If licensing is your only issue, then I wouldn't change the user connections option. Keep in mind that a CAL is not equal to a user connection. This is because one connection can serve multiple users and a single user can have multiple connections. If you have specific licensing issues, you really need to talk to your sales representative because licensing issues are very complicated and can be slightly different between different companies.

    Brad M. McGehee
  6. shreesoft00 New Member

    thanx again bradmcgehee
    the problem is we have few apllications which will be used by few thousand users i guess like 2000 and at peak time may be 4000 and my cheif informatiuon officer asked me a solution that can we afford to have all connection to flow to server or should we restrict the concurrent connection to 1000 or 2000 as a performance issue we have 4 proccessors and 3gb ram on our sql server with sql200 win2k with SP3 and our company have 1000 cal so what should be my reply to my boss and what is the best solution ??? as he said i guess i can restrict the user to 1000 or 2000 but is this a solution or something else should be done ?
    waiting for reply .....

  7. bradmcgehee New Member

    From a license viewpoint, if you only have 1,000 CALs, these means that there should never be over 1,000 concurrent users. If there are, then you would be in violation of your license agreement with Microsoft. If you think that there might be 2,000 to 4,000 concurrent users, then you need to tell your boss to get some more licenses.

    As satya mentioned above, it is rare to really have that many active connections. What you might find is that there are, let's say 2,000 connection, that only 500 of them might be active, and that is what is really important, the number of active connections, from a performance perspective.

    I will leave the license issues up to your boss. But from a performance perspective, your system might be a little small if you want to have 4,000 concurrent connections, and a great many of them are active. But this will depend on the nature of the database. For example, if the users mostly only perform SELECTs and not data modifications, and if the data is well indexed, then handling this many users is within reach. But if the users are performing many data modifications, then your server may not be able to handle it.

    This is hard to predict. Is this system running now, or is it in testing? What you may end up having to do is just experiment and see what happens and go from there. In the real world, this is very common, as predicting the affect of thousands of users is very hard to do.

    I imagine that you will be wanting to watch performance very closely as more and more users begin to access your system.

    Brad M. McGehee
  8. shreesoft00 New Member

    thanx bradmcgehee

    yes i am little bit relaxed as a DBA that might be there will be less active connections in all concurrent connections but it will be around 60% of the max 4000 then definetly it will be around 1000-1500 active connections and this is a live database right now there is few users but in next few weeks the users are goin to be large large as i mentioned so company wants to saturate at a point where they can give a nice performance and keep the cost in control so and right now the performance is very good and i have one more query regarding performance can i reindex the table when the databse is used by so many users ? and how often i should reindex?

    waiting for reply ......
  9. bradmcgehee New Member

    If you reindex the tables in your database (by dropping and rebuilding them), then during the time a particular table is having its indexes rebuild, the table will be locked, preventing users from accessing it. If the tables aren't too big, the wait won't be very long, but if the tables are huge, the wait could be quite a while. Because of this, it is best to rebuild indexes during the slow time of the day or week.

    If this is not an acceptable option, a second choice, although not a good as a complete rebuild, is to run the DBCC INDEXDEFRAG, which does not lock users out of tables. This will help a lot from a performance perspective. You will want to read up on it in the Books Online.

    Brad M. McGehee

Share This Page