How to find out how many concurrent users does the server support?
-
It depends on the licensing mode that you selected when SQL Server was installed. For SQL 2000, you can pick per processor, or per seat. Per processor will allow an unlimited number of connections. Per seat allows only the same number of connections that you purchased licenses for. Look up the topic "Choose Licensing Mode" in Books On Line for more information.
My hovercraft is full of eels.
-
If this question is about capacity, the answer would have to be: that depends.
The number of concurrent users a server can support at acceptable levels of response depends on hardware, memory, the type of workload, disk configuration, and a number of other factors. It's unlikely that anyone on this forum could give you a hard number without knowing a lot more about the physical setup, the application design, and the response requirements of the application or applications you were planning to run.
Running benchmark tests with a representative workload as you progress will help to make sure your server can handle the projected load.
If you're looking to figure out what size server to get for your application, posting the characterists of the target environment might allow the experienced folks here to give you a rough guess, but your milage will vary, no matter what anyone recommends.
And then again, I might be wrong ...
David Webb -
David,
Thanks for the advise. The question I posted is general only. I just like to know is there any thump rule exists?
One more thing, say I have a box running on Production environment there is a requirment to find out what is the maximum no.of concurrent user this box would the support?
In this case what is process i need to follow to get the answer.
What are aspects / parameter i need to look for?
Usaully how it being followed.
TIA
-
Siva,
It's not as easy as that. The number of concurrent users on the SQL Server is not that important. What is important is what those users are going to be doing on the SQL Server. How many transactions is the server going to be handling per second? What kind of transactions? And so on.
If all your concurrent users are going to do is run queries off a 10 row table, then you'll obviously be able to handle more users than if they were all going be writing, updating, and querying million row tables.
As a rule of thumb, each connection to SQL Server takes about 500k of RAM. So from a memory point of view, if you have 1000 MB of RAM that will allow 2000 concurrent SQL Server connections. However, you have to allow for the OS using up some memory. Plus, you need to account for SQL Server having some memory so that it can process the transactions.
Rather than ask, "how many concurrent users can my system support?" you should be asking "how many concurrent users does my system need to support? And what kind of operations are then going to be running?" and then build your system around that.
I know it's not the answer you were hoping for but unfortunately there is no rule of thumb. I'd recommend reading the book SQL Server Performance Tuning (Technical Reference) by Microsoft Press. It's got some good information there on Capacity Planning and sizing.
-
>As a rule of thumb, each connection to SQL Server takes about 500k of RAM.
That is not quite true actually. Each connection to SQL Server takes 12 KB + (3 * Network Packet Size). Network packet size by default is 4 KB, but can be configured using sp_configure.
However, if you have all these connections actually doing work at the same time there will also be 255 worker threads (or whichever amount is configured as the max number of worker threads) running, and these each use 0.5 MB of RAM.
-
Hi Karl Grambow.,
Your reply is splendid. I would be grateful if you could expalin the same with example for say client requesting configuration for box to support 100 concurrent users. The transaction done by users would be of DML nature.
Please explain,
1. Hardware details like Memory, CPU, HDD and N/w to look for
2. SQL Server Configuration settings if any specific to be made to avail this
3. SQL / Windows Licence details
Thanks a million
S Siva Prasad
-
Hi Chris ,
So at any point of time 255 Worler Threads => 255*0.5 MB = 128 MB + each connection approx 0.5MB would be required on RAM aspect. Is that right?
On Network packet 12 KB + (3 * N/w Packet size) would be the N/W Bandwidth requirement. Is it right?
Could you clarify, Please?
Thanks
S Siva Prasad
-
My answer was only meant as a small correction to the previous message. It is not meant as a recommendation as to how many concurrent users you can have in a system. SQL Server loves RAM and will take as much as you can give it, but SQL Server 2000 Standard Edition apparantly runs on as low as 64 MB RAM according to Microsoft minimum requirements. Anyone can guess that it would not handle a lot of users, but it would run. Like someone said above, you need to test your specific usage on a server to determine whether or not it can handle the load.
-
Compaq (now HP) used to have an 'Active Answers' section of their web site that did what you are asking for. You would answer some basic questions about your application, schema, and users, and it would spit out a recommended server configuration. I ran it for SQL Server, and even though it is very general, it wasn't too bad. Check out hp.com and see if it is still out there (or another incarnation).
For that matter, contact your server vendor (as long as it is one of the majors) and they should be able to help you out with some sizing recommendations. Take their recommendations with a huge grain of salt, though.
-
The function is still there (as of about 45 days ago) ... beware, it can be horrendously slow !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply