Post reply

Concurrent users on SQL Server

  • August 16, 2005 at 1:49 pm

    #109888

    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

  • August 17, 2005 at 3:56 am

    #582717

    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

  • August 17, 2005 at 9:33 am

    #582787

    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?

  • August 17, 2005 at 9:57 am

    #582809

    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.

  • August 17, 2005 at 12:01 pm

    #582855

    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.

  • August 18, 2005 at 3:43 am

    #582946

    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

  • August 18, 2005 at 6:32 am

    #582981

    "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??

  • August 18, 2005 at 6:50 am

    #582985

    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

  • August 18, 2005 at 6:57 am

    #582987

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

  • August 18, 2005 at 9:38 am

    #583135

    Thanks, Guys.

  • August 18, 2005 at 12:25 pm

    #583243

    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.  

  • April 30, 2008 at 11:02 am

    #809206

    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

  • June 12, 2010 at 5:00 am

    #1179526

    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

  • June 12, 2010 at 12:41 pm

    #1179558

    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)

  • June 12, 2010 at 12:48 pm

    #1179559

    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