Concurrent users on SQL Server

  • Is there any data on how many concurrent users can a SQL Server support confortably without noticible delay in response time and throughput.

    In Oracle you can doing things like configure server process or use share server. Is there a way in SQL server one can allow more concurrent users?

    Thanks

  • There is no magic number. It depends on your way of doing SQL statements (more long running, ressource intensive queries will allow less concurrent access without a performance penality)

    I have SQL systems where I have sometimes 1000+ concurrent connection and it works.



    Bye
    Gabor

  • Thanks Gabor.

    Do you need to configure SQL Server in any special way, like change server and /or database options, to support over 1000 users, or just take the default will do?

  • You can limit the number of connections, but by default there is no limit. And as Gabor mentioned, it depends. 5 people can kill a server or 5000 can run fine. You need to design well and tune the code appropriately to handle things.

  • Let me put my question in another way.

    Suppose my SQL Server serves 1000 concurrent users and I were told it is slow. I would do the following to try to improve it:

    1) checking blocking, and try to resolveit by redesign the queries

    2) check long open tranactions and try to avoid them

    3) use with (nolock) in all queries possible

    4) tune the slow statement to improve performance

    5) make sure indexes are used properly and resolve fragment files and objects

    Can any server and database settings help.

    Any other sugegstions and opineans are appreciated.

  • All this.

    In addition you can check where you bottleneck is (Disk, CPU, Memory, Network, Client app...). What you have to know: Adding more memory always helps. But if you have a small database (some few gigs) then adding too meny GB of memory wont help you

    As a rule of thumb: 80% of the performance improvement can be reached by TSQL tuning (right SQL, table, index design) and only 80% by HW tuning.



    Bye
    Gabor

  • "As a rule of thumb: 80% of the performance improvement can be reached by TSQL tuning (right SQL, table, index design) and only 80% by HW tuning."

    Did you mean 20% by HW tuning??

  • Yes.

    And what I mean by this is if you have a wrong written SQL statement with wrong or no index using it doesn't really make sense increasing the HW power, it is better to tune the query itself.



    Bye
    Gabor

  • Yup... but that's almost a given, I guess ?!?!

  • Thanks, Guys.

  • Optimize statements or procedures you use very often, even if you can save miliseconds on them, ms multiply by thousands/millions really count, Transactions should be the shortest you can. 

    In addittion use Stored procedures  and Extended Stored Procedures.

    • Using Stored procedures you save bandwidth, they are precompiled, so you would save parsing and optimizing, in some cases performance is 8 or more times better than executing directly SQL Statements.
    • Extended Stored Procedure written in C / Delphi / etc. are standard DLL, use them when you need to make intensive CPU use, you can get some GNU XP dll for String manipulation, email sending, etc.

     

     

    • Pedro.  
  • Hi guys, I need your support, my boss give me a task, I need to know how many concurrent users is logging every 30 minutes, that for take a picture for statistics to analyze the data for buy users licenses of sql server 2000 !!

    excuse my English, but I am still learning !!

    Thanks for all,

    Edgar Flores

    Guatemala

  • Hi,

    I have a question. In one of my applications, I am getting a Deadlock situation of there are more users using the site at the same time. Is there any remedy? Is this anyway connected to database Pool size or concurrent connections?

    Please help!

    Thanks in advance

    Subrata

  • lugang (8/17/2005)


    3) use with (nolock) in all queries possible

    That's actually some pretty terrible advice and is usually indicative of some really bad code if it's actually needed to realize performance. Google the subject and find out all the reasons why it's such a bad idea.

    {edit} Sorry... didn't realize that post was almost 5 years old. Still, it's true.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • niladri.sirkar (6/12/2010)


    Hi,

    I have a question. In one of my applications, I am getting a Deadlock situation of there are more users using the site at the same time. Is there any remedy? Is this anyway connected to database Pool size or concurrent connections?

    Please help!

    Thanks in advance

    Subrata

    Yes... there are many remedies and they all "depend" on what the cause is. Generally speaking, slow or improperly written code is the culprit. My recommendation is to read about troubleshooting deadlocks in Books Online.

    Of course, step 1 is for you to make sure that the deadlocks are being logged so you can actually find the code that needs repair. You can do that by turning on certain trace flags (which should be left on even when you're done) or by setting up SQL Server Profiler.

    This is a large and very broad subject and you really need to read Books Online to start to get a feel for it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply