Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    95

    Unanswered: performance and #of users

    If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

    Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?


  2. #2
    Join Date
    Nov 2002
    Posts
    9

    Re: performance and #of users

    Originally posted by ricka49
    If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

    Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

    SQL Server 7.0 and 2000 use approximately 50 Kb of server RAM per connection. Note that a user can have multiple connections, even with connection pooling. A good rule of thumb here is one connection per 100 records returned, so 50Kb of server memory per 100 records.

    I cannot site an article on this. I got this information from a MS developer when I was looking at licensing options a couple of years back.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: performance and #of users

    You can check the number of users logged in from Enterprise Manager. You can also run sp_who and/or sp_who2. There is another place to check on user connections (and also connection memory)::

    Look at the sysperfinfo table on master:

    Code:
    SELECT * FROM master..sysperfinfo 
    WHERE
      (object_name = 'SQLServer:General Statistics' and counter_name = 'User Connections') OR
      (object_name = 'SQLServer:Memory Manager' and counter_name = 'Connection Memory (KB)')
    I make it a general habit to snapshot this data to a history table on an hourly basis. It can help me sometimes track usage and performance issues.

    Regards,

    Hugh Scott


    Originally posted by ricka49
    If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

    Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?


Posting Permissions

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